open form multiple criteria

[Guest]

Hi!

I need help with the syntax of a Docmd.openform procedure

I want to open a form with 3 criteria
I am able to open it with 1 criterion at the time , but I can't get the
syntax right for concatenating the 3

here is my code (Sorry for some names in french...):


Private Sub Prime_Click()
On Error GoTo Err_Prime_Click

Dim message, msg, msg2, title As String
Dim stDocName As String
Dim stLinkCriteria, stLinkCriteria1, stLinkCriteria2 As String

msg = "Before entering a bonus, enter the employeeID"
title = "Error"

If Len(Trim(Nz(Me!Employé, ""))) <> 0 Then
stDocName = "Prime"
stLinkCriteria1 = "[DateTravail] = #" & Format$(Me!DateTravail,
"short date") & "#"
stLinkCriteria = "[Employé]=" & Me![Employé]
stLinkCriteria = "[CodeTravail]=" & Me![CodeTravail]

DoCmd.OpenForm stDocName, , , stLinkCriteria & stLinkCriteria1 &
stLinkCriteria2
Else
message = MsgBox(msg, vbExclamation, title)
Exit Sub
End If

Exit_Prime_Click:
Exit Sub

Err_Prime_Click:
MsgBox Err.Description
Resume Exit_Prime_Click

End Sub
-------------------------------------
The docmd part is wrong obviously.
Can someone please give me the syntax to write?
Thanks a lot

Louis Pat

 

[Douglas J Steele]

DoCmd.OpenForm stDocName, , , stLinkCriteria & " AND " & stLinkCriteria1 & "
AND " & stLinkCriteria2

or

stLinkCriteria = "[Employé]=" & Me![Employé] & " AND " & _
"[DateTravail] = #" & Format$(Me!DateTravail, "short date") & "#" & _
" AND [CodeTravail]=" & Me![CodeTravail]
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Guest]

You may have only one Where condition in an OpenForm method. It is a valid
SQL statement. You need to put all 3 of your criteria into one string with
And between each.

stLinkCriteria = "[DateTravail] = #" & Format$(Me!DateTravail, "short date") _
& "# And [Employé]=" & Me![Employé] & " And [CodeTravail]=" &
Me![CodeTravail]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Also note that your code is formatted so that Me![Employé] And
[CodeTravail] are expected to be numeric.

If they are string, then the syntax should be:

stLinkCriteria = "[DateTravail] = #" & Format$(Me!DateTravail, "short date") _
& "# And [Employé]='" & Me![Employé] & "' And [CodeTravail]='" &
Me![CodeTravail] & "'"

I have not tested this, so I cannot guarantee there are no syntax errors.

 

[Guest]

Thanks a lot guys for the fast reply!!!

Louis Pat

You may have only one Where condition in an OpenForm method. It is a valid
SQL statement. You need to put all 3 of your criteria into one string with
And between each.

stLinkCriteria = "[DateTravail] = #" & Format$(Me!DateTravail, "short date") _
& "# And [Employé]=" & Me![Employé] & " And [CodeTravail]=" &
Me![CodeTravail]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Also note that your code is formatted so that Me![Employé] And
[CodeTravail] are expected to be numeric.

If they are string, then the syntax should be:

stLinkCriteria = "[DateTravail] = #" & Format$(Me!DateTravail, "short date") _
& "# And [Employé]='" & Me![Employé] & "' And [CodeTravail]='" &
Me![CodeTravail] & "'"

I have not tested this, so I cannot guarantee there are no syntax errors.

Hi!

I need help with the syntax of a Docmd.openform procedure

I want to open a form with 3 criteria
I am able to open it with 1 criterion at the time , but I can't get the
syntax right for concatenating the 3

here is my code (Sorry for some names in french...):


Private Sub Prime_Click()
On Error GoTo Err_Prime_Click

Dim message, msg, msg2, title As String
Dim stDocName As String
Dim stLinkCriteria, stLinkCriteria1, stLinkCriteria2 As String

msg = "Before entering a bonus, enter the employeeID"
title = "Error"

If Len(Trim(Nz(Me!Employé, ""))) <> 0 Then
stDocName = "Prime"
stLinkCriteria1 = "[DateTravail] = #" & Format$(Me!DateTravail,
"short date") & "#"
stLinkCriteria = "[Employé]=" & Me![Employé]
stLinkCriteria = "[CodeTravail]=" & Me![CodeTravail]

DoCmd.OpenForm stDocName, , , stLinkCriteria & stLinkCriteria1 &
stLinkCriteria2
Else
message = MsgBox(msg, vbExclamation, title)
Exit Sub
End If

Exit_Prime_Click:
Exit Sub

Err_Prime_Click:
MsgBox Err.Description
Resume Exit_Prime_Click

End Sub
-------------------------------------
The docmd part is wrong obviously.
Can someone please give me the syntax to write?
Thanks a lot

Louis Pat