MS Access code - Combo box code

[sock]

Hi,

Someone please help on the below code.. I am new to MS Access..

Option Compare Database
Private Sub Combo2_AfterUpdate()
Dim Org As String
    Org = "Select * from StoreDat where ([Customer Number] = " & Me.Combo2 & ")"
    Me.StoreDat_subform.Form.RecordSource = Org
    Me.StoreDat_subform.Form.Requery
End Sub

when I test this code it showing a "Data type mismatch in criteria expression" Error

The customer Number is Numbers

 

[Joe4]

If you open up the StoreDat table, and go to Design View, what is the Data Type of the Customer Number field?

 

[sock]

Thanks for your Reply..

it is "Short text"

regards
Socrates

 

[xenou]

if you are selecting with raw sql (which is what you are doing when you write a sql command text), then you need to put text in single quotes.

"Select * from StoreDat where ([Customer Number] = " & chr(39) & Me.Combo2 & chr(39) & ")"

Or

"Select * from StoreDat where ([Customer Number] = '" & Me.Combo2 & "')"

 

[Joe4]

Sorry, not sure how I missed your reply earlier.

it is "Short text"

Yep, then it is text and needs to have text qualifiers (of single or double-quotes) around it, like xenou showed.

 

[sock]

Yes. it works.. here is the complete code.

Private Sub Combo2_AfterUpdate()
Dim Org As Variant
    Org = "Select * from StoreDat where ([Customer Number] = '" & Me.Combo2.Column(1) & "')"
    Me.StoreDat_subform.Form.RecordSource = Org
    Me.StoreDat_subform.Form.Requery
End Sub

Thank for your help...


regards
Sock

Me.StoreDat_subform.Form.RecordSource = Org
Me.StoreDat_subform.Form.Requery
End Sub

 

[HiTechCoach]

Hi,

Someone please help on the below code.. I am new to MS Access..

Option Compare Database
Private Sub Combo2_AfterUpdate()
Dim Org As String
    Org = "Select * from StoreDat where ([Customer Number] = " & Me.Combo2 & ")"
    Me.StoreDat_subform.Form.RecordSource = Org
    Me.StoreDat_subform.Form.Requery
End Sub

when I test this code it showing a "Data type mismatch in criteria expression" Error

The customer Number is Numbers

TIP: Use Option Explicit


You an also bind the sub form's master and child field properties on the data tab. The master will be the Combo2 and the child [Customer Number]

Then the code would be:

Option Compare Database
Option Explicit

Private Sub Combo2_AfterUpdate()
    Me.StoreDat_subform.Form.Requery
End Sub