LookUp

[alex]

Question:

I have a standard Form that populates a table.

At the top of the Form are several fields (employee, supervisor, unit,
etc) based on a LookUp table, e.g., Employee Table.

Right now, the data entry clerk tabs from field to field selecting
individual values from the LookUp table; e.g., select employee; select
supervisor, select building; select unit.

I would like the Form (and hence the table being populated) to auto
populate based on the selection of one field. E.g., if the user
selects employee John Doe, the other fields related to John would auto
populate, such as his supervisor; his building; his unit.

I know that this could be accomplished with some fancy IIF(), but I
figured there's an easier way.

Now, most astute DBAs would ask: why put all this info into one table,
essentially doing so would create a 'flat file.'

What I'm trying to do is create a historical record that will most
likely be exported to Excel. E.g., if John's supervisor is Jane Doe
today, it may be someone else tomorrow-the flat file appears to be the
best way to capture this data for historical purposes.

If my output table only has John's name or ID, the database will only
give me John's current supervisor when linked to the Employee table.

I hope I haven't confused anyone.

Any thoughts?

alex

 

[Jeff Boyce]

Alex

Assuming you have already set up a well-normalized relational database
structure, you do NOT need to build a "flat file" for export to Excel.
Instead, use a query to join the related tables, select the fields you need
to export, and export the query (actually, Access exports the rows returned
by the query, not the code/query itself).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[alex]

Alex

Assuming you have already set up a well-normalized relational database
structure, you do NOT need to build a "flat file" for export to Excel.
Instead, use a query to join the related tables, select the fields you need
to export, and export the query (actually, Access exports the rows returned
by the query, not the code/query itself).

Regards

Jeff Boyce
Microsoft Office/Access MVP

















- Show quoted text -

Thanks Jeff for the response.

I'm probably a little confusing and certainly long winded.

Can you tell me if it's possible to auto populate a field (in a Form
or Table) based on the value of another field?

E.g., I have a Form requiring all kinds of input.

Part of this input is based on a table called Empoyee and the Form's
fields are based on this table via a lookup(s). For example, when you
click on Employee there's a combo box that lists all the employees in
the Employee table.

If I select John Doe, can the applicable fields in the same Form auto
populate with particular information about John, e.g., his supervisor?

I know you can do this with an event procedure; I didn't know however
if there was another way since records are related by a single user
ID.

alex

 

[Jeff Boyce]

Alex

If you are ONLY considering auto-populating the form (and NOT re-saving the
same data more than once), then "Yes, you can". (If you answer that you
want to re-record John Doe's supervisor, etc., then NO, you should not.)

In a form you have a combo box that lets you pick John Doe from a list of
employees. That combo box should be based on a query. The query needs to
not only list all the employees, but also include all the other fields
(perhaps through joins to other tables) that you'd like to be able to see
the values of.

In the combo box's AfterUpdate event, you'd add something like the
following:

Me!txtFirstName = Me!cboEmployee.Column(1)
...
Me!txtSupervisor = Me!cboEmployee.Column(4)

Note that .Column() is zero-based ... your query's SECOND field is referred
to as .Column(1).

This way, every time a new value is chosen in the Employee combo box, the
related fields are updated, based on the other (hidden) fields in the query
that feeds the combo box.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[alex]

Alex

If you are ONLY considering auto-populating the form (and NOT re-saving the
same data more than once), then "Yes, you can". (If you answer that you
want to re-record John Doe's supervisor, etc., then NO, you should not.)

In a form you have a combo box that lets you pick John Doe from a list of
employees. That combo box should be based on a query. The query needs to
not only list all the employees, but also include all the other fields
(perhaps through joins to other tables) that you'd like to be able to see
the values of.

In the combo box's AfterUpdate event, you'd add something like the
following:

Me!txtFirstName = Me!cboEmployee.Column(1)
...
Me!txtSupervisor = Me!cboEmployee.Column(4)

Note that .Column() is zero-based ... your query's SECOND field is referred
to as .Column(1).

This way, every time a new value is chosen in the Employee combo box, the
related fields are updated, based on the other (hidden) fields in the query
that feeds the combo box.

Regards

Jeff Boyce
Microsoft Office/Access MVP











- Show quoted text -

Thanks Jeff for your help. I'll give this a try.
Also, you mentioned that the combo box should be based on a query; why
not based on a table?

alex

 

[Jeff Boyce]

Alex

Combo box based on query gives you selection and sort capability. ... on a
table gets you the data in the order Access decides to give it.

Regards

Jeff Boyce
Microsoft Office/Access MVP