OpenForm with multiple criteria

S

scottmarkle

I am trying to open a form applying multiple criteria. Below is the code
that I have so far. I would like to be able to make selections from all
four, but I can't even to get the first two to work. Each one seems to work
independently but when I use them together I get a Type mismatch error. I'm
sure it must be something with the syntax I am using, but I just can't figure
it out (by the way, I am an extreme new user of access and vba). Any help
would be greatly appreciated.

Thanks,

Scott

Dim stLinkUser As String
Dim stLinkSupplier As String
Dim stLinkMatGroup As String
Dim stLinkComIncom As String


stDocName = "User Update Form"

stLinkUser = "[User]=" & "'" & Me![Combo10] & "'"
stLinkSupplier = "[LastOfSupplier Name]=" & "'" & Me![Combo48] & "'"
DoCmd.OpenForm stDocName, , , stLinkUser And stLinkSupplier
 
M

Marshall Barton

scottmarkle said:
I am trying to open a form applying multiple criteria. Below is the code
that I have so far. I would like to be able to make selections from all
four, but I can't even to get the first two to work. Each one seems to work
independently but when I use them together I get a Type mismatch error. I'm
sure it must be something with the syntax I am using, but I just can't figure
it out (by the way, I am an extreme new user of access and vba). Any help
would be greatly appreciated.

Dim stLinkUser As String
Dim stLinkSupplier As String
Dim stLinkMatGroup As String
Dim stLinkComIncom As String

stDocName = "User Update Form"

stLinkUser = "[User]=" & "'" & Me![Combo10] & "'"
stLinkSupplier = "[LastOfSupplier Name]=" & "'" & Me![Combo48] & "'"
DoCmd.OpenForm stDocName, , , stLinkUser And stLinkSupplier
Click to expand...


That specific example would work by using:

DoCmd.OpenForm stDocName, , , stLinkUser & " And " &
stLinkSupplier

But that will come apart if either one is not specified.

If you want your criteria to be optional, the code could be
more like:

Dim stCriteria As String
Dim stDocName As String
stDocName = "User Update Form"

If Not IsNull(Me!Combo10) Then
stCriteria = " And [User]=""" & Me!Combo10 & """ "
End If
If Not IsNull(Me!ComboGroup) Then
stCriteria = " And MatGroup=""" & Me!ComboMatGroup& """ "
End If
If Not IsNull(Me!Combo48) Then
stCriteria = " And [LastOfSupplier Name]=""" & Me!Combo48
& """ "
End If
If Not IsNull(Me![ComboComIncom]) Then
stCriteria = " And [ComIncom]=""" & Me![ComboComIncom] &
""" "
End If

DoCmd.OpenForm stDocName, , , Mid(stCriteria, 6)
 
S

scottmarkle

Hi Marsh,

You read my mind, that is exactly what I would like for it to do. Ignore
any criteria that is not selected.

I tried your code and it seems to work with one exception, it is only
selecting the last criteria that has a value. For example, if I have a value
for User and a value for LastOfSupplier, it is only selecting the
LastOfSupplier. What I would like for it to do is filter by User AND the
LastOfSupplier information from the selection.

Please let me know if this makes sense, and thanks a ton for your help.

Scott

Marshall Barton said:
scottmarkle said:
I am trying to open a form applying multiple criteria. Below is the code
that I have so far. I would like to be able to make selections from all
four, but I can't even to get the first two to work. Each one seems to work
independently but when I use them together I get a Type mismatch error. I'm
sure it must be something with the syntax I am using, but I just can't figure
it out (by the way, I am an extreme new user of access and vba). Any help
would be greatly appreciated.

Dim stLinkUser As String
Dim stLinkSupplier As String
Dim stLinkMatGroup As String
Dim stLinkComIncom As String

stDocName = "User Update Form"

stLinkUser = "[User]=" & "'" & Me![Combo10] & "'"
stLinkSupplier = "[LastOfSupplier Name]=" & "'" & Me![Combo48] & "'"
DoCmd.OpenForm stDocName, , , stLinkUser And stLinkSupplier
Click to expand...


That specific example would work by using:

DoCmd.OpenForm stDocName, , , stLinkUser & " And " &
stLinkSupplier

But that will come apart if either one is not specified.

If you want your criteria to be optional, the code could be
more like:

Dim stCriteria As String
Dim stDocName As String
stDocName = "User Update Form"

If Not IsNull(Me!Combo10) Then
stCriteria = " And [User]=""" & Me!Combo10 & """ "
End If
If Not IsNull(Me!ComboGroup) Then
stCriteria = " And MatGroup=""" & Me!ComboMatGroup& """ "
End If
If Not IsNull(Me!Combo48) Then
stCriteria = " And [LastOfSupplier Name]=""" & Me!Combo48
& """ "
End If
If Not IsNull(Me![ComboComIncom]) Then
stCriteria = " And [ComIncom]=""" & Me![ComboComIncom] &
""" "
End If

DoCmd.OpenForm stDocName, , , Mid(stCriteria, 6)
Click to expand...
 
S

scottmarkle

Hi Marsh,

Yes, you read my mind, that is exactly what I would like for it to do.
Ignore any criteria that is not selected.

I tried your code and it seems to work with one exception, it is only
selecting the last criteria that has a value. For example, if I have a value
for User and a value for LastOfSupplier, it is only selecting the
LastOfSupplier. What I would like for it to do is filter by User AND the
LastOfSupplier information from the selection.

Please let me know if this makes sense, and thanks a ton for your help.

Scott

Marshall Barton said:
scottmarkle said:
I am trying to open a form applying multiple criteria. Below is the code
that I have so far. I would like to be able to make selections from all
four, but I can't even to get the first two to work. Each one seems to work
independently but when I use them together I get a Type mismatch error. I'm
sure it must be something with the syntax I am using, but I just can't figure
it out (by the way, I am an extreme new user of access and vba). Any help
would be greatly appreciated.

Dim stLinkUser As String
Dim stLinkSupplier As String
Dim stLinkMatGroup As String
Dim stLinkComIncom As String

stDocName = "User Update Form"

stLinkUser = "[User]=" & "'" & Me![Combo10] & "'"
stLinkSupplier = "[LastOfSupplier Name]=" & "'" & Me![Combo48] & "'"
DoCmd.OpenForm stDocName, , , stLinkUser And stLinkSupplier
Click to expand...


That specific example would work by using:

DoCmd.OpenForm stDocName, , , stLinkUser & " And " &
stLinkSupplier

But that will come apart if either one is not specified.

If you want your criteria to be optional, the code could be
more like:

Dim stCriteria As String
Dim stDocName As String
stDocName = "User Update Form"

If Not IsNull(Me!Combo10) Then
stCriteria = " And [User]=""" & Me!Combo10 & """ "
End If
If Not IsNull(Me!ComboGroup) Then
stCriteria = " And MatGroup=""" & Me!ComboMatGroup& """ "
End If
If Not IsNull(Me!Combo48) Then
stCriteria = " And [LastOfSupplier Name]=""" & Me!Combo48
& """ "
End If
If Not IsNull(Me![ComboComIncom]) Then
stCriteria = " And [ComIncom]=""" & Me![ComboComIncom] &
""" "
End If

DoCmd.OpenForm stDocName, , , Mid(stCriteria, 6)
Click to expand...
 
S

scottmarkle

Hi Marsh,

You read my mind, that is exactly what I would like for it to do. Ignore
any criteria that is not selected.

I tried your code and it seems to work with one exception, it is only
selecting the last criteria that has a value. For example, if I have a value
for User and a value for LastOfSupplier, it is only selecting the
LastOfSupplier. What I would like for it to do is filter by User AND the
LastOfSupplier information from the selection.

Please let me know if this makes sense, and thanks a ton for your help.

Scott

Marshall Barton said:
scottmarkle said:
I am trying to open a form applying multiple criteria. Below is the code
that I have so far. I would like to be able to make selections from all
four, but I can't even to get the first two to work. Each one seems to work
independently but when I use them together I get a Type mismatch error. I'm
sure it must be something with the syntax I am using, but I just can't figure
it out (by the way, I am an extreme new user of access and vba). Any help
would be greatly appreciated.

Dim stLinkUser As String
Dim stLinkSupplier As String
Dim stLinkMatGroup As String
Dim stLinkComIncom As String

stDocName = "User Update Form"

stLinkUser = "[User]=" & "'" & Me![Combo10] & "'"
stLinkSupplier = "[LastOfSupplier Name]=" & "'" & Me![Combo48] & "'"
DoCmd.OpenForm stDocName, , , stLinkUser And stLinkSupplier
Click to expand...


That specific example would work by using:

DoCmd.OpenForm stDocName, , , stLinkUser & " And " &
stLinkSupplier

But that will come apart if either one is not specified.

If you want your criteria to be optional, the code could be
more like:

Dim stCriteria As String
Dim stDocName As String
stDocName = "User Update Form"

If Not IsNull(Me!Combo10) Then
stCriteria = " And [User]=""" & Me!Combo10 & """ "
End If
If Not IsNull(Me!ComboGroup) Then
stCriteria = " And MatGroup=""" & Me!ComboMatGroup& """ "
End If
If Not IsNull(Me!Combo48) Then
stCriteria = " And [LastOfSupplier Name]=""" & Me!Combo48
& """ "
End If
If Not IsNull(Me![ComboComIncom]) Then
stCriteria = " And [ComIncom]=""" & Me![ComboComIncom] &
""" "
End If

DoCmd.OpenForm stDocName, , , Mid(stCriteria, 6)
Click to expand...
 
M

Marshall Barton

My bad, sorry. The stCriteria lines should all look like:

stCriteria = stCriteria & " And . . .

That's supposed to be all on one line.
--
Marsh
MVP [MS Access]

You read my mind, that is exactly what I would like for it to do. Ignore
any criteria that is not selected.

I tried your code and it seems to work with one exception, it is only
selecting the last criteria that has a value. For example, if I have a value
for User and a value for LastOfSupplier, it is only selecting the
LastOfSupplier. What I would like for it to do is filter by User AND the
LastOfSupplier information from the selection.

Marshall Barton said:
scottmarkle said:
I am trying to open a form applying multiple criteria. Below is the code
that I have so far. I would like to be able to make selections from all
four, but I can't even to get the first two to work. Each one seems to work
independently but when I use them together I get a Type mismatch error. I'm
sure it must be something with the syntax I am using, but I just can't figure
it out (by the way, I am an extreme new user of access and vba). Any help
would be greatly appreciated.

Dim stLinkUser As String
Dim stLinkSupplier As String
Dim stLinkMatGroup As String
Dim stLinkComIncom As String

stDocName = "User Update Form"

stLinkUser = "[User]=" & "'" & Me![Combo10] & "'"
stLinkSupplier = "[LastOfSupplier Name]=" & "'" & Me![Combo48] & "'"
DoCmd.OpenForm stDocName, , , stLinkUser And stLinkSupplier
Click to expand...


That specific example would work by using:

DoCmd.OpenForm stDocName, , , stLinkUser & " And " &
stLinkSupplier

But that will come apart if either one is not specified.

If you want your criteria to be optional, the code could be
more like:

Dim stCriteria As String
Dim stDocName As String
stDocName = "User Update Form"

If Not IsNull(Me!Combo10) Then
stCriteria = " And [User]=""" & Me!Combo10 & """ "
End If
If Not IsNull(Me!ComboGroup) Then
stCriteria = " And MatGroup=""" & Me!ComboMatGroup& """ "
End If
If Not IsNull(Me!Combo48) Then
stCriteria = " And [LastOfSupplier Name]=""" & Me!Combo48
& """ "
End If
If Not IsNull(Me![ComboComIncom]) Then
stCriteria = " And [ComIncom]=""" & Me![ComboComIncom] &
""" "
End If

DoCmd.OpenForm stDocName, , , Mid(stCriteria, 6)
Click to expand...
Click to expand...
 
S

scottmarkle

Hi Marsh,

Thank you very much. It worked like a charm.

Scott

Marshall Barton said:
My bad, sorry. The stCriteria lines should all look like:

stCriteria = stCriteria & " And . . .

That's supposed to be all on one line.
--
Marsh
MVP [MS Access]

You read my mind, that is exactly what I would like for it to do. Ignore
any criteria that is not selected.

I tried your code and it seems to work with one exception, it is only
selecting the last criteria that has a value. For example, if I have a value
for User and a value for LastOfSupplier, it is only selecting the
LastOfSupplier. What I would like for it to do is filter by User AND the
LastOfSupplier information from the selection.

Marshall Barton said:
scottmarkle wrote:
I am trying to open a form applying multiple criteria. Below is the code
that I have so far. I would like to be able to make selections from all
four, but I can't even to get the first two to work. Each one seems to work
independently but when I use them together I get a Type mismatch error. I'm
sure it must be something with the syntax I am using, but I just can't figure
it out (by the way, I am an extreme new user of access and vba). Any help
would be greatly appreciated.

Dim stLinkUser As String
Dim stLinkSupplier As String
Dim stLinkMatGroup As String
Dim stLinkComIncom As String

stDocName = "User Update Form"

stLinkUser = "[User]=" & "'" & Me![Combo10] & "'"
stLinkSupplier = "[LastOfSupplier Name]=" & "'" & Me![Combo48] & "'"
DoCmd.OpenForm stDocName, , , stLinkUser And stLinkSupplier


That specific example would work by using:

DoCmd.OpenForm stDocName, , , stLinkUser & " And " &
stLinkSupplier

But that will come apart if either one is not specified.

If you want your criteria to be optional, the code could be
more like:

Dim stCriteria As String
Dim stDocName As String
stDocName = "User Update Form"

If Not IsNull(Me!Combo10) Then
stCriteria = " And [User]=""" & Me!Combo10 & """ "
End If
If Not IsNull(Me!ComboGroup) Then
stCriteria = " And MatGroup=""" & Me!ComboMatGroup& """ "
End If
If Not IsNull(Me!Combo48) Then
stCriteria = " And [LastOfSupplier Name]=""" & Me!Combo48
& """ "
End If
If Not IsNull(Me![ComboComIncom]) Then
stCriteria = " And [ComIncom]=""" & Me![ComboComIncom] &
""" "
End If

DoCmd.OpenForm stDocName, , , Mid(stCriteria, 6)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Criteria when opening form 1
Wherecondition in openform 2
I am not "getting" it.....DoCmd. OpenForm 4
OpenForm Question 4
stLinkCriteria Statement with multiple criteria 4
OpenForm to show Last n Records 5
using the openform method with a combobox value 1
OpenForm with stCriteria 4

Top