[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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
0
gbnorton
Asked:
gbnorton
  • 7
  • 7
  • 5
  • +1
3 Solutions
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
 
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now