Using duplicate entries in a combo box

I have a combo box with 5 columns.
SalesOrder, LineItem, PartNumber, Quantity, Date

There many many records with the same SalesOrder, but each has a different LineItem.

The combo box correctly displays the records, but if there are 3 of the same SalesOrder like this:
SalesOrder, LineItem, PartNumber, Quantity, Date
S12345        1             PN1               150         9/1/2011
S12345        2             PN2               250         9/2/2011
S12345        3             PN2               350         9/3/2011
No matter which record I select, I get the data from the 1st record

I use this code in my AfterUpdate event:
Private Sub cboCUST_ORDER_ID_AfterUpdate()

        Me.cboCUST_ORDER_ID = Me.cboCUST_ORDER_ID.Column(0)
        Me.txtLINE_NO = Me.cboCUST_ORDER_ID.Column(1)
        Me.txtPART_ID = Me.cboCUST_ORDER_ID.Column(2)
        Me.txtORDER_QTY = Me.cboCUST_ORDER_ID.Column(3)
        Me.txtDESIRED_SHIP_DATE = Me.cboCUST_ORDER_ID.Column(4)

End Sub

 How can I get the data from the record I selected?

Thanks,
Brooks
gbnortonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tlovieCommented:
Does the recordset that is used to populate the combo box have a primary key? - I suspect that's what is causing your difficulty.

0
gbnortonAuthor Commented:
No primary key.  
0
tlovieCommented:
If you are able to add a primary key to that, and make that column the bound column, and hide that column, your problems may resolve.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What is the Bound Column of the Combo?
Probably 1.  Try making it 2.

mx
0
mbizupCommented:
What is the recordsource property of the combobox?

If you can add a PK, you should - as noted above, that is why you can't distinguish between items.

If you can't then you will have to find some combination of fields that makes each record unique.

Can the same line item appear under different orders?
0
mbizupCommented:
<What is the recordsource property of the combobox? >

Sorry - I meant to say rowsource property...
0
gbnortonAuthor Commented:
I don't think I can add a Primary Key to SalesOrder.  That would not allow duplicates.
0
mbizupCommented:
What is the actual rowsource property of your combo box?

If it is based on a table, you may be able to add an Autonumber field (ensures no duplicates) as a primary key.

If you are able to post your database here, we can take a look.  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try setting the Bound Column of the Combo to 2 instead of 1

mx
0
gbnortonAuthor Commented:
I set it to 2.  Now I don't get anything.  Is there something to be done in addition to changing the bound column?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Yes ... change the Column Widths property to something link

1;1;1;1;1

mx
0
gbnortonAuthor Commented:
Here is the file.  I appreciate your help.  
Diode-Shipping-Label-App-Rev-C.accdb
0
mbizupCommented:
Add a primary key to your table and adjust your combo's query and code accordingly:


Diode-Shipping-Label-App-Rev-C.accdb
0
mbizupCommented:
The primary key was initially suggested by tlovie in the first comment: http:#a36906162.  The sample above shows you how to work it into your DB.  You need to adjust:
- ColumnCount
- Add a zero-width column to your combo
- Adjust your code to use the correct column indices
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this.  No primary key is needed.  I removed the control sources from the text boxes and combo box. Your combo row source was the same table as the form's Record Source.  Bound column is back to 1.  But not really sure what you are trying to do here ...?

mx
Diode-Shipping-Label-App-Rev-C-M.accdb
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
oops ... not sure if I uploaded the right file.  Here it is.

I guess EE is dropping off part of the Name ...

mx
Diode-Shipping-Label-App-Rev-C-M.zip
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
gbnorton
Your main issue here is ... the Row Source for the combo is the same as the Record Source for the Form 'Cust_Order_Line_Local' .  And each control you have on the Form is bound to a field in the table.  This normally does not compute.  Not sure what your intent here was?

Normally, you would make a selection from a combo and then populate unbound text boxes with some of the other columns ... like you were trying to do here. But, having those text boxes bound to fields in the same table as driving the combo doesn't really make sense ...

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
Are you simply trying to find the record selected in the combo box, and bring up the record for viewing/editing?

The normal way to do this is to use bookmarks.

Also, while a Primary Key might not be necessary, it is highly recommended.  It makes your data relationships a lot easier to work with.  Access practically begs you to create primary key whenever you create a table.

I'm not positive that this is what your looking for, but this brings up a record selected in the combo box, based on a ID field (primary key).

Diode-Shipping-Label-App-Rev-C.accdb
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
btw ... I was only noting that a PK was not necessary to fix this issue per se.  Of course, PK's in general are highly recommended.

mx
0
gbnortonAuthor Commented:
I just wanted to display the record for viewing and editing.

I wasn't understanding the PK suggestion.  Using the db modified by mbizup I do now... and the bookmark.  Works great.

MX,
You solution works also.  

Thank you.
Brooks
0
mbizupCommented:
Glad to help out.

Anytime you create a table do include a PK field.  I highly recommend one that does not involve user input - the Autonumber works great.  Doing so will spare you a lot of grief, and give you better database performance to boot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.