Search form with multiple combo boxes all reference query of first combo box

[Mithril28]

I have a search form with 3 combo boxes, see code below. When I open the form and select from either the second or third combo boxes I created, cboBranch or cboEntered I get:


run-time error '13', type mismatch

I thought originally I had a datatype mismatch, but that wasn't the case.

Then I noticed that when I moused over the "Me.Search_subform.Form.RecordSource =" line of either cboBranch or cboEntered sub it was showing the query associated with the first query I created, the cboCompany sub.

I verified the record source, and every property of all 3 combo boxes, neither cboBranch or cboEntered refer in any way to cboCompany.

What have I missed? Thank you!

Private Sub cboCompany_AfterUpdate()
Dim myCompany As String
myCompany = "Select * from [Main Table] where [Company Name] = '" & Me.cboCompany & "'"
Me.Search_subform.Form.RecordSource = myCompany
Me.Search_subform.Form.Requery
End Sub

Private Sub cboBranch_AfterUpdate()
Dim myBranch As Integer
myBranch = "Select * from [Main Table] where ([Branch] = " & Me.cboBranch & ")"
Me.Search_subform.Form.RecordSource = myBranch
Me.Search_subform.Form.Requery
End Sub



Private Sub cboEntered_AfterUpdate()
Dim myEnteredBy As Integer
myEnteredBy = "Select * from [Main Table] where [Entered By] = '" & Me.cboEntered & "'"
Me.Search_subform.Form.RecordSource = myEnteredBy
Me.Search_subform.Form.Requery
End Sub

[davegri]

Dim myBranch as integer , myEnteredBY as integer

is wrong. Those two are obviously strings.

[Mithril28]

Branch is an integer (it's the number of the bank, 1-50), I was messing around with EnteredBy out of desperation, I forgot to return it before posting, apologies!

Even so, why would that cause that to use the Company select statement?

[davegri]

Even so, why would that cause that to use the Company select statement?

Because that's the last (and first) time that

Me.Search_subform.Form.RecordSource = 

was correctly created. myCompany is correctly defined as string.

You have defined myBranch as an integer. It cannot equal the string "Select * from [Main Table] where ([Branch] = " & Me.cboBranch & ") without being in error. Same for myEnteredBy.
As stated in post #2, neither can be defined as an integer and set to a string value. That's error 13, type mismatch.

Perhaps you expect myBranch to be equal to the results of the select statement. It will not. It will contain the select statement itself.

Dim myCompany As String
myCompany = "Select * from [Main Table] where [Company Name] = '" & Me.cboCompany & "'"
Me.Search_subform.Form.RecordSource = myCompany

is the same as

Me.Search_subform.Form.RecordSource = "Select * from [Main Table] where [Company Name] = '" & Me.cboCompany & "'"

[Mithril28]

That fixed it!!! (taking out the two statements and melding them into one) Thank you so much!!! And thanks for the info that it was showing the myCompany query because that was the last known good. It made perfect sense once I thought about it, but I didn't til you did. Thanks!!!

[davegri]

Glad to be of some assistance. Good luck with the rest of your project!