access combobox values not showing

Question

I am trying to create a form with 3 combo boxes on which will run a report from existing data. The combo boxes will filter down based on the data chosen in the next level up combo box.

However I'm having issues with the Batch Number Combobox not populating it's list

Now this is my VBA script:

Private Sub Form_Load()
    Me.cboDate.RowSource = ""
    Me.cboBatchNo.RowSource = ""
End Sub

Private Sub cboBottleNo_AfterUpdate()
    Dim sDateSource As String

        sDateSource = "SELECT [tblNewCC].[Date] FROM [tblNewCC]" & _
        " WHERE [tblNewCC].[BottleNo] = " & Me.cboBottleNo.Value

        Me.cboDate.RowSource = sDateSource
        Me.cboDate.Requery
End Sub

Private Sub cboDate_AfterUpdate()
    Dim sBatchSource As String

        sBatchSource = "SELECT [tblBatchTotals].[BatchNo] FROM [tblBatchTotals] INNER JOIN [tblNewCC] ON [tblBatchTotals].[RunNo]=[tblNewCC].[RunNo]" & _
        " WHERE [tblNewCC].[BottleNo] = " & Me.cboBottleNo.Value & _
        " AND [tblNewCC].[Date] = " & Me.cboDate.Value

    Me.cboBatchNo.RowSource = sBatchSource
    Me.cboBatchNo.Requery
End Sub

From what I can see this is working alright on the vba side as I can see it replacing the rowsource of the batch number combobox, and in datasheet view it is giving me results.

However the combobox isn't showing anything in it's list....

Unless I go in make a change and save the sql query again.

Any clues?

and obviously I can't actually be doing that manually as the rowsource is constantly changing from the VBA script — Bidaum92

AVG AVG · Accepted Answer · 2015-08-16T15:54:25

The last part of sBatchSource should be " AND [tblNewCC].[Date] = #" & Me.cboDate.Value & "#" As an aside, 'Date' is a terrible name for a field. It is a reserved word in Access and will eventually cause you problems. Here is a helpful link http://www.fontstuff.com/access/acctut15pfv.htm

access combobox values not showing

1 Answers