Dynamically Find Table Name used in Form

[blyxx86]

Hi everyone,
I am using some VB code to have a "Notes" table be stored for use on all forms that relates to the specific record in any other table.

(I haven't used the terminology in a while, but i believe the FK is the primary key of another table???)
I have created a table of all the tables in my database:

 tblTableList
TableID; AutoNumber; PK
TableName; String
DateAdded: Date/Time
 

 tblNotes
NoteID; AutoNumber;PK
TableID; Number; FK
TablePKID; Number; FK
Note1; String
Note2; String
EnteredByNet; String
EnteredByJet; String
EnteredDate; Date/Time
 

I use the following code to open a form and place the TableID and TablePKID into the form's hidden textboxes.
This is on the OnClick command of a button.

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intTableID As Integer
    Dim lngTablePKID As Long
 
    stDocName = "frmNoteTest1"
    intTableID = 7 'This is what I am trying to find dynamically, as I have to set it manually right now.  tblAcq has ID 7 in the tblTableList table, and the form is bound to tblAcq, how do I take the bound form and find it in the tblTableList table
    lngTablePKID = Me![AcqID]
 
    stLinkCriteria = "[TablePKID]=" & Me![AcqID]
    stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
 

Then when the form "frmNoteTest1" is opened I call this code in the OnOpen event:

    Dim intTableID As Integer
    Dim lngTablePKID As Long
 
    If Not IsNull(Me.OpenArgs) Then
    ' Split OpenArgs data into separate fields
        intTableID = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
        lngTablePKID = Mid(OpenArgs, InStr(OpenArgs, ";") + 1)
 
        Me.TableID.DefaultValue = intTableID
        Me.TablePKID.DefaultValue = lngTablePKID
 
    End If
 

How do I select the TableID from the first form based on what table the form is bound to? Does that even make sense?

[JConsulting]

Hi everyone,
I am using some VB code to have a "Notes" table be stored for use on all forms that relates to the specific record in any other table.

(I haven't used the terminology in a while, but i believe the FK is the primary key of another table???)
I have created a table of all the tables in my database:

 tblTableList
TableID; AutoNumber; PK
TableName; String
DateAdded: Date/Time
 

 tblNotes
NoteID; AutoNumber;PK
TableID; Number; FK
TablePKID; Number; FK
Note1; String
Note2; String
EnteredByNet; String
EnteredByJet; String
EnteredDate; Date/Time
 

I use the following code to open a form and place the TableID and TablePKID into the form's hidden textboxes.
This is on the OnClick command of a button.

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intTableID As Integer
    Dim lngTablePKID As Long
 
    stDocName = "frmNoteTest1"
    intTableID = 7 'This is what I am trying to find dynamically, as I have to set it manually right now.  tblAcq has ID 7 in the tblTableList table, and the form is bound to tblAcq, how do I take the bound form and find it in the tblTableList table
    lngTablePKID = Me![AcqID]
 
    stLinkCriteria = "[TablePKID]=" & Me![AcqID]
    stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
 

Then when the form "frmNoteTest1" is opened I call this code in the OnOpen event:

    Dim intTableID As Integer
    Dim lngTablePKID As Long
 
    If Not IsNull(Me.OpenArgs) Then
    ' Split OpenArgs data into separate fields
        intTableID = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
        lngTablePKID = Mid(OpenArgs, InStr(OpenArgs, ";") + 1)
 
        Me.TableID.DefaultValue = intTableID
        Me.TablePKID.DefaultValue = lngTablePKID
 
    End If
 

How do I select the TableID from the first form based on what table the form is bound to? Does that even make sense?

from the first form...when you're in it, me.recordsource will give you the name of the underlying table/query

J

[blyxx86]

I would then just do a simple Select query to return the value of the ID.
Do I really need to do the whole..
With rs....
...
End With

I'm new to this type of process with the 'With' statements, so I'm not sure if I am using them properly.

 
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim strSQL As String            'SQL Clause
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intTableID As Integer
    Dim lngTablePKID As Long
 
    Set db = CurrentDb()
 
    strSQL = "SELECT TableID, TableName FROM tblTableList WHERE TableName = " & """" & Me.RecordSource & """" & ";"
 
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    With rs
        If .RecordCount = 1 Then
            intTableID = !TableID
        Else
            MsgBox "The table was either not found or there was an error, please contact your administrator.", vbCritical, "Error"
            Exit Sub
        End If
    End With
 
    stDocName = "frmNoteTest1"
    lngTablePKID = Me![AcqID]
 
    stLinkCriteria = "[TablePKID]=" & Me![AcqID]
    stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
 

[JConsulting]

I would then just do a simple Select query to return the value of the ID.
Do I really need to do the whole..
With rs....
...
End With

I'm new to this type of process with the 'With' statements, so I'm not sure if I am using them properly.

 
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim strSQL As String            'SQL Clause
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intTableID As Integer
    Dim lngTablePKID As Long
 
    Set db = CurrentDb()
 
    strSQL = "SELECT TableID, TableName FROM tblTableList WHERE TableName = " & """" & Me.RecordSource & """" & ";"
 
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    With rs
        If .RecordCount = 1 Then
            intTableID = !TableID
        Else
            MsgBox "The table was either not found or there was an error, please contact your administrator.", vbCritical, "Error"
            Exit Sub
        End If
    End With
 
    stDocName = "frmNoteTest1"
    lngTablePKID = Me![AcqID]
 
    stLinkCriteria = "[TablePKID]=" & Me![AcqID]
    stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
 

so you're opening the notes form based on the record that you were on in whatever form? And you want to write that ID and FK into the notes table..seems you're passing openargs, why not just pass the recordset name also...since you're on a form, it's properties are available to you to gather and pass. You had already done that with the ID fields, me.recordset would return the table or query for the form...am I not understanding what you're trying to do?
J

[blyxx86]

so you're opening the notes form based on the record that you were on in whatever form? And you want to write that ID and FK into the notes table..seems you're passing openargs, why not just pass the recordset name also...since you're on a form, it's properties are available to you to gather and pass. You had already done that with the ID fields, me.recordset would return the table or query for the form...am I not understanding what you're trying to do?
J

I am using the SQL to find the TableID that is stored in my table list. So I have to pass the ID number to the Note table.

I was attempting to construct a single table to store all the notes from every single person that is going to be using the database. I did not want to create a separate table for every other table just to store notes. This seemed like it would be the best way to have one location for the notes.

Do you know any other way to store notes in a central locations?

It's the weekend, and I'm not quite sure if I am making sense. :)