Combo Box on Form works only Once to find records

[capndave]

I have 3 combo boxes in the header of a form that I use to search for records in the related table. Each time I build the combo boxes (4 times now) and use them to find a record they only work until I close the form and reopen it. After reopening the form the drop down displays the search field for all records but selecting a record from the list no longer populates the form. I use the wizard to create the combo boxes. The form is NOT set to data entry.

Thanks

 

[GingerR]

So just making sure: You have a Recordsource for the form, right?

What's the code for each combo box? After you build the combo box code with the wizard, do you save the form design?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[capndave]

yes, I am saving the form after creating the combo box and it works but only until I close the form and open it again

This is the SQL for one of the search combos.
SELECT RECEIPT_LOG.OrderLogNumber, RECEIPT_LOG.TissueID
FROM RECEIPT_LOG;

 

[GingerR]

I meant what is the code in the AfterUpdate event of each combo box?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[capndave]

This is the only code in the afterupdate propety of one of the combo boxes

Private Sub cboFindPO_AfterUpdate()

End Sub

 

[GingerR]

So you don't have any code that will do this: "selecting a record from the list no longer populates the form"

??

The code (built by the wizard) goes into the AfterUdate event of the combo box, which after you select something from the combo box, the form moves to the record you select in the combo box. Is this your issue? If not, please explain more: you are choosing THREE things from THREE combo boxes - where is your code that "populates the form"?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[capndave]

I understand the question now. Here is the code for the after update event for one of the combo boxes. Only one combo box is used per search; the user can search by one of three fields.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[OrderLogNumber] = " & Str(Nz(Me![Combo209], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I do understand now that if I change the name of the combo box that I have to reenter the after update code as there is none once I change the name. However, even without changing the name of the combo box and with the code being present the form does not populate. In troubleshooting this I have recreated the combo boxes many times, verified the code present and now find that the form does not populate even the first time on occassion.

So frustrating- I have done this before with no problems!

Thanks for your help and patience with this novice!

CapnDave

 

[GingerR]

I still feel like I am not getting the entire story.

You have three combo boxes. A user may pick any one of the three boxes, and upon doing so, you expect the form to go to the selected record. Is this correct?

If this is correct, every one of the three combo boxes must have AFTER UPDATE code in them in order to operate as you wish. Is there After Update code properly inserted in all three combo boxes? Are the combo boxes named the same as the AfterUpdate events are named? If this is true, please tell me. If there is code, please post it. Do they work? Do any work? How do they work? How do they not work?

Does the combo box that you show the AfterUpdate code for in your previous post work properly?

When you first open the form, are there records? How many (look at the navigation bar at the bottom)?

Does the data you are searching for in the combo boxes really exist in the form's dataset? Just making sure there really is a matching record.

What is the Recordsource of your form?
Is there any other code at all for the form or any controls on the form?

You say that sometimes the form doesn't "does not populate even the first time on occassion": do you mean when you open the form, there are no records? Why not? how is the form being opened? From a button some place else? what is the button's code?

Sorry for all the picky questions but it's just troubleshooting trying to get you to give us enough information to help.





Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[capndave]

Boy, do I feel dumb! All of your "picky questions" got me to solve my own problem. Turns out the issue was that the form populated fine when it was opened from the database window, but when I opened it from anyone of several buttons on other forms it was opening in the Add Data mode.

Sorry to waste your time- I am relatively new at Access and teaching myself because it is such a powerful tool and I really enjoy working with it. Problem is it takes me so long to build a useful application because I get hung up on things like this for hours searching all the FAQs etc.

Thanks again