Button points to a record in form

[doc1355]

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?

[patjones]

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

[jaxjagfan]

If you are only using one table as the data source. Have you considered using tab control? It would be just one form with multiple tabs for data entry. Users could easily navigate from tab to tab. The different tabs would always be part of the same record. I did this for an application where I had about 50 fields for display and entry. I don't like my endusers to have to scroll up and down if possible within a form.

[patjones]

If you are only using one table as the data source. Have you considered using tab control? It would be just one form with multiple tabs for data entry. Users could easily navigate from tab to tab. The different tabs would always be part of the same record. I did this for an application where I had about 50 fields for display and entry. I don't like my endusers to have to scroll up and down if possible within a form.

That's a good idea also. I've actually done it that way before with multiple tables; as long as the form's RecordSource is set properly (e.g. via a well written SQL statement), it works great.