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

Button points to a record in form

P: 16
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command20_Click()
  2. DoCmd.OpenForm "02 - Origin"
  3. 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?
Dec 21 '07 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command20_Click()
  2. DoCmd.OpenForm "02 - Origin"
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "02- Origin", acNormal, , , , , , Me.txtID
Then, in the module for the second form, you could do something like

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_Open(Cancel As Integer) 
  2.  
  3. Dim rst As Recordset
  4.  
  5. Set rst = Me.RecordsetClone
  6.  
  7. rst.FindFirst "[tblName].[fldID] Like ' * " & Me.OpenArgs & " * ' "
  8.  
  9. If rst.NoMatch = False Then
  10.      Me.Bookmark = rst.Bookmark
  11. End If
  12.  
  13. rst.Close
  14. Set rst = Nothing
  15.  
  16. End Sub
  17.  
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
Dec 21 '07 #2

jaxjagfan
Expert 100+
P: 254
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.
Dec 21 '07 #3

patjones
Expert 100+
P: 931
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.
Dec 21 '07 #4

Post your reply

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