MS Access – ComboBox not allowing me to select an item-Collection of common programming errors

new to Access here and already spent a lot of time trying to solve this problem, so here the background:

Note: already had a look at this: ComboBox won’t allow me to select an item, but didn’t provide the answers I needed.

I have a database that allows a person to input and store customer details via forms, and currently I have 3 tables with the following relationships:

Customer – CustomerID (PK) – FirstName – LastName

Process – ProcessID (PK) – Detail – PartsUse –

Job – JobID (PK) – CustomerID (FK) – ProcessID (FK) – MachineDetail –

Customer has a 1-M relation with Job, but by extension (not sure why) Process also has a 1-M relationship with Job also. So the aim here is that a customer can have many jobs, and a job should only have one process (will need to fix that up later).

Now here’s my code to bind the ComboBox in my NewJob form – its objective is to populate the ComboBox with all the names of the customer when the form opens, and only allowing the user to enter the job details after a customer has been selected:

Private Sub Form_Open(Cancel As Integer)

    Dim db As Database
    Dim recordSet As DAO.recordSet
    Dim sql As String

    sql = "SELECT [Customer].[CustomerID], [Customer].[FirstName] & [Customer].[LastName] FROM Customer ORDER BY [CustomerID];"

    'clear all fields
    ClearJobFormFields

    'disable all controls until a customer is selected
    DisableJobFormControls

    With cmbCustomer
        .ControlSource = "Customer"
        .RowSource = sql
        .ColumnCount = 2
        .ColumnWidths = "1cm; 3cm"
        .BoundColumn = 0
    End With

    cmbCustomer.ControlSource = "Customer"
    cmbCustomer.RowSource = sql

End Sub

Just to note, each form is independent – I am not using subforms. The form this is on (NewJob) DOES have AllowEdit set to yes, and the form has no RecordSource bound to it.

The ComboBox DOES populate properly, but everytime I try to select an item, I get the error: “control cannot be edited it’s bound to unknown field Customer”.

And that’s all there is to it. Sorry if this is a common / easy-to-solve problem, but its been bugging me for days.

1. If your form has no Recordsource, your control shouldn’t (can’t) have a Controlsource. If you go to properties of the form in design mode, you will see that Customer is not a valid choice at the Controlsource property. Why don’t you have a Recordsource. Isn’t the objective of the form to input Job data?

2. As you are setting the rowsource property via VBA, you should leave this as an unbond control and scrap:

   cmbCustomer.ControlSource = "Customer"
   

   Your open form sub should be more like this:

   Private Sub Form_Open(Cancel As Integer)
   
   Dim db As Database
   Dim recordSet As DAO.recordSet
   Dim sql As String
   
       sql = "SELECT [Customer].[CustomerID], [Customer].[FirstName] & [Customer].[LastName] FROM Customer ORDER BY [CustomerID];"
   
       'clear all fields
       ClearJobFormFields
   
       'disable all controls until a customer is selected
       DisableJobFormControls
   
       With cmbCustomer
           .RowSource = sql
           .ColumnCount = 2
           .ColumnWidths = "1cm; 3cm"
           .BoundColumn = 0
       End With
   
   End Sub
   

   And then you can use the after update event of this combobox to determine whether or not to enable the job details fields:

   Private Sub cmbCustomer_AfterUpdate()
   
       'Check it's populated and set fields as necessary
       If cmbCustomer & "" = "" Then
           txtJobDetails.Enabled = 0 'Change this fieldname as required
       Else
           txtJobDetails.Enabled = -1
       End If
   
   End Sub
   

Originally posted 2013-11-09 23:34:58.