ComboBox Populating TextBoxes

[Garrett!]

I have a table called Dealers that I am looking to update through a form. I did not create this table and for some reason it has the primary key as Dealer. That is a text and not an autonumber. I asked and was told that is how it needs to be. I have a combobox that a user can select a dealer and then the shipping and billing address, city, state, and zip will populate text boxes for editing. After the user is done editing I want them to click the Save Changes button to save. I don't want to update the record after they leave the text box. Currently all my text boxes and combo boxes are unbound. If I bind the combo box it gives me an error message that says "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." So I unbound the text boxes and wrote a SQL statement to Update the table. The only problem I've found is in the combo box. If I set the property List Rows to 16 it will populate all my text boxes but I can see blank columns in the drop down menu. If I set List Rows to 1 I do not populate the text boxes. Is there a way to only show one column in the combo box and populate the text boxes?

My code in case I've done something wrong:

Option Compare Database
Option Explicit


Private Sub cboDealer_AfterUpdate()
      
    Me.txtBillAdd1 = Me![cboDealer].Column(2)
    Me.txtBillAdd2 = Me![cboDealer].Column(3)
    Me.txtBillCity = Me![cboDealer].Column(4)
    Me.txtBillState = Me![cboDealer].Column(5)
    Me.txtBillZip = Me![cboDealer].Column(6)
   
    Me.txtShipAdd1 = Me![cboDealer].Column(7)
    Me.txtShipAdd2 = Me![cboDealer].Column(8)
    Me.txtShipCity = Me![cboDealer].Column(9)
    Me.txtShipState = Me![cboDealer].Column(10)
    Me.txtShipZip = Me![cboDealer].Column(11)
    
   
End Sub


Private Sub SaveChgs_Click()

Dim strSQL As String
Dim strDealer As String

    strDealer = cboDealer.Value
           
    strSQL = "UPDATE Tbl_Dealer SET Tbl_Dealer.[BillAddr1]=txtBillAdd1, "
    strSQL = strSQL & "Tbl_Dealer.[BillAddr2] = txtBillAdd2, Tbl_Dealer.[BillCity]=txtBillCity, Tbl_Dealer.[BillState]=txtBillState,"
    strSQL = strSQL & "Tbl_Dealer.[BillZip]=txtBillZip, Tbl_Dealer.[ShipAddr1]=txtShipAdd1, Tbl_Dealer.[ShipAddr2]=txtShipAdd2,"
    strSQL = strSQL & "Tbl_Dealer.[ShipCity]=txtShipCity, Tbl_Dealer.[ShipState]=txtShipState, Tbl_Dealer.[ShipZip]=txtShipZip"
    strSQL = strSQL & " WHERE Tbl_Dealer.[Dealer]= cboDealer;"
    

    DoCmd.RunSQL strSQL
    

End Sub

 

[spikepl]

List rows has nothing to do with this, You discover this type of stuff by placing the cursor in the property value in question and pressing F1.

What IS relevant is which is the Bound Column(that is one of the properties) and Column Count - another property.

 

[Garrett!]

List rows has nothing to do with this, You discover this type of stuff by placing the cursor in the property value in question and pressing F1.

What IS relevant is which is the Bound Column(that is one of the properties) and Column Count - another property.

You are correct. Changing Column Count changes how many columns I'm seeing in my combo box. So with that said is there a way to have Column Count = 1 and have it unbound and still populate the text boxes?

 

[Ranman256]

just use a query, no VB code needed.
pick combo box, form.datasource = qsFilterCombo

Ok, 1 line of code.