OpenForm with multiple criteria

[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

 

[Marshall Barton]

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)

 

[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

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)

 

[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

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)

 

[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

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)

 

[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.

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)

 

[scottmarkle]

Hi Marsh,

Thank you very much. It worked like a charm.

Scott

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.

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)