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]

Create a new control called something like txtSSNFind, move the code to the new control's After Update, but with some small changes, like so:

Private Sub txtSSNFind_AfterUpdate(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "SSN='" & Me.txtSSNFind & "'"

If Not rs.NoMatch Then
    'Me.txtSSNFind.Undo
    'Cancel = True
    MsgBox "Found"
    Me.Bookmark = rs.Bookmark
Else 
    Me.SSN=Me.txtSSNFind
End If

End Sub

Note that Me.Undo gets changed.

 

[mytaurus2000]

So should I create a new txt field, or add this to the current one.

 

[Remou]

I think a new, unbound field is the best bet. Get rid of the code in the current SSN control, and put the code above in the new control.

 

[mytaurus2000]

Please grant me a little more patience. I have another error message

Procedure declaration doesn't match description of event or procedure having the same name.

I entered the following code:

Private Sub txtSSNFind_AfterUpdate(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "SSN='" & Me.txtSSNFind & "'"

If Not rs.NoMatch Then
Me.txtSSNFind.Undo
Cancel = True
MsgBox "Found"
Me.Bookmark = rs.Bookmark
Else
Me.SSN = Me.txtSSNFind
End If

End Sub

 

[PHV]

Private Sub txtSSNFind_AfterUpdate([!]Cancel As Integer[/!])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Sorry, my fault. I should have removed the 'Cancel' bit after pasting:

Private Sub txtSSNFind_AfterUpdate(Cancel As Integer)

 

[mytaurus2000]

Now it is saying that variable is not defined and Cancel is highlighted.

If Not rs.NoMatch Then
Me.txtSSNFind.Undo
Cancel = True
MsgBox "Found"
Me.Bookmark = rs.Bookmark
Else
Me.SSN = Me.txtSSNFind
End If

 

[Remou]

This ' (single quote) means a comment. In the sample I posted, these two lines:
'Me.txtSSNFind.Undo
'Cancel = True
Are commented out.

 

[mytaurus2000]

The error is saying that 'Method or data member not found'

Me.SSN = Me.txtSSNFind

SSN is highlighted

 

[Remou]

You used to have an SSN. What happened to it?

 

[mytaurus2000]

I copied the code from above.

Private Sub txtSSNFind_AfterUpdate(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "SSN='" & Me.txtSSNFind & "'"

If Not rs.NoMatch Then
Me.txtSSNFind.Undo
Cancel = True
MsgBox "Found"
Me.Bookmark = rs.Bookmark
Else
Me.SSN = Me.txtSSNFind
End If

End Sub

 

[Remou]

I mean, you used to have a control called SSN. It is where the first 60 or so posts happened. Did you rename it or otherwise dispose of it?

 

[mytaurus2000]

Ok, I added that control back. It is searching, but I'm still running into the same problem as the above post.

It is going to the correct record. Now, lets say I look up 000-00-0000 and the fields go to that record. If I enter another SSN and its not in the database, the SSN for 000-00-000 will be changed to the 2nd SSN.

If I type a SSN that is not in the database, my current record is being updated with the SSN I just entered.

How do I stop it from updating.

 

[PHV]

Private Sub txtSSNFind_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "SSN='" & Me!txtSSNFind & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
DoCmd.RunCommand acCmdRecordsGoToNew
Me!SSN = Me!txtSSNFind
Me!txtSSNFind.SetFocus
End If
Set rs = Nothing
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Ok. You click a button called say, Add Record, which takes you to a blank record. You type an SSN into txtSSNFind, which as far as the user is concerned is masquerading as SSN. The code runs. Yes No?

You now have to do various things, such as deciding if the code can also be used to search, what should happen if the SSN is not found and so on.

 

[mytaurus2000]

Thank you again for all your help. You are truly appreciated, considering this was a major issue with my company's database.