still having problems with "and" in findfirst criteria statement.

[BRC]

I am still having problems with the syntax of this statement. I am
trying to join the following statements
rst.FindFirst " [event] = " & Chr(34) & Me.event & Chr(34)
and
rst.FindFirst "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#")

individually they work, but when i try to put them together as in:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#)" & _
" AND [event] = " & Chr(34) & Me.event & Chr(34) the vb
editor
says "syntax error" the problem seems to be around puncuation of the
"and " any thought s would be appreciated.
event is a string and date is a date field. "date" and "event" are
fields on the form and fields in the table
Thanks for any suggestions

 

[John W. Vinson]

I am still having problems with the syntax of this statement. I am
trying to join the following statements
rst.FindFirst " [event] = " & Chr(34) & Me.event & Chr(34)
and
rst.FindFirst "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#")

individually they work, but when i try to put them together as in:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#)" & _
" AND [event] = " & Chr(34) & Me.event & Chr(34) the vb
editor
says "syntax error" the problem seems to be around puncuation of the
"and " any thought s would be appreciated.
event is a string and date is a date field. "date" and "event" are
fields on the form and fields in the table
Thanks for any suggestions

Try defining the criteria string as a variable:

Dim strCrit As String
strCrit = "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#") & _
" AND [event] = " & Chr(34) & Me.event & Chr(34)
rst.FindFirst strCrit

This will let you step through the code and see what the string actually is.

Note one correction: you had the quotemark closing the Format string OUTSIDE
the parentheses, it should be inside.


John W. Vinson [MVP]

 

[BRC]

I am still having problems with the syntax of this statement. I am
trying to join the following statements
rst.FindFirst " [event] = " & Chr(34) & Me.event & Chr(34)
and
rst.FindFirst "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#")

individually they work, but when i try to put them together as in:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#)" & _
" AND [event] = " & Chr(34) & Me.event & Chr(34) the vb
editor
says "syntax error" the problem seems to be around puncuation of the
"and " any thought s would be appreciated.
event is a string and date is a date field. "date" and "event" are
fields on the form and fields in the table
Thanks for any suggestions

Try defining the criteria string as a variable:

Dim strCrit As String
strCrit = "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#") & _
" AND [event] = " & Chr(34) & Me.event & Chr(34)
rst.FindFirst strCrit

This will let you step through the code and see what the string actually is.

Note one correction: you had the quotemark closing the Format string OUTSIDE
the parentheses, it should be inside.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John, Thanks, that helped. Using the strcrit variable I was able to
verify that the criteria is correct. I discoved my problem is when i
click to add a new record i have filtered the recordset recordcount to
0 so my code is not looking at the complete underlying record set but
only the new record.
What i am trying to do is assure for any date there is only 1 named
event. for instance if date is 6/15/07 and event is "amMaint" that
combo can only exist once. if user tries to create a new "amMaint" for
that date it will show message that event already exists and take the
user to that record.date. I think I somehow have to remove the filter
before the recordsetclone. THanks again.
BRC

 

[John W. Vinson]

What i am trying to do is assure for any date there is only 1 named
event. for instance if date is 6/15/07 and event is "amMaint" that
combo can only exist once.

You can prevent this by creating a unique two-field Index on these two fields.

John W. Vinson [MVP]

 

[BRC]

You can prevent this by creating a unique two-field Index on these two fields.

John W. Vinson [MVP]

Thanks for all of the help. I was able to get the routine I wanted by
adding a new record with docmd.openform then docmd.gotorecord,,acnew.
this allowed cloning the complete recordsource and searching for
exiting records after the event control is updated. If the record
exists it will undo the new record and take user to the exisiting
record. I had created a new unique index before starting on this path
but the problem was it did not find the duplicate until you actually
tried to save the record. thanks again.
BRC