By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Dynamically Find Table Name used in Form

blyxx86
100+
P: 256
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:
Expand|Select|Wrap|Line Numbers
  1. tblTableList
  2. TableID; AutoNumber; PK
  3. TableName; String
  4. DateAdded: Date/Time
  5.  
Expand|Select|Wrap|Line Numbers
  1. tblNotes
  2. NoteID; AutoNumber;PK
  3. TableID; Number; FK
  4. TablePKID; Number; FK
  5. Note1; String
  6. Note2; String
  7. EnteredByNet; String
  8. EnteredByJet; String
  9. EnteredDate; Date/Time
  10.  
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.
Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.     Dim intTableID As Integer
  4.     Dim lngTablePKID As Long
  5.  
  6.     stDocName = "frmNoteTest1"
  7.     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
  8.     lngTablePKID = Me![AcqID]
  9.  
  10.     stLinkCriteria = "[TablePKID]=" & Me![AcqID]
  11.     stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
  12.  
  13.     DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
  14.  
Then when the form "frmNoteTest1" is opened I call this code in the OnOpen event:
Expand|Select|Wrap|Line Numbers
  1.     Dim intTableID As Integer
  2.     Dim lngTablePKID As Long
  3.  
  4.     If Not IsNull(Me.OpenArgs) Then
  5.     ' Split OpenArgs data into separate fields
  6.         intTableID = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
  7.         lngTablePKID = Mid(OpenArgs, InStr(OpenArgs, ";") + 1)
  8.  
  9.         Me.TableID.DefaultValue = intTableID
  10.         Me.TablePKID.DefaultValue = lngTablePKID
  11.  
  12.     End If
  13.  
How do I select the TableID from the first form based on what table the form is bound to? Does that even make sense?
Aug 30 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
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:
Expand|Select|Wrap|Line Numbers
  1. tblTableList
  2. TableID; AutoNumber; PK
  3. TableName; String
  4. DateAdded: Date/Time
  5.  
Expand|Select|Wrap|Line Numbers
  1. tblNotes
  2. NoteID; AutoNumber;PK
  3. TableID; Number; FK
  4. TablePKID; Number; FK
  5. Note1; String
  6. Note2; String
  7. EnteredByNet; String
  8. EnteredByJet; String
  9. EnteredDate; Date/Time
  10.  
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.
Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.     Dim intTableID As Integer
  4.     Dim lngTablePKID As Long
  5.  
  6.     stDocName = "frmNoteTest1"
  7.     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
  8.     lngTablePKID = Me![AcqID]
  9.  
  10.     stLinkCriteria = "[TablePKID]=" & Me![AcqID]
  11.     stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
  12.  
  13.     DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
  14.  
Then when the form "frmNoteTest1" is opened I call this code in the OnOpen event:
Expand|Select|Wrap|Line Numbers
  1.     Dim intTableID As Integer
  2.     Dim lngTablePKID As Long
  3.  
  4.     If Not IsNull(Me.OpenArgs) Then
  5.     ' Split OpenArgs data into separate fields
  6.         intTableID = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
  7.         lngTablePKID = Mid(OpenArgs, InStr(OpenArgs, ";") + 1)
  8.  
  9.         Me.TableID.DefaultValue = intTableID
  10.         Me.TablePKID.DefaultValue = lngTablePKID
  11.  
  12.     End If
  13.  
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
Aug 31 '07 #2

blyxx86
100+
P: 256
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim db As DAO.Database          'CurrentDb()
  3.     Dim rs As DAO.Recordset         'Various recordsets.
  4.     Dim strSQL As String            'SQL Clause
  5.     Dim stDocName As String
  6.     Dim stLinkCriteria As String
  7.     Dim intTableID As Integer
  8.     Dim lngTablePKID As Long
  9.  
  10.     Set db = CurrentDb()
  11.  
  12.     strSQL = "SELECT TableID, TableName FROM tblTableList WHERE TableName = " & """" & Me.RecordSource & """" & ";"
  13.  
  14.     Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
  15.     With rs
  16.         If .RecordCount = 1 Then
  17.             intTableID = !TableID
  18.         Else
  19.             MsgBox "The table was either not found or there was an error, please contact your administrator.", vbCritical, "Error"
  20.             Exit Sub
  21.         End If
  22.     End With
  23.  
  24.     stDocName = "frmNoteTest1"
  25.     lngTablePKID = Me![AcqID]
  26.  
  27.     stLinkCriteria = "[TablePKID]=" & Me![AcqID]
  28.     stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
  29.  
  30.     DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
  31.  
Aug 31 '07 #3

JConsulting
Expert 100+
P: 603
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim db As DAO.Database          'CurrentDb()
  3.     Dim rs As DAO.Recordset         'Various recordsets.
  4.     Dim strSQL As String            'SQL Clause
  5.     Dim stDocName As String
  6.     Dim stLinkCriteria As String
  7.     Dim intTableID As Integer
  8.     Dim lngTablePKID As Long
  9.  
  10.     Set db = CurrentDb()
  11.  
  12.     strSQL = "SELECT TableID, TableName FROM tblTableList WHERE TableName = " & """" & Me.RecordSource & """" & ";"
  13.  
  14.     Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
  15.     With rs
  16.         If .RecordCount = 1 Then
  17.             intTableID = !TableID
  18.         Else
  19.             MsgBox "The table was either not found or there was an error, please contact your administrator.", vbCritical, "Error"
  20.             Exit Sub
  21.         End If
  22.     End With
  23.  
  24.     stDocName = "frmNoteTest1"
  25.     lngTablePKID = Me![AcqID]
  26.  
  27.     stLinkCriteria = "[TablePKID]=" & Me![AcqID]
  28.     stLinkCriteria = stLinkCriteria & " AND [TableID]=" & intTableID
  29.  
  30.     DoCmd.OpenForm stDocName, , , stLinkCriteria, , , intTableID & ";" & lngTablePKID
  31.  
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
Aug 31 '07 #4

blyxx86
100+
P: 256
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. :)
Sep 1 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.