Setting 2 or more criteria for Openning a form

[sadiqsabia]

Any Help Please.
I have a table with many fields of which

Sno is a number
name is string


Sno is one and the name is many
I have instances where the name can be same only the sno can be different.

I have a form with list boxes

1st list box display- Sno
user select from list and the 2nd list shows the names.

i am trying to open a from based on the selection from 2nd list based on name and sno.
I have to compare both as names can be same spelling.

i can open by sno only or name only but i want both criteria to be met so i use this code but i get type mismatch error

DoCmd.OpenForm "tblpic", , , ("[sno]= " & Me!List1.Column(1)) And ("[name]=" & "'" & Me!List2.Column(0) & "'")



I think the problem is because of the two type of data one is string and the other is number but i need to do this.
how should i do it. Please help.

 

[Kiwiman]

Howzit

does this work any better?

Also note that Name is a reserved word, and you may get into trouble at some stage using reserved words as field names.

DoCmd.OpenForm "tblpic", , , ("[sno]= " & Me!List1.Column(1)) And ("[name]= '" &  Me!List2.Column(0) & "'")

 

[sadiqsabia]

Howzit

does this work any better?

Also note that Name is a reserved word, and you may get into trouble at some stage using reserved words as field names.

DoCmd.OpenForm "tblpic", , , ("[sno]= " & Me!List1.Column(1)) And ("[name]= '" &  Me!List2.Column(0) & "'")

it does not work. Same error. Type mismatch

 

[Kiwiman]

howzit - Try this, extract the necessary bits.

Dim StrDocName As String, strFilter As String

Stop                        ' stop the code and step thru using F8
StrDocName = "tblpic"       ' name of the form you are opening

strFilter = "[sno] = " & Me.List1.Column(1)
strFilter = strFilter & " AND [name] = '" & Me.List2.Column(0) & "'"

Debug.Print strFilter       ' Print strFilter to the immediat window - must be open


DoCmd.OpenForm StrDocName, , , strFilter

 

[sadiqsabia]

howzit - Try this, extract the necessary bits.

Dim StrDocName As String, strFilter As String
 
Stop                        ' stop the code and step thru using F8
StrDocName = "tblpic"       ' name of the form you are opening
 
strFilter = "[sno] = " & Me.List1.Column(1)
strFilter = strFilter & " AND [name] = '" & Me.List2.Column(0) & "'"
 
Debug.Print strFilter       ' Print strFilter to the immediat window - must be open
 
 
DoCmd.OpenForm StrDocName, , , strFilter

Thankss alot. It worked. I think it is referencing to numbers and strings.

 

[Kiwiman]

it didn't help that we did not have the "AND" in a set of quote marks