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?

 

[mytaurus2000]

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

On Error GoTo SSN_Err

Stop

Set cnn = CurrentProject.Connection
mySQL = "Select * From tblAgencyInformation 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

SSN_Exit:
Exit Sub

SSN_Err:
MsgBox Error$
Resume SSN_Exit
End Sub

 

[Remou]

Back a couple of posts, I recommended commenting out the Where statement. Lets try it without the Where statement, shall we?

mySQL = "Select * From tblAgencyInformation" Where SSN=" & Me.SSN

It would be a good idea to find out whether SSN is a text field or a number field. Look in the tblApplicantInformation table.

 

[mytaurus2000]

My error message is now
Item can not be found in the collection corresponding to the requested name or ordinal.

 

[mytaurus2000]

When I changed back to the correct table name

tblApplicantInformation

My msg box now says found SSN#, but its not the one I entered.

 

[Remou]

That is great. I am so happy. Now all we have to do is find the right SSN. Did you look up the table to see if SSN is a number field or a text field?

 

[mytaurus2000]

SSN is a text

 

[Remou]

Ok, change the SQL like so:

[tt]mySQL = "Select * From tblApplicantInformation Where SSN='" & Me.SSN & "'"[/tt]

 

[mytaurus2000]

Nothing is happening. I don't receive error messages or any msg box. The SSN changes, but it doesn't reflect the rest of the record information.

 

[mytaurus2000]

Check that, now I'm getting an error message

Syntax error in string in query expression 'SSN='000-00-000"".

 

[Remou]

If the message box came back and said it had the right SSN, we are a lot further along.

Now let us examine the form. Have you fields on the form with their Control Sources set to the names of fields in tblApplicantInformation?

 

[mytaurus2000]

Fixed the above error message, now I'm receiving the msg box Found SSN 000-00-000. But when I click OK, I get an error message telling me I can't add record because it already exists.

 

[mytaurus2000]

My control source for the form is a query selecting all fields from tblApplicantInformation.

 

[Remou]

Ok. I give up. Let us forget about ADO, I do not know why you wanted to use it. Get rid of all the code. This is DAO you will need a reference to the Microsoft DAO 3.x Object Library.

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

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

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

End Sub

 

[mytaurus2000]

Ok, here we go

I entered the above code and I have a msg box that says "Found". Should we be using update b/c it keeps telling me it can't change the record?

 

[Remou]

Change:
Me.SSN.Undo
To:
Me.Undo

 

[mytaurus2000]

You are absolutely positively the best. I really appreaciate all of your post and I'm giving you as many stars as they will allow.

 

[Remou]

I am so happy (jumps up and down).

 

[mytaurus2000]

Remou,

HELP. 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.

I hope this makes sense.

 

[PHV]

For me, a control used as a navigation tool should be unbound.

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

 

[mytaurus2000]

It was intended as a navigation tool. It was set up to give an error message if the user enter a SSN that was in the database. But the user would have to exit the system and come back in to enter.

Thought I would provide a way to search for this information.