ComboBox to find record (A2K)

Topic

New Reply

I found this tip (Author Allen Browne), which looks quite nifty, however it is falling over, can anyone help please. The idea is that an Unbound Combo Box can be used as a means of record navigation.

AfterUpdate Event Procedure (the example show the combo named as cboMoveTo and the field Customer Name as the 1st part of the loockup)

Private Sub cboMoveTo_AfterUpdate()
Dim Rst As Recordset
Set Rst = Me.RecordsetClone
Rst.findfirst “[Customer Name] = ‘ ” & Me![cboMoveTo] & ” ‘ ”
If Not Rst.NoMatch Then Me.Bookmark = Rst.Bookmark
End Sub

However it returns a compile error on the part Me![cboMoveTo] which is method or data member not found.

Any suggestions please

Steve

Reply | Quote

Replies

That’s very standard code and I use that kind of thing all the time, but your error suggests that it isn’t recognizing either the name of the control or the name of the field. Is the actual name of your combobox cboMoveTo and is there a CustomerName field in your underlying recordset? If not, you’ll always get an error.

Reply | Quote

Hi Charlotte,

Thanks for the quick response. I think I was getting the error because I left the . out between rst and findfirst. However even though I am now not getting the error message nothing seems to happen, perhaps I am expecting the wrong thing.

To try this out I made a form based on my Customer table and in the header info put the combo box and named it cboMoveTo and then put the code into the AfterUpdate Property.

What I expected was that when I selected a name from the cboMoveTo the rest of the fields would be completed on the form.

Is that right or am I missing the point of it.

TIA

Steve

Reply | Quote

There was a period in the appropriate position in the code you posted here. If the other fields are bound, then the statement “Me.Bookmark = Rst.Bookmark” should cause the form to display the appropriate record … unless it doesn’t find a match for “[Customer Name] = ‘ ” & Me![cboMoveTo] & ” ‘ ”

Is the combobox populated with just the customer name, or is the first column the CustomerID? In that case, you’ll never get a match using the Customer table in the Northwind sample unless you change your criteria to “[CustomerID]= ‘” & Me!cboMoveTo & “‘”

Oh, you also have an extra space between the combobox value and the closing single quote in your existing criteria, which could also cause you problems.

Reply | Quote

Hi Charlotte

Thanks for spareing the time to help. I have the combobox set to lookup all of the records from my Customers table but only have it set so that when the combobox is clicked only the CompanyName shows up (o”;1″;0″ etc.). I have then placed text boxes on the form, the first of which is called CompanyName. My expectation was that one I picked the CustomerName from the combo box all of these other fields would be populated.

To try not to bother you I opened the N/W db but on all of the forms the only one with a combo is the ‘orders’ form, however the afterupdate event is nothing like what I was trying to do. I copied this piece of code though and then named boxes appropriately on a new form and even that did not work.

If you could tell me which form in N/W this example is in so I can try to solve it.

TIA

Steve

Reply | Quote

[indent]

---

I have the combobox set to lookup all of the records from my Customers table

---

[/indent]Sorry, but I’m not sure what you mean by this. The normal way to set this up is with a regular bound form that returns all the records in the records. The combobox itself is unbound so that in the afterupdate event, the code looks for a matching CustomerID and then moves to that record. All you need in the combobox is CustomerID and CompanyName, the value you want to match and the value you want to select. Everything else is going to be ignored for these purposes.

I haven’t looked at the Northwind sample in years unless I wanted something specific from it, so I have no idea where such a button might be. Where did you get the code from? It’s entirely possible that nothing like that exists and it’s simply a sample that uses those table because they wind up installed automatically when you install Access.

Reply | Quote