Finding Records with Form

[b123]

Hi guys I have a tabbed form that is updating multiple tables through linked fields in both forms. I would like to use the main form to pull up existing records or to create new ones across the subordinate forms, but the problem is that when I try type in the quote number field (this is the parent /child field) it attempts to edit the current record viewing instead of advancing to that existing record. I have looked at the property settings and can't figure out how to set this up. Please help

[pbaldy]

It sounds like you want to try the combo box wizard, choosing the third option, "Find a record...". The problem with what you have is that the text/combo box you're using is bound to the underlying table, so as you've seen it updates an existing record. The combo created by the wizard won't be bound to a field (no control source).

[b123]

I have tried that but when i use the combo box it doesnt update the subordinate forms so that they populate the information associated with the quote number selected. Currently the only way that I can get all of the other forms to cooperate is to cycle through the the records buttons at the bottom of the form. Also when I select the correct record through the combo. I notice that the record buttons dont indicate that I have actually changed records.

[pbaldy]

The normal way of keeping subforms in sync with the main form is the Master/Child link properties of the subform controls. Are those set correctly?

[b123]

Yes I believe they are. When a record in the main form is created it appears in the subform and the foriegn key field is recorded in the subform table. Also when I cycle through the records in the main form and switch to the sub form it has populated the data associated with the current record in the main form. However I can only do this using the access record control buttons to cycle through the records. When i use the combo box to select an existing record nothing happens.

[b123]

I have viewed the help file on the combo boxes and I see the problem. In the video there is a third option as you metioned before. In my combo wizard there are only two options. How can change the settings to include the third option.

[pbaldy]

I believe the wizard will only show the third option when the form is bound to a table. I've seen it not show when the form is bound to SQL. It produces code like this:

Private Sub Combo16_AfterUpdate()
   'Find the record that matches the control.
   Dim rs                      As Object

   Set rs = Me.Recordset.Clone
   rs.FindFirst "[ID] = " & str(Me![Combo16])
   Me.Bookmark = rs.Bookmark
End Sub

[b123]

I am really confused now I did what you said and bound the Page 1 form to the table. Then I added a combo box with the wizard and the third option it works great, but now I lost connectivity to the page 2. I have been trying everything that I can think of to reestablish the link between these forms/with no luck. What I am trying to do is set up a combo box on page 1. Then use the combo box to pull up a record that would populate the data associated across all forms with the current record selected. Then be able to add/edit all fields on all forms except page 1 which is the reference. I appreaciate the help you are giving me. I am new to access and have been spending many hours studying to create this program.

[pbaldy]

Can you post the db?

[b123]

I just got my problem solved. On a hunch I deleted all of the previous fields and then reset them in the 2nd page and everything is working properly. I thought I was losing my mind for a minute. Thanks so much for the help it got me thinking in the right direction.

[pbaldy]

Ah good, glad you got it working.