CODEWITHSUNDEEP
ms-accessvba
Ryan_W4588
Ryan_W4588

Reputation: 668

Access 2010 VBA - Open New Recordset - Values Unexpectedly Saved From Before Opening

I am having a script auto-detect if a user exists based on their last name and an identification number. If it exists within the database, the form is auto-completed with the correct data and will be updated upon saving. If not, a new record will be saved after the user enters in the rest of the data and pushes a save button.

The issue I am having is when the script auto-detects that a user DOES exist in the database, it saves the current data (being a last name and ID number) to the table, leaving all other fields blank.

My code:

Private Sub Last_AfterUpdate()

    Dim Check As Integer
    Dim records As Recordset
    Dim tmp As String
    Dim tmp1 As String
    Dim db As Database

    Set db = CurrentDb

    If Not IsNull(Me.ID) Then
        tmp = Me.ID
    End If

    If Not IsNull(Me.Last) Then
        tmp1 = Me.Last
    End If

    Set records = db.OpenRecordset("SELECT * from [EmailList] WHERE ID1= '" & tmp & "' AND " & "Last = '" & tmp1 & "'")
    Me.Recordset.FindFirst "ID=" & records.Fields("ID")

End Sub

Note: There is a difference between fields ID and ID1. They are two forms of identification I use.

Upvotes: 1

Views: 63

Answers (1)

Newd
Newd

Reputation: 2185

From what I can tell, you are doing this in the AfterUpdate so the record is being created before you ever check to see if the user exists. I think you want something more like this:

First a function to check if user exists: 

Public Function UserExists(intID As Integer, strLastName As String) As Boolean
    'If user exists in EmailList Table
    If Nz(DLookup("Last", "EmailList", "Last = '" & strLastName & "' AND ID = " & intID), "") <> "" Then
        UserExists= True
    Else
        UserExists= False
    End If

End Function

Then in your BeforeUpdate event you want to do something like this: 

Private Sub Last_BeforeUpdate(Cancel As Integer)

    'If user filled out both fields
    If Nz(Me.ID, "") <> "" And Nz(Me.Last, "") <> "" Then

        'If they exist in the database
        If UserExists(Me.ID, Me.Last) Then

           'Cancel saving record and go to the record with their information
           Cancel = True
           Me.Recordset.FindFirst "ID=" & records.Fields("ID")
        End If

    Else 'User didn't fill out both fields.
       MsgBox "You must fill out ID and Last field"
    End If

End Sub

This is rough coding but I feel like this is the direction you will want to take to accomplish what you are looking for. Logic will need to be adjusted for your situation.

Upvotes: 1

Related Questions