combo box selects first of duplicate record.

[ChrisDo11]

i have a combo box on my form which looks up company names in my table. Each company name has its own ID(autonumber), but the "company name" field sometimes has the same company name. This is for companies that have offices in 2 different locations. both records show up in the drop down, but no matter which one i select, if always selects the first record.

This shouldn't be that hard to figure out..... i just can't get it.....

thanks to anyone willing to help....

 

[dcx693]

Not sure why this is happening, but why not change your combo box to include the company name and the location so you can be sure which one should be selected?

 

[ChrisDo11]

thanks for the reply..... i've done that, so i can see which record i'm selecting, but it still defaults back to the first record with that name.... not making any sense....

 

[dcx693]

What's the rowsource for the combobox?

 

[ChrisDo11]

SELECT [company information].[company name], [company information].[company id] FROM [company information];

i also have the following on the after-update;

Me![PO Box] = DLookup("[PO Box]", "Company Information", "[Company Name]=forms![main form].[transmittal subform].[form].[company name]")
Me![Street] = DLookup("[street]", "Company Information", "[Company Name]=forms![main form].[transmittal subform].[form].[company name]")
Me![City] = DLookup("[City]", "Company Information", "[Company Name]=forms![main form].[transmittal subform].[form].[company name]")
Me![Postal Code] = DLookup("[Postal Code]", "Company Information", "[Company Name]=forms![main form].[transmittal subform].[form].[company name]")
Me![Province] = DLookup("[province]", "Company Information", "[Company Name]=forms![main form].[transmittal subform].[form].[company name]")

I think what's happening is that once i select the company name all the Dlookup commands query the first record matching that selection. what i need is for it to look up the company name and the id, but i'm not sure of the code.....

i'm also sure there's a better and more efficient way of doing this, but again, i'm not sure how...

thanks for replying.

 

[dcx693]

Boy I'm glad you posted that code. There's a much easier way to handle what you're doing - and it will give you the right answer to boot!

Right now, your combo box has two columns in it (the [company name] and [company id] fields). You can also include all the addres information in your combo box as other columns. If you don't want to display all the information when the combo box opens, no problem. Set the column width to zero in the combo box properties and they won't be displayed - BUT they will still be available. Add those columns in the rowsource by editing the query that is feeding the company data into the control. Instead of just having the name and id, have all the address info in there.

Each combo box (or list box) has a .Column property. The first column is numbered 0, the next is column 1, etc... To get at a value in the combo box, use an expression like this:
Me.CompanyNameComboBox.Column(2)
That will take the value in the 3rd column of the combo box.

The advantage is that you don't have to use those Dlookup functions (which incidentally are quite slow).

So, for example, if we replaced those Dlookups, you can use statements like these in the After Update event of the combo box:
Me.[PO Box]=Me.CompanyNameComboBox.Column(2)
Me![Street]=Me.CompanyNameComboBox.Column(3)
etc...

 

[ChrisDo11]

works great guys! thank you both very much...... muchly appreciated.

 

[dcx693]

muchly appreciated

Muchly?

 

[ChrisDo11]

muchly appreciated = appreciated very much!