Bound Combo Box problem

[jennik]

I have a bound combo box on a form which displays customer names - custID, firstname, lastname. The combo box is bound by custID and all is working OK if I keep the recordsource query unsorted. But as soon as I sort the query by last name instead of the correct customer being displayed I get the first customer of that surname. What am I doing wrong???

For example:
Cust ID Last Name First Name
1 Smith Fred
2 Smith Bill

If my record contains custid=1 it will actually display Smith, Bill as this is the first Smith sorted alphebetically.

Thanks in Advance

 

[scriverb]

How are you selecting the record to be displayed based upon the selection of the combobox. Please display the code behind the AfterUpdate event procedure of the ComboBox.

This would help

Bob Scriver

 

[jennik]

I have no code in the AfterUpdate event procedure of the combobox. This is a bound combobox. I have set the controlsource of the combobox to the custID. The form contains Course Registration data such as date of registration, fee amount and the customer which is in a combo box. The rowsource of the combobox is "SELECT custid, lastname, firstname FROM customer ORDER BY lastname, firstname" where the boundcolumn is set to 1. But only the last name and first names are visible. If I leave off the ORDER BY I dont have a problem, but I need the data sorted. I hope this explains what I am trying to do. Thanks again.

Jenni

 

[scriverb]

Okay, we are getting closer. I have recreated your data base but need a little more. The main form is populated by the table Customer, correct? And the bound ComboBox has a ControlSource of CustID, correct? The table has a field CustID which is indexed with No Duplicates, correct?

You are trying to have the selection of the combo box refresh the screen with the data from table Customer with the selection from the ComboBox. There must be some code in the combobox that performs this selection. It just doesn't happen by just picking a selection by itself. If you are currently viewing record number 1 and you select Bill Smith from the ComboBox all you are doing is changing the field CustID on record number to the value of 2 which would cause a Dup index error. No requery, no find record, no bookmark process, etc. One of these are necessary to refresh with the record selected in the ComboBox. Can you select another SurName and have the rest of the data change to the data for the first record with that surname? If yes, then there is some process after selecting the combo box that performs that lookup.

Look for that and display it. Usually, the combobox being used in selecting records will have code built into the AfterUpdate procedure that performs the selection process. From what you describe by having the ComboBox be a bound form you are merely changing the value of the field CustID on the current record being displayed, just as if you typed it into a regular text box.

Please look closer and determine how the records are being selected. My inclination is that they are not. We can add that feature with no problem, just need to understand better.

Bob Scriver