combo box to find specific records

[Agnus]

It should take less than 10 seconds to create a combo box that does this (just use the wizard!), however ... the combo boxes I have created worked intermitently and now not at all. Is this some bug in Access that only I have?
This kind of thing is a basic necesity for a user, eg select the customer's name from the combo box and the rest of the form is filled with the right details.
This is just not working for me. I wondered if anyone else had come across this, and if there is a solution?
Thanks in advance from a learner.

 

[Aivars]

Hi, Agnus!

There are codes for finding of record of form (event combobox after update):

private sub cboSelectCriteria_AfterUpdate()
dim rst as recordset

set rst=me.recordsetclone
rst.findfirst "MyField=" & cboSelectCriteria
if not rst.nomatch then
me.bookmark=rst.bookmark
end if
rst.close
set rst=Nothing
end sub

Aivars

 

[rbowes]

I assume that the combo box has more than one column and that the bound column contains the customer's ID. You can do this two ways. Place a textbox on the form with the visible property set to False. On the combobox's Afterupdate event place this code:

me.txtMyTextbox = me.cboCustomer.Column(0)'assuming the first column has the ID

me.requery

Then set your query to Forms![frmCustomers]![txtMyTextbox]

or you could do it through ADO or DAO using recordsets and placing the cboCustomer.column(0) in the where clause of the SQL statement

then iterate through the recordsets fields filling the controls with their appropriate values.



Hope that helps.

Bob

 

[Agnus]

Thanks Aivars!
the wizard creates code similar to that you suggested.
After further exploration I think the problem was that I had been changing the name given to the combo box by the wizard ... it's the only problem I can think of.
It seems to be working (for now, but I won't trust it for a while yet!)
Thanks for your time. As I get more experienced, I'll try to help others too.

Agnus