Access combobox doesn't return correct data

[bman414]

Hi all,

I'm trying to build an access form with a combobox that can search on multiple columns. I built it once and it was working as expected, then I changed the data source and it stopped working. I then tried building the old one from scratch.

There are three comboboxes that I'm using as a search function. I use the following columns (PK) Primary Key, Branch_Name, and Branch_Number.

The PK combobox works correctly, but when I select either of the other two options, they don't return the correct result. Is there something I did when recreating the form that cause the sources to get mixed up?

------------

Option Compare Database

Private Sub cboPK_AfterUpdate()
Dim PK As String
PK = "Select * from dbo_CRA_Branch_Hours " & _
     "where ([PK] = " & Me.cboPK & ")"
Me.dbo_CRA_Branch_Hours_subform.Form.RecordSource = PK
Me.dbo_CRA_Branch_Hours_subform.Form.Requery
End Sub

Private Sub cboBranchName_AfterUpdate()
Dim Branchname As String
Branchname = "Select * from dbo_CRA_Branch_Hours " & _
             "where ([Branch_Name] = '" & Me.cboBranchName & "')"
Me.dbo_CRA_Branch_Hours_subform.Form.RecordSource = Branchname
Me.dbo_CRA_Branch_Hours_subform.Form.Requery
End Sub

Private Sub cboBranchNumber_AfterUpdate()
Dim MyBranch_Number As String
MyBranch_Number = "Select * from dbo_CRA_Branch_Hours " & _
                  "where ([Branch_Number] = " & Me.cboBranchNumber & ")"
Me.dbo_CRA_Branch_Hours_subform.Form.RecordSource = MyBranch_Number
Me.dbo_CRA_Branch_Hours_subform.Form.Requery
End Sub

[twinnyfo]

First, always include "Option Explicit" at the top of your modules. You can set this as a default in your VBA editor (Tools/Options/Require Variable Declaration).

I do believe you need to include the information from the other Combo Boxes in your Query search criteria:

Option Compare Database
Option Explicit
 
Private Sub cboPK_AfterUpdate()
Dim PK As String
PK = "Select * from dbo_CRA_Branch_Hours " & _
     "where ([PK] = " & Me.cboPK & ")"
Me.dbo_CRA_Branch_Hours_subform.Form.RecordSource = PK
Me.dbo_CRA_Branch_Hours_subform.Form.Requery
End Sub
 
Private Sub cboBranchName_AfterUpdate()
Dim Branchname As String
Branchname = "Select * from dbo_CRA_Branch_Hours " & _
             "where ([Branch_Name] = '" & Me.cboBranchName & "') " & _
             "AND ([PK] = " & Me.cboPK & ")"
Me.dbo_CRA_Branch_Hours_subform.Form.RecordSource = Branchname
Me.dbo_CRA_Branch_Hours_subform.Form.Requery
End Sub
 
Private Sub cboBranchNumber_AfterUpdate()
Dim MyBranch_Number As String
MyBranch_Number = "Select * from dbo_CRA_Branch_Hours " & _
                  "where ([Branch_Number] = " & Me.cboBranchNumber & ") " & _
                  "AND ([PK] = " & Me.cboPK & ") " & _
                  "AND  ([Branch_Name] = '" & Me.cboBranchName & "')"
Me.dbo_CRA_Branch_Hours_subform.Form.RecordSource = MyBranch_Number
Me.dbo_CRA_Branch_Hours_subform.Form.Requery
End Sub

Hope this hepps!

[bman414]

thanks for the quick response!

I'll give this a try, when I originally made the form, I used similar code and it worked perfect when I searched the data using either of the comboboxes. So I'm not sure if that's the issue, but I'll give it a try.

Thanks,

[twinnyfo]

Let us know how it comes along. Remember, we can't test the code I posted, as I am just adding it freehand. It may produce an error or two, but, hopefully we can work thorugh those.

[bman414]

I applied your changes and it still didn't work. Its almost as if the Branch_Number and Branch_Name aren't querying the data. The combobox is pulling down the accurate data, but there are no results.

The PK combobox still works, but its the other two that sometimes return the incorrect value or don't return a value at all.

[PhilOfWalton]

Things to check:-

Are BranchNumbers and BranchNames Text or numbers? Also your very oddly named PK (Normally used as Primary Key - a desctiption of a field rather than a field name).

Don't forget Text needs quotes round them

Are you reading the correct Columns from the Combo Boxes?

Phil

[zmbd]

Been awhile since I've taken a look at the following article; however, it may be a good reference for what you are trying to accomplish - might D/L the database there to see if there's something you can use :-) :
home > topics > microsoft access / vba > insights > cascaded form filtering