[help] search for record

kysonMok · June 1st, 2009, 03:49 AM

my access file have a form that allow user to key in the customer account number,
and after that i want the customer name to be display in a listbox.
but the prob is, the records contain not only a account number and same account number may have different customer name.
what method should i use?

i totally no idea with it coz last time i only doin excel VBA..><

thank you in advance for the help

mmcdonal · June 5th, 2009, 11:02 AM

It sounds like you will have to give the users a method to look up a Customer Number (combo?) and then display all the possible Customer Names in your list box.

I would use the combo wizard to create a combo box that looks up the Customer Number. Then make sure there is no PK associated with the Customer Number (really, this is a design flaw with the database. There should be no dupes on the customer number, and one name associated with each number.)

So you combo might have:

Code:

SELECT DISTINCT CustomerTable.CustomerNumber FROM CustomerTable ORDER BY CustomerTable.CustomerNumber

Column Count 1
Column Width 2"
Bound Column 1

Then on the After Update event of your combo box:

Code:

Dim sCustNum As String
Dim sSQL As String
 
sCustNum = Me.cboCustomerNumber
 
sSQL = "SELECT CustomerID, CustomerName FROM CustomerTable WHERE CustomerNumber = '" & sCustNum & "'"
 
Me.lstCustName.RowSource = sSQL

Make sure the List box is set up to take Table/Query as the Row Source,
Column Count 2
Bound Column 1
Column Width 0";2"
Multi Select None

Then you will want to use the list box and a button to launch the results.

Did that help?