auto fill a form in Microsoft Access

[Guest]

I have a form, and i would like it to auto fill two to three text
box's,(first name, last name and middle name) based on a employee id number
which is entered into the first text box. Is there any way to do this? I
would like access to match a employee id which is entered to a table , and
then auto fill the remainding fields(first name, last name, and middle name)

Thanks

 

[Guest]

Hi.

There are two easy ways to do this. Either base your form on a query that
includes the name fields, or use multiple uses of the Column property. The
first seems more straightforward in this instance.

For those instances when you'd like other fields to display based on a
choice from a combo box, include all desired fields in the Row Source of the
combo box, and set the Control Source property as follows:

=YourComboBoxName.Column(x), where x indicates the column number, beginning
with 0.

HTH
Sprinks

 

[John Vinson]

I have a form, and i would like it to auto fill two to three text
box's,(first name, last name and middle name) based on a employee id number
which is entered into the first text box. Is there any way to do this? I
would like access to match a employee id which is entered to a table , and
then auto fill the remainding fields(first name, last name, and middle name)

Thanks

If you're trying to store the name information redundantly in a second
table, DON'T. These values should exist ONLY in the Employee table.

I'd suggest basing your Combo Box (cboEmployee) on a Query:

SELECT EmployeeID, [LastName] & ", " & [FirstName] & (" " +
[MiddleName]) ORDER BY LastName, FirstName, MiddleName;

Set the column count to 2 and put a textbox on the form with a Control
Source to

=cboEmployee.Column(1)

to *display* the name (without storing it redundantly).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John solution works great but how do you take it one step more and save the
data in the database. By changing the control source I lose the ability to
save this field.

I have a form, and i would like it to auto fill two to three text
box's,(first name, last name and middle name) based on a employee id number
which is entered into the first text box. Is there any way to do this? I
would like access to match a employee id which is entered to a table , and
then auto fill the remainding fields(first name, last name, and middle name)

Thanks

If you're trying to store the name information redundantly in a second
table, DON'T. These values should exist ONLY in the Employee table.

I'd suggest basing your Combo Box (cboEmployee) on a Query:

SELECT EmployeeID, [LastName] & ", " & [FirstName] & (" " +
[MiddleName]) ORDER BY LastName, FirstName, MiddleName;

Set the column count to 2 and put a textbox on the form with a Control
Source to

=cboEmployee.Column(1)

to *display* the name (without storing it redundantly).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Lynn Trapp]

John solution works great but how do you take it one step more and save
the

data in the database. By changing the control source I lose the ability to
save this field.

Notice John's comment -- "to *display* the name (without storing it
redundantly)."

You do NOT want to store it in that table. You already have it stored and
there is no reason to store it again.

 

[Guest]

In my senaro I'm using our accounting software table to do a lookup. Item
Location and Bin Location. So If I shouldn't save this data how can I
redisplay this lookup in other forms where I have the item number saved but
not the bin?

 

[Lynn Trapp]

If you have it stored once and can use it for a lookup, then you can use the
same SQL statement to do the same lookup in another form.

 

[John Vinson]

In my senaro I'm using our accounting software table to do a lookup. Item
Location and Bin Location. So If I shouldn't save this data how can I
redisplay this lookup in other forms where I have the item number saved but
not the bin?

Your Accounting software should be able to use a Query instead of a
Table to do its lookup. If it can't, post back.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

That worked except... any form that uses this query will not allow my "delete
record" button to work. Any Idea's

 

[John Vinson]

That worked except... any form that uses this query will not allow my "delete
record" button to work. Any Idea's

See the online help for "How do I create an updateable query", or open
the Query in SQL view and post the SQL code here. Not all queries are
updateable, but a simple join query should be, *provided* you have a
unique Index (such as a Primary Key) in the "one" side table's joining
field, and a relationship defined between the two tables.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Here is the SQL view
SELECT [Bin Assignement].[Item Number], MACOLALOOKUP.picking_seq AS [Primary
Bin Location], [Bin Assignement].[Bulk Bin Location], [Bin Assignement].[Date
Entered], [Bin Assignement].[Product Date Code], [Bin Assignement].ID
FROM MACOLALOOKUP INNER JOIN [Bin Assignement] ON MACOLALOOKUP.item_no =
[Bin Assignement].[Item Number];

 

[John Vinson]

Here is the SQL view
SELECT [Bin Assignement].[Item Number], MACOLALOOKUP.picking_seq AS [Primary
Bin Location], [Bin Assignement].[Bulk Bin Location], [Bin Assignement].[Date
Entered], [Bin Assignement].[Product Date Code], [Bin Assignement].ID
FROM MACOLALOOKUP INNER JOIN [Bin Assignement] ON MACOLALOOKUP.item_no =
[Bin Assignement].[Item Number];

Which table is on the "One" side of the relationship? Is Item_No (or
Item Number) the Primary Key of that table? Have you used the
Relationships window to define a relationship between the tables, with
the "Enforce Relational Integrity" box checked?

Unless both of these conditions are met, the query will not be
updateable. If they are, it should be.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps