UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> ListFindFormTable    


Synopsis

Use this function to identify the table associated with a form. It works by comparing the record source (Table or SQL statement) with the current database's table definitions. It rejects record sources based on a JOIN as (1) there would be no unique table name, (2) the resulting record set may not be updateable. Related procedures are ListRenumber, ListDeleteRecord, ListInitialize, and ListNewRecord.

CODE
' ListFindFormTable
' http://www.utteraccess.com/wiki/ListFindFormTable
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2015-09-23                    
'
'************************************************************
' Utility Function to Find the Table from a record source (Table OR SQL statement)
' Function Arguments:
'           frm:  the form object
' Return:
'           The table name as a string if successful,
'           error messages and aborted process if not
' NOTE:
'           Used internally by List... functions to determine a table name
Public Function ListFindFormTable(frm As Form) As String
  Dim tbls As TableDefs, i As Long, sql As String, msg As String
  sql = frm.RecordSource
  If InStr(1, sql, " JOIN ", vbTextCompare) > 0 Then
     msg = "Form " & frm.Name & " has a RecordSource with a JOIN " & _
        "resulting in an ambiguous Table Name for the Ordering Process."
  Else
     Set tbls = CurrentDb.TableDefs
     For i = 0 To tbls.Count - 1
        If InStr(1, sql, tbls(i).Name, vbTextCompare) > 0 Then
           ListFindFormTable = tbls(i).Name
           Exit Function
        End If
     Next
  End If
  If Len(msg) = 0 Then
     msg = "Form " & frm.Name & "'s RecordSource failed to return a valid " & _
        "Table Name."
  End If
  frm.Undo
  MsgBox msg, vbCritical
  End
End Function

Creative Commons License
ListFindFormTable by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 766 times.  This page was last modified 14:29, 23 September 2015 by azolder.   Disclaimers