ComboBox Question

[The professor]

Hi,

I have a ComboBox on my form which was built using the wizard. When the drop down is activated, it will list from the Customers Table all the Customer IDs, and allow the user to jump to a particular record. It does this job perfectly. However I want it to do more than this, two things in particular.

I want the ComboBox to display the CustID for the current record when the user navigates the database. At the moment when the form is opened, the ComboBox is blank, and as you navigate through the database it stays blank unless you use it to jump to a particular record. However, if the user navigates away from that record, the combobox still continues to display the CustID for the record you 'jumped to', and not for the current record being displayed. When I try to make the CustID the control source in the ComboBox, this soon results in error messages.

Secondly, I want to be able to add new CustIDs. Is it possoible to have a ComboBox do all things? Thanks.

 

[John Big Booty]

...

I want the ComboBox to display the CustID for the current record when the user navigates the database. At the moment when the form is opened, the ComboBox is blank, and as you navigate through the database it stays blank unless you use it to jump to a particular record. However, if the user navigates away from that record, the combobox still continues to display the CustID for the record you 'jumped to', and not for the current record being displayed. When I try to make the CustID the control source in the ComboBox, this soon results in error messages.

...

The following code in the Form's On Current event should achieve this goal;

Me.YourComboName = Me.YourIDFieldName

...

Secondly, I want to be able to add new CustIDs. Is it possoible to have a ComboBox do all things? Thanks.

The sample here demonstrates one method that you can use to achieve this goal.

 

[The professor]

Hello Big John,

Thank you very much for that - very helpful, worked a treat.

I've now put a second ComboBox in place on the form. In this second box I've hidden the key, which doesn't need to be shown here, because the ComboBox lists Post Codes - but how do I get it to list the PostCodes in alpha-numerical order? I think currently its listing in oder of the the hidden key.

Cheers.

 

[John Big Booty]

Open your form in design view. Click on the combo box. Go to the Data Tab and click on the Ellipsis (...) next to the Row Source, this will open a query in Design view, now you can select which part Row Source you wish to order by and whether it is Ascending or Descending. You can also do this when you use the Combo Box Wizard to add your combo box (forth step) to your form.

 

[The professor]

Hello Big John

Once again - thanks for that, did the trick nicely and I learned a bit too! Great.

Cheers.