Search Form Using Combo box for linked form and subform (1 Viewer)

theinviter · Aug 19, 2017

Hi
i need a help;
i am having a form linked to subform.
so i want to make a new multiple search form to to filter and display the information base on my selection in dropbox.
i tried this:

Private Sub mlocation_AfterUpdate()

Dim my_ck As String
my_ck = " select * from [Dispensing 2] where ([Drug name]= " & Me.mlocation & " )"

Me.Dispensing__2_Subform.Form.RecordSource = my_ck
Me.Dispensing__2_Subform.Form.Requery

End Sub

but got this error message:
Syntax Error ( missing operation) in query expression ([Drug name]= " & Me.mlocation & " )"
then this field get highlighted yellow:
Me.Dispensing__2_Subform.Form.RecordSource = my_ck

so what is the solution please.

CJ_London · Aug 19, 2017

Drug Name implies the field is text. If it is the code needs to be

my_ck = " select * from [Dispensing 2] where ([Drug name]= '" & Me.mlocation & "' )"

theinviter · Aug 20, 2017

hi
done as you said :
my_ck = " select * from [Dispensing 2] where ([Drug Name]= '" & Me.mlocation & "' )"

got this error:

data type mismatch in criteria expression .

isladogs · Aug 20, 2017

There shouldn't be a space before the final bracket:

Code:

& "')"

Similarly no space before SELECT at start

Code:

my_ck = "select *

In fact you could omit the brackets completely:

Code:

my_ck = "select * from [Dispensing 2] where [Drug Name]= '" & Me.mlocation & "'"

CJ_London · Aug 20, 2017

the datatype mismatch error says you are trying to compare different datatypes. I only said change it if the values are text.

I do find it strange you are comparing a drug name with what appears to be a location value.

also, if you are using lookups in your table, that will add to the confusion

JHB · Aug 20, 2017

As CJ_London mention, the problem is the Lookup field type you use for the "Drug Name" field.
Try the below.

Code:

my_ck = "select * from [Dispensing  2] where [Drug name]=" & Me.mlocation[B][COLOR=Red].Column(0)[/COLOR][/B]

arnelgp · Aug 20, 2017

if your mlocation combobox has two columns where column1=id or unique key, and column2 is the drug name, you use:

my_ck="select * from [Dispensing 2] where [Drug name]= '" & me.mlocation.Column(1) & "'"

if there is only one column showing drug name:

my_ck="select * from [Dispensing 2] where [Drug name]= '" & me.mlocation & "'"

remember that that if you set the Bound Column to 1, then these are same:

mlocation = mlocation.value = mlocation.column(0)

Search Form Using Combo box for linked form and subform (1 Viewer)

theinviter

Registered User.

Attachments

CJ_London

Super Moderator

theinviter

Registered User.

isladogs

MVP / VIP

CJ_London

Super Moderator

JHB

Have been here a while

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread