Populating fields after DLookup 1

[mytaurus2000]

IWhen entering my form, a user types in customer id. If customer exists a message box tells user can't add record because id exists. If customer id exists, I would like the fields to automatically populate with the entered id.

Private Sub SSN_BeforeUpdate(Cancel As Integer)

Dim rstNewApp As New ADODB.Recordset
Dim x As Variant

x = DLookup("[SSN]", "tblApplicantInformation", "[SSN]='" _
& Forms!frmApplicantDataEntry!SSN & "'")

On Error GoTo SSN_Err

If Not IsNull(x) Then
Beep
MsgBox "That SSN already exist in this database. Please look it up.", vbCritical, _
"Duplicate Value"
Cancel = True

End If


SSN_Exit:
Exit Sub

SSN_Err:
MsgBox Error$
Resume SSN_Exit

End Sub

How can I edit code to populate fields with current record?

 

[Remou]

Check the properties for the SNN control on the form. On the Events tab, does it show [Event Procedure] on the After Update line?

 

[mytaurus2000]

Yes it does. What is going on if I enter a SSN that is not in the database I get the msg box "No Records". But if I eneter a SSN that is in the database it just tells me that SSN exists and look up msg box.

 

[Remou]

Does it not say:
MsgBox "Found " & rstNewApp.Fields("SSN")
?

Have you got rid of or renamed the Before Update code?

 

[mytaurus2000]

I kept the Before Update code. Should I remove it?

 

[Remou]

Yes. Or rename the event so it does not fire.

 

[mytaurus2000]

Now when I run the code, it is actually changing the values in the database. I don't want to change the records. I just want the database to find the record with that SSN# and display in the current screen.

 

[Remou]

In what way is it changing the values? Can you give an example?

 

[mytaurus2000]

I thought by changing the code to the following:

Private Sub SSN_AfterUpdate()
Dim rstNewApp As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim x As Variant
Dim appDate As Variant
Dim varFirstName As Variant
Dim varLastName As Variant
Dim varMidInit As Variant
Dim varAddress As Variant
Dim varState As Variant
Dim varCity As Variant

Dim mySQL As String

On Error GoTo SSN_Err

appDate = DLookup("[ApplicationDate]", "tblApplicantInformation", "[SSN]='" _
& Forms!frmApplicantDataEntry!SSN & "'")

varFirstName = DLookup("[FirstName]", "tblApplicantInformation", "[SSN]='" _
& Forms!frmApplicantDataEntry!SSN & "'")

If (Not IsNull(appDate)) Then Me![ApplicationDate] = appDate
If (Not IsNull(varFirstName)) Then Me![FirstName] = varFirstName

But when I run it the values actually change.

 

[mytaurus2000]

But when I run the following code without the BeforeUpdate I get the msg box "No records"

Private Sub SSN_AfterUpdate()
Dim rstNewApp As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim x As Variant

'On Error GoTo SSN_Err

Set cnn = CurrentProject.Connection
mySQL = "Select * From tblApplicantInformation Where SSN=" & Me.SSN
Set rstNewApp = New ADODB.Recordset
With rstNewApp
Set .ActiveConnection = cnn
.Source = mySQL
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

If Not rstNewApp.EOF Then
MsgBox "Found " & rstNewApp.Fields("SSN")
Set Me.Recordset = rstNewApp
Else
MsgBox "No Records"
End If

End Sub

 

[Remou]

Then what we need to do is to correct the SQL.

Replace the code with this:

Private Sub SSN_AfterUpdate()
    Dim rstNewApp As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim x As Variant
    
    'On Error GoTo SSN_Err
    
    Set cnn = CurrentProject.Connection
    mySQL = "Select * From tblApplicantInformation" ' Where SSN=" & Me.SSN
    Set rstNewApp = New ADODB.Recordset
   With rstNewApp
      Set .ActiveConnection = cnn
        .Source = mySQL
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With
    
    If Not rstNewApp.EOF Then
        MsgBox "Found " & rstNewApp.Fields("SSN")
        Set Me.Recordset = rstNewApp
     Else
        MsgBox "No Records"
     End If

End Sub

This will test that a recordset can be created. Please tell me what the message box says, after you try this.

 

[mytaurus2000]

My msg box says "No records

 

[Remou]

Is

tblApplicantInformation

The exact spelling of the table name, as it appears in the database window?

 

[mytaurus2000]

yes

 

[Remou]

After the commented on error line, type
Stop
This will allow you to step through the code by pressing F8. Do you get any errors?

 

[mytaurus2000]

Do you mean after

On Error GoTo SSN_Err

 

[Remou]

Yes, I do.

 

[mytaurus2000]

I do not get any errors.

 

[Remou]

Can we try with a different table?

mySQL = "Select * From Any Other Table Name"

 

[mytaurus2000]

I get the following error message

"No value given for one or more of the required parameters

 

[Remou]

Please paste your code here again.