recordset.FindFirst Problems

[burns863]

Hi,

I'm relatively new to writing Access modules so please bear with me

I'm using Access 2003 and developing a module to pull data from a table, manipulate it, then use it to populate an external config file for some SCADA software. Essentially, everything works fine barring the part that has to search the table for a string.

I am using a DAO recordset, and I am using all other recordset methods without a problem (which leads me to believe that everything is fine, barring my syntax when using the FindFirst method). The problem is that it seems to find no records with the matching criteria, despite the fact that they are there. I am checking this with the .NoMatch property which always equates to be true after the findfirst method.

Thanks in advance to anyone that can help

Here is snippet of the code. There are a few commented lines which show my attempts at using FindFirst unsuccessfully. (

If it would make it easier for anyone to help, I can post the full module )

'Search all recordsets for the Memory Word in which the bit is mapped *from*
'                    Dim criteria As String
'                    criteria = ".Fields(""PROC Reg/Tag"") = " & registerANA
'                    criteria = "PROC Reg/Tag = '" & registerANA & "'"
'                    .FindFirst (criteria)
 
                    .FindLast (.Fields("PROC Reg/Tag") = registerANA)

                   
                    
                    'If a record is found
                    If recordset.NoMatch = False Then

                        'If the record is that of an Analogue tag
                        If recordset.Fields("Data Type") = "ANA" Then

                            'Is the field blank?
                            blankTag2 = IsNull(recordset.Fields("Data Tag*"))

                            If blankTag2 = False Then
                                word = recordset.Fields("Data Tag*")
                            Else
                                word = ""
                            End If

                        End If
                        
                        'Move record pointer back to the earlier set bookmark
                        recordset.Bookmark = BM
                        
                    Else
                        'No Match Found
                        word = ""
                        
                        'Move record pointer back to the earlier set bookmark
                        recordset.Bookmark = BM
                    End If

 

[namliam]

Using spaces and special characters we are, bad thing this is, yes, yes...

Try using [] around your field name, not 100% it is your only problem but should get you started...
criteria = "[PROC Reg/Tag] = '" & registerANA & "'"
.FindFirst (criteria)

 

[burns863]

Using spaces and special characters we are, bad thing this is, yes, yes...

Try using [] around your field name, not 100% it is your only problem but should get you started...
criteria = "[PROC Reg/Tag] = '" & registerANA & "'"
.FindFirst (criteria)

Thank you for your reply namliam. Unfortunately, I still recieve a 3077 Syntax Error. Any other ideas?

To be clear on what I want to do; I need to search for a record that has the "PROC Reg/Tag" field equal to the contents of the string variable 'registerANA'. The one line which doesn't get a syntax error is

.FindFirst (.Fields("PROC Reg/Tag") = registerANA)

...but it doesn't find the string in the PROC Reg/Tag field for any of the fields in the recordset. It is definitely present and so should be finding it.

Hope somebody can help, this is driving me mad

 

[burns863]

I seem to have had a bit of success. I have gone down a different path and created a seperate function with a new recordset effectively replacing the need to the .findfirst method, finding the records I need via a SQL query string. So far so good, it appears to work.

For future reference I'd still be interested in getting to the bottom of the .findfirst problems if anybody can shed any light?

Thanks

 

[namliam]

The one line which doesn't get a syntax error is

.FindFirst (.Fields("PROC Reg/Tag") = registerANA)

Check my example again...

when using findfirst the criteria must be a string...

 

[burns863]

Check my example again...

when using findfirst the criteria must be a string...

OK. Well that explains why that one line doesn't return any results.

However, I have checked your earlier example again along with some variations and it unfortunately still won't work.

Confusing

 

[namliam]

I cannot see your declarion for your recordset, however this needs to be declared as
1) DAO
2) dbOpenSnapshot

i.e. something along the lines of

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("yourtable", dbOpenSnapshot)
    criteria = "[PROC Reg/Tag] = '" & registerANA & "'"
    rs.FindFirst (criteria)

 

[burns863]

I cannot see your declarion for your recordset, however this needs to be declared as
1) DAO
2) dbOpenSnapshot

i.e. something along the lines of

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("yourtable", dbOpenSnapshot)
    criteria = "[PROC Reg/Tag] = '" & registerANA & "'"
    rs.FindFirst (criteria)

That could potentially be the issue. I declared mine as DAO, OpenDynaSet.

I'll give it a go...

 

[burns863]

I get an "Item not found in this collection" error. However, thats a step forward from what I had. It is actually implementing the FindFirst but not finding anything. I had this earlier when usiong my alternate method (new function w/recordset). Tweaking the query fixed it in the end.

Thanks namliam

 

[twoplustwo]

Is the field you are searching on spelt correctly?

 

[burns863]

Is the field you are searching on spelt correctly?

Yeah. I've checked that a fair few times I wonder whether the special characters have anything to do with it? The Field Name has a forward slash in it and the string I am searching for uses a % symbol.

Simply creating a new recordset and querying for the records I want has worked perfectly though. In some ways it has actually worked out better than using .FindFirst for me.

Thanks for all your help

 

[twoplustwo]

Is registerANA declared as the same data type as the values in [PROC Reg/Tag]? Also, I would remove the spaces and forward slash in that field name - it'll only cause you headaches!

 

[burns863]

Is registerANA declared as the same data type as the values in [PROC Reg/Tag]? Also, I would remove the spaces and forward slash in that field name - it'll only cause you headaches!

registerANA is declared as a string which I am sure is correct.

I want to change all of the field names to make them more VBA friendly but unfortunatrly the database has to remain untouched. It is a company standard which is pretty much 'set in stone'

 

[namliam]

If I misspell the column name in the findfirst criteria I get the error: Microsoft Jet database engine does not recognize 'PROD Reg/Tag' as a valid field name or expression.

A % shouldnt be a problem for access, as it doesnt mean anything special... Unless your going accross a ODBC connection.

However do I misread this or did you get it fixed in the end?

 

[burns863]

No you didn't read wrong namliam. I managed to get the module to do what I wanted it to by creating a new recordset and querying for records with [PROC Reg/Tag] = registerANA instead of using the FindFirst method. I was just eager to get to the bottom of the FindFirst method not working as I expected