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]

How about something like:

    Dim rstNewApp As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim x As Variant
    
    On Error GoTo SSN_Err
    
    Set cnn = CurrentProject.Connection
    strSQL = "Select * From tblTable Where SSN=" & Me.SSN
    Set rstNewApp = New ADODB.Recordset
   With rstNewApp
      Set .ActiveConnection = cnn
        .Source = strSQL
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With
    
    If Not rstNewApp.EOF Then
        Beep
        MsgBox "That SSN already exist in this database. Please look it up.", vbCritical, _
        "Duplicate Value"
        'Cancel = True
        Set Me.Recordset = rstNewApp
     End If

 

[mytaurus2000]

I want to omit the following code

If Not rstNewApp.EOF Then
Beep
MsgBox "That SSN already exist in this database. Please look it up.", vbCritical, _
"Duplicate Value"
'Cancel = True
Set Me.Recordset = rstNewApp
End If

When the user enters the SSN, if there is a record then I want the fields on the form to populate with the values from the recordset.

 

[Remou]

Did you get a chance to look at the idea I was proposing?

 

[mytaurus2000]

Yes I did. I guess what is confusing me is once you populate the recordset with strSQL, how can I populate the text box from the record in the record set?

 

[Remou]

You did not indicate how the form is normally populated, but as you were using ADO, I assumed you were either binding a recordset to the form or filling the form from a recordset.

The above sample retrieves a recordset consisting of, hopefully, one record, which is then bound to the form. Controls that are bound to fields in the recordset will be populated. Alternatively, each field in the recordset can be assigned to a control on the form, in which case it will not be necessary to bind the recordset.

If you say how the form is normally populated, it may be possible to either suggest some other method or to improve on this method.

 

[mytaurus2000]

The form is populated from a sql statement that select's all the fields from tblApplicantInformation. The recordset should retrieve only one record b/c it is searching for the customer's SSN which is the primary key. Once an identical SSN is found then I would like for that record to be retrieved and the customer's demographics populate in the corresponding fields. e.g. last name from recordset populates last name of the form.

When I try the suggested code I receive the following error:

'No value given for one or more required parameters'

 

[Remou]

Test the SQL in the query designer. It is likely that there is some small error.

 

[mytaurus2000]

My query is as follows:

mySQL = "SELECT * FROM tblApplicantInformation WHERE ([SSN] = Forms!frmApplicantDataEntry!SSN)"

Forms!frmApplicantDataEntry is what the user types.

I would like the database to then look up the record with the corresponding SSN and then retrieve LastName, FirstName, etc.

 

[mytaurus2000]

When I run the above query, it returns the right record.

 

[Remou]

But will not work with ADO, unfortunately. Try:

[tt]mySQL = "SELECT * FROM tblApplicantInformation WHERE [SSN] = " & Forms!frmApplicantDataEntry!SSN[/tt]

Note that you need quotes if SSN is text:

[tt]mySQL = "SELECT * FROM tblApplicantInformation WHERE [SSN] = '" & Forms!frmApplicantDataEntry!SSN & "'"[/tt]

 

[mytaurus2000]

Remou

I tried with the above code, but is still not working. Program was written by another programmer and I'm not quite sure why he set up this way.

Is there a way that I can allow the user to enter SSN#, and if there is a customer with a matching SSN than the fields will populate, but
if there is not a customer with matching SSN than will allow user to add customer demographics?

 

[Remou]

That is what we are aiming for. We need to get a matching recordset. When you say 'is still not working', what error message are you getting? Is a recordset returned if you leave out the criteria? That is:

[tt]mySQL = "SELECT * FROM tblApplicantInformation"[/tt]

 

[mytaurus2000]

I'm getting my msg I coded, SSN already exists. Which is ok. I would like the user to know they are not adding a record.

I'm also getting an error message stating - The macro or function set to BeforeUpdate or ValidationRule porperty is preventing dbDatabase from saving data in the field.

 

[Remou]

I left the message in as I thought it belonged to you and you would wish to change it to show a different message, you can comment it out:

[tt] If Not rstNewApp.EOF Then
'Beep
'MsgBox "That SSN already exist in this database. Please look it up.", vbCritical, _
"Duplicate Value"
'Cancel = True
Set Me.Recordset = rstNewApp
End If[/tt]

The code would be better in the After Update event. You can move it, or just rename the Before Update event and add an After Update event, into which you can copy the code.

 

[mytaurus2000]

So in the After Update event, it will then allow the fields to contain the values from the recordset?

 

[Remou]

Hopefully. Is the recordset working?

 

[mytaurus2000]

I'm not sure that it's working. I changed the code to place in After Update event, but when I type in the SSN# and if it's in the database, it's not selecting the record nor is it populating the fields.

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
Beep
MsgBox "That SSN already exist in this database. Please look it up.", vbCritical, _
"Duplicate Value"
Cancel = True
Set Me.Recordset = rstNewApp
End If


End Sub

 

[Remou]

Let's try to find out what's happening. The code below should give a message saying whether a record was found or not.

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

 

[mytaurus2000]

I'm not getting an msg box with the above statement.