Auto Populate Fields on a Data Load Form

[puddytat99]

I have a form that is used to populate a table (tblEntities). I would
like my user to be able to list an ID and have several fields in the
form autofilled by finding the ID in a different table (tblNames) and
returning that person's Name field, Address field, etc. into the form
so that my user does not have to type all that information in.

As a background, this is not a relational database. The Names table
has over 6000 rows so I need my user to be able to type in the 4 digit
ID and have the fields returned.

I really don't have a clue how to do this. Hope someone can give me
direction.

 

[John W. Vinson]

I have a form that is used to populate a table (tblEntities). I would
like my user to be able to list an ID and have several fields in the
form autofilled by finding the ID in a different table (tblNames) and
returning that person's Name field, Address field, etc. into the form
so that my user does not have to type all that information in.

As a background, this is not a relational database. The Names table
has over 6000 rows so I need my user to be able to type in the 4 digit
ID and have the fields returned.

I really don't have a clue how to do this. Hope someone can give me
direction.

Are you ABSOLUTELY CERTAIN that you want to use Microsoft Access (a relational
database development environment), avoiding using all of its relational
features? In particular, do you want to store the names data redundantly, so
that the same name exists in both tblNames and also in tblEntities, requiring
that you find all of the instances of a name if you should ever need to
correct a spelling error or handle a change of name? And are you certain that
you want the user to manually look up a numeric ID, rather than simply picking
a name from a combo box?

If so... I feel sorry for your users, but here's how you could do it. Put code
in the AfterUpdate event of the textbox such as

Private Sub txtLookupID_AfterUpdate()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblNames")
rs.FindFirst "[ID] = " & Me!txtLookupId
If rs.NoMatch Then
MsgBox "Nonexistant ID, try another", vbOKOnly
Else
Me!txtLastName = rs!LastName
Me!txtFirstName = rs!FirstName
<and so on through all the fields>
End If
End Sub

Use your own table, field and control names of course.

 

[Clifford Bass]

Hi,

What is the purpose of storing the same data twice? If you have an ID
in one table and the same ID in another table, that refers to the item in the
first table, it is a relational database.

I am thinking it would be better to create a query that will be used
for as the record source of your form:

select tblEntities.*, tblNames.[Name], tblNames.Address, tblNames.Phone
from tblEntities inner join tblNames on tblNames.ID = tblEntities.ID;

Then in your entities form set the Locked properties of the Name,
Address, Phone, etc. fields Yes and the Tab Stop properties to No. When the
user enters the ID, and tabs out of it, it will immediately show the name,
address, etc. No double storage.

Now, if you really need to copy the data over (there can be good
reasons for doing so), make the fields from tblNames invisible. And you will
have the same fields from tblEntities, that will be visible and editable.
Use an After Update event on the ID field to copy the values from the
tblNames fields to the corresponding tblEntities fields.

Oh yes, if you really have a field named "Name", I would suggest
changing it as Name is a reserved word. While Access allows you to use
reserved words for various object names, it is better to avoid them as you
can get unexpected results. Better to use something like FirstName,
LastName, FullName or CompanyName. Do a search in Access's online help for
"reserved words" for a list of reserved words.

Hope that helps,

Clifford Bass