Solved Syntax: rs.Findfirst...Not rs.NoMatch (1 Viewer)

[evictme]

Im trying to check for a record during a double-click event but Im not sure my syntax is correct; would you please take a look. Any help would be greatly appreciated...
I would like to find the record, if it exists based on the parameters, and open it in the form.

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Employee Reviews")

rs.FindFirst ("EmployeeID=" & Me.EmployeeID & "AND Year = '" & "2021" & "AND Quarter = '" & "2nd Qrtr" & "'")
If Not rs.NoMatch Then
DoCmd.OpenForm "Quarterly-Review", acNormal
Else...

 

[Gasman]

" AND not "AND

Put the criteria into a string variable and Debug.Print it.
Then you can errors like this immediately, when correct use that in the command as well.

 

[evictme]

This

" AND not "AND

Put the criteria into a string variable and Debug.Print it.
Then you can errors like this immediately, when correct use that in the command as well.

This is what I have so far,

would this work?

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Employee Reviews")

Dim EmployeeID As Integer
Dim Year As String
Dim Quarter As String
Dim strCriteria As String

strCriteria = "EmployeeID=" & Me.EmployeeID & " AND Year = '" & "2021" & " AND Quarter = '" & "2nd Qrtr" & "'"

rs.FindFirst strCriteria
If Not rs.NoMatch Then
DoCmd.OpenForm "Quarterly-Review", acNormal

 

[Gasman]

Just try it?, line by line with breakpoints.
Only you know if year is meant to be text?

 

[evictme]

Just try it?, line by line with breakpoints.
Only you know if year is meant to be text?

It is.

 

[Solo712]

strCriteria = "EmployeeID=" & Me.EmployeeID & " AND Year = '2021'" & " AND Quarter = '2nd Qrtr'"

 

[Gasman]

It is.

So Debug.Print it and post back what that produces.?
Solo712 has spotted a missing '

 

[evictme]

So Debug.Print it and post back what that produces.?
Solo712 has spotted a missing '

Review Date DblClick 3070 - The Microsoft Access database engine does not recognize 'Year' as a valid field name or expression.

 

[Gasman]

Yeh, Year is a reserved name, you should NOT be using that. Sorry, I missed that, concentrating on the concatenation.
Enclose it with [ and ]

 

[pbaldy]

Is it actually getting to the FindFirst? I can't imagine this working without brackets due to the inadvisable space in the table name:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Employee Reviews")

 

[evictme]

Is it actually getting to the FindFirst? I can't imagine this working without brackets due to the inadvisable space in the table name:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Employee Reviews")

Im not sure. It does give me that error about the Year and I do I have a few other events that use this particular table that work fine. Here, it just gives me that error about the 'Year' field

 

[evictme]

Is it actually getting to the FindFirst? I can't imagine this working without brackets due to the inadvisable space in the table name:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Employee Reviews")

That was the issue actually, I put brackets around Employee Reviews -

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Employee Reviews]")

 

[pbaldy]

If you have a table named Employee, it may have been aliasing that table and it probably doesn't have a field named Year. That's the only way I can see the code working at all.

As has been mentioned, that isn't good idea as a field name but if you keep it you will sometimes have to bracket it.

 

[pbaldy]

I wasn't sure if Access required "AS" to alias a table since I normally work with SQL Server. Tested and this works:

SELECT * FROM TableName Whatever

so my guess is you have a table named Employee and your recordset was actually pulling data from that table.

As an aside, it would be more efficient to put the criteria in the OpenRecordset SQL and test for EOF. Your code pulls the entire table over the wire and then looks for a matching record. If that table is large, that will be a performance issue.

 

[Gasman]

That was the issue actually, I put brackets around Employee Reviews -

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Employee Reviews]")

That was not just the issue?, your concatenation was incorrect as well?

 

[evictme]

If you have a table named Employee, it may have been aliasing that table and it probably doesn't have a field named Year. That's the only way I can see the code working at all.

As has been mentioned, that isn't good idea as a field name but if you keep it you will sometimes have to bracket it.

Thank you, I believe you were right. The bracketing took care of the issue. Thankfully, it is not a very large table. I am still meaning to redo the whole database with better names for everything and cleaned up coding.

 

[evictme]

That was not just the issue?, your concatenation was incorrect as well?

Right, I meant that AFTER the concatenation was corrected the continued issue was the Table name not being in brackets

 

[pbaldy]

Thank you, I believe you were right. The bracketing took care of the issue. Thankfully, it is not a very large table. I am still meaning to redo the whole database with better names for everything and cleaned up coding.

No problem.

Personally I'd still change the methodology. The table may get large in the future, and I'd just prefer to use the more efficient method out of habit. Your call though, it will obviously work as is.

 

[Pat Hartman]

If all you want to do is determine if a record exists, use dLookup() or dCount(). Opening an unrestricted recordset and using FindFirst is the worst solution.