Autofill

  • Thread starter Thread starter Ms937j
  • Start date Start date
M

Ms937j

I am working on a form with two areas that allow an user to autofill in
textboxs, On the first text box when information is entered, the
corresponding textboxes will not autofill--automatically update with data.
However, the second set will. Please help.

First textbox area On Exit coding information:

Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst, varLast As Variant
varFirst = DLookup("Firstname", "Instructor", "SSN =[SSN1] ")
varLast = DLookup("Lastname", "Instructor", "SSN =[SSN1] ")
If (Not IsNull(varFirst)) Then Me![First] = varFirst
If (Not IsNull(varLast)) Then Me![Last] = varLast
End Sub
 
Three things:

1) Names should be stored as text therefore, declare the variables to store
the names as string variables, not variants.
2) What data type is the SSN stored as in the table? That affects the syntax
used in the Dlookup statement.
3) You should use the name of the text box control on your form to set the
value, not the name of the field from the table.

Here is code if the SSN is numeric:
Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst as String
Dim varLast As String
varFirst = DLookup("[Firstname]", "Instructor", "SSN = " & [SSN1])
varLast = DLookup("[Lastname]", "Instructor", "SSN = " & [SSN1])
If (Not IsNull(varFirst)) Then Me.txtFirst.Value = varFirst 'replace
with the
name of your text box
If (Not IsNull(varLast)) Then Me.txtLast.Value = varLast 'replace with
the
name of your text box
End Sub

Here is code if the SSN is text:
Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst as String
Dim varLast As String
varFirst = DLookup("[Firstname]", "Instructor", "SSN = """ & [SSN1] &
"""")
varLast = DLookup("[Lastname]", "Instructor", "SSN = """ & [SSN1] & """")
If (Not IsNull(varFirst)) Then Me.txtFirst.Value = varFirst 'replace
with the
name of your text box
If (Not IsNull(varLast)) Then Me.txtLast.Value = varLast 'replace with
the
name of your text box
End Sub
 
My apologies, I just noticed an error with the code I sent, here is correction:

Here is code if the SSN is numeric:
Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst as String
Dim varLast As String
'replace all text box names with the actual names of the controls
'on your form
varFirst = DLookup("[Firstname]", "Instructor", "SSN = " & _
Me!txtSSN1.Value)
varLast = DLookup("[Lastname]", "Instructor", "SSN = " & _
Me!txtSSN1.Value)
If (Not IsNull(varFirst)) Then Me.txtFirst.Value = varFirst
If (Not IsNull(varLast)) Then Me.txtLast.Value = varLast
End Sub

Here is code if the SSN is text:
Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst as String
Dim varLast As String
'replace all text box names with the actual names of the controls
'on your form
varFirst = DLookup("[Firstname]", "Instructor", "SSN = """ & _
Me!txtSSN1.Value & """")
varLast = DLookup("[Lastname]", "Instructor", "SSN = """ & _
Me!txtSSN1.Value & """")
If (Not IsNull(varFirst)) Then Me.txtFirst.Value = varFirst
If (Not IsNull(varLast)) Then Me.txtLast.Value = varLast
End Sub
 
Thank you so much for all your help- Chris! I will give it a try now...
Again, thanks.

Chris said:
My apologies, I just noticed an error with the code I sent, here is correction:

Here is code if the SSN is numeric:
Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst as String
Dim varLast As String
'replace all text box names with the actual names of the controls
'on your form
varFirst = DLookup("[Firstname]", "Instructor", "SSN = " & _
Me!txtSSN1.Value)
varLast = DLookup("[Lastname]", "Instructor", "SSN = " & _
Me!txtSSN1.Value)
If (Not IsNull(varFirst)) Then Me.txtFirst.Value = varFirst
If (Not IsNull(varLast)) Then Me.txtLast.Value = varLast
End Sub

Here is code if the SSN is text:
Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst as String
Dim varLast As String
'replace all text box names with the actual names of the controls
'on your form
varFirst = DLookup("[Firstname]", "Instructor", "SSN = """ & _
Me!txtSSN1.Value & """")
varLast = DLookup("[Lastname]", "Instructor", "SSN = """ & _
Me!txtSSN1.Value & """")
If (Not IsNull(varFirst)) Then Me.txtFirst.Value = varFirst
If (Not IsNull(varLast)) Then Me.txtLast.Value = varLast
End Sub


Ms937j said:
I am working on a form with two areas that allow an user to autofill in
textboxs, On the first text box when information is entered, the
corresponding textboxes will not autofill--automatically update with data.
However, the second set will. Please help.

First textbox area On Exit coding information:

Private Sub SSN1_Exit(Cancel As Integer)
Dim varFirst, varLast As Variant
varFirst = DLookup("Firstname", "Instructor", "SSN =[SSN1] ")
varLast = DLookup("Lastname", "Instructor", "SSN =[SSN1] ")
If (Not IsNull(varFirst)) Then Me![First] = varFirst
If (Not IsNull(varLast)) Then Me![Last] = varLast
End Sub
Click to expand...
Click to expand...
 
Back
Top