Never Smart enough

[Kelly]

Can anyone provide me with a visual basic code that I can
structure on a form that will take a look at one text box
on the form and auto fill four other text boxes with
information from a table that matches the one field on the
form???? I do not want a query or a table join to
accomplish this task I want an example of a Visual Basic
code that uses auto fill.

 

[Howard Brody]

First, I would recommend using a ComboBox instead of a TextBox. With the LimitToList property, you can make sure the user can only enter/select a value that does exist in the database

For the autofill, use DLookUps (check the Help files for details) in the AfterUpdate event of your ComboBox

Assuming the following controls
cboSSN (employee's social security number
txtFirstNam
txtLastNam
txtYouGetTheIde

Private Sub cboSSN_AfterUpdate(

' make sure a value has been selected. If not, end the su
If IsNull([cboSSN]) or [cboSSN]="" The
Exit Su
End I

' otherwise..

' declare variable
Dim strFirst As Strin
Dim strLast As Strin

' lookup and populate employee control
strFirst = DLookUp("[FirstName]","tblEmployees","[SSN]='" & [cboSSN] & "'"
strLast = DLookUp("[LastName]","tblEmployees","[SSN]='" & [cboSSN] & "'"

[txtFirstName] = strFirs
[txtLastName] = strLas

' And so on ..

End Su

Hope this helps

Howard Brod


----- Kelly wrote: ----

Can anyone provide me with a visual basic code that I can
structure on a form that will take a look at one text box
on the form and auto fill four other text boxes with
information from a table that matches the one field on the
form???? I do not want a query or a table join to
accomplish this task I want an example of a Visual Basic
code that uses auto fill

 

[PC Datasheet]

Put the following in the control source of the first textbox:

=DLookup("[NameOfFirstField]","NameOfTable","[MatchingFieldInTable] = '" _
Forms!NameOfForm!NameOfOneFoeldOnForm & "'")

Do the same for the other three textboxes.

Note - the fact that you have to do this indicates your tables might not be
designed correctly. You might want to look at that closely.