I have a MS Access database with multiple forms for data entery. All the data are stored in one table, but I have multiple forms to organize the data entry. I also have a main form that shows the record and has buttons linked to other forms for data entry. Clicking on each button will take you the form to enter the data.
So, here is what I have:
Main form with some minor information about a record (ID, name) and all the buttons, and then FORM 1, FORM 2 and so on and navigation keys. when you click on FORM2 button on thte main form it will open the FORM2 and shows the fields for the first record. The script for the button is as follows:
- Private Sub Command20_Click()
-
DoCmd.OpenForm "02 - Origin"
-
End Sub
But what I really need is that when I open the main form and I go to record 30 using the navigation butons, by clicking on the FORM2 button, it opens form 2 and navigates to the same record on that form so I enter the data for that record.
How should I change the script to be able to navigate to the record corresponding to the one I have on my main form?
Suppose that, on your main form, "ID" is stored in a text box called txtID. One way to do this is use the OpenArgs property of the OpenForm command to pass the ID of the current record to the second form:
- DoCmd.OpenForm "02- Origin", acNormal, , , , , , Me.txtID
Then, in the module for the second form, you could do something like
- Private Sub Form_Open(Cancel As Integer)
-
-
Dim rst As Recordset
-
-
Set rst = Me.RecordsetClone
-
-
rst.FindFirst "[tblName].[fldID] Like ' * " & Me.OpenArgs & " * ' "
-
-
If rst.NoMatch = False Then
-
Me.Bookmark = rst.Bookmark
-
End If
-
-
rst.Close
-
Set rst = Nothing
-
-
End Sub
-
In this, you would put tblName = your table's name, and fldID = the name of the field that ID is stored in.
This method assumes that:
1) ID uniquely identifies your records (otherwise FindFirst will just go to
the first record that has ID in it, even if there are many other records
that contain ID).
2) The second form is already bound to the table (e.g.
Me.RecordSource = "tblName").
Another (possibly simpler) method would be to use a SQL statement to assign a data source to the form (e.g. Me.RecordSource = "SELECT [field names] FROM [table name] WHERE [fldID] = Me.OpenArgs"). This would also be done inside the second form's VB module.
Hope this helps.
Pat
16
