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]

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)

--
Marsh
MVP [MS Access]

[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

[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

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

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]

scottmarkle wrote:
>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]

Hi Marsh,

Thank you very much. It worked like a charm.

Scott