DAO Recordset, Findfirst, Error 3070 (1 Viewer)

mjdemaris

Working on it...
Local time
Yesterday, 23:59
Joined
Jul 9, 2015
Messages
424
This seems relatively simple, but I'm not sure what is causing Access to act up.

I get the Error 3070 on "UserName" when I run this code:

Code:
Public Sub UpdateUserID()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    
    Set db = CurrentDb
    Set tdf = db.TableDefs("Users")
    Set tdf2 = db.TableDefs("RequestTemp")
    Set rst = tdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
    Set rst2 = tdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    
    With rst2
        Do Until .EOF
            If .EOF Then Exit Sub
            
            .FindFirst "[UserName] =" & rst!EnvironName
            If .NoMatch Then
                GoTo NextRecord
            End If
            
            .Edit
            !UserID = rst!UserID
            .Update
            
NextRecord:
        .MoveNext
        Loop
        
    End With
    
    
End Sub

I've double checked the table, and it does indeed have a field named 'UserName'. Both fields in both tables are Short Text, and both field names are correct. I've compacted and repaired it, but no luck. I've also set a combo box record source to the RequestTemp table's field list, and the 'UserName' field shows up on the list.

Thoughts?

Problem solved: typo; used the tdf for both recordsets! Dumb mistake...
 

mjdemaris

Working on it...
Local time
Yesterday, 23:59
Joined
Jul 9, 2015
Messages
424
Here is the code that actually does the job of searching for a user name from one table in the other table, and updates the UserID. The purpose is to match UserIDs with these records for data that is imported from Excel sheets and touched up a bit and normalized.

Code:
Public Sub UpdateUserID()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim tdf2 As DAO.TableDef
    Dim rst2 As DAO.Recordset
    
    Set db = CurrentDb
    Set tdf = db.TableDefs("RequestTemp")
    Set rst = tdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Set tdf2 = db.TableDefs("Users")
    Set rst2 = tdf2.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
    
    With rst
        Do Until .EOF
            If .EOF Then Exit Sub
            Debug.Print rst.Fields(0), rst2.Fields(1)
'            Stop
            .FindFirst "UserName =" & Chr(34) & rst2!EnvironName & Chr(34)
            If .NoMatch Then
                GoTo NextRecord
            End If
            
            .Edit
            !UserID = rst2!UserID
            .Update
            
            Do Until .EOF
                .FindNext "UserName =" & Chr(34) & rst2!EnvironName & Chr(34)
                If .NoMatch Then
                    GoTo NextRecord
                End If
                .Edit
                !UserID = rst2!UserID
                .Update
                If .EOF Then Exit Do
            Loop
'            If !PONumber = rst2!PONumber Then

'            End If
            
NextRecord:
        rst2.MoveNext
        Loop
    End With
    
    rst.Close
    rst2.Close
    Set tdf = Nothing
    Set tdf2 = Nothing
    db.Close
    
End Sub
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 06:59
Joined
Sep 12, 2006
Messages
15,614
try this

.FindFirst "[UserName] = " & chr(34) & rst!EnvironName & chr(34)
 

Users who are viewing this thread

Top Bottom