Auto fill of related fields in a form

[Guest]

My quandry: I wish to auto fill related fields in a form. The fields are
"employee name" and "employee number". I'd like to select the name from a
combo box (bound to a very simple two column table) which displays the emp.
name and corresponding emp. number, and then have the related employee number
autofill into the "employee number" field. I have tried many ways to skin
this cat and have come out empty handed. Any advice, please? Thank you.

 

[Van T. Dinh]

The [Order Form] in the sample database "NorthWind' shows a few different
methods to do this.

Check this sample database which should be installed with your Access
software. It is also available from Microsoft Downloads.

 

[Guest]

I'm afraid I am not seeing it.... For some reason my sample Northwind
database doesn't seem to be working as it should. I looked at the SQL code
in the [Order Form] but it didn't look like what I needed. Any suggestions?

The [Order Form] in the sample database "NorthWind' shows a few different
methods to do this.

Check this sample database which should be installed with your Access
software. It is also available from Microsoft Downloads.

--
HTH
Van T. Dinh
MVP (Access)

My quandry: I wish to auto fill related fields in a form. The fields are
"employee name" and "employee number". I'd like to select the name from a
combo box (bound to a very simple two column table) which displays the
emp.
name and corresponding emp. number, and then have the related employee
number
autofill into the "employee number" field. I have tried many ways to skin
this cat and have come out empty handed. Any advice, please? Thank you.

 

[John W. Vinson]

My quandry: I wish to auto fill related fields in a form. The fields are
"employee name" and "employee number". I'd like to select the name from a
combo box (bound to a very simple two column table) which displays the emp.
name and corresponding emp. number, and then have the related employee number
autofill into the "employee number" field. I have tried many ways to skin
this cat and have come out empty handed. Any advice, please? Thank you.

If you're trying to *COPY* the employee name into a field in your second
table... don't. The Control Source of the combo box should be the employee
number, and it will store the chosen number into that field. The first
non-zero width column of the combo could be the employee name; the user would
see the name, the computer would see the number, and both will be satisfied.

John W. Vinson [MVP]

 

[Guest]

This is what I am trying to do:

1. On a form, select an employee by name from a combo box (which displays
the employee name and corresponding number, derived from (bound to) a table /
query).

2. Then: Have the next field (corresponding employee number) autofill into
that field.

3. Then: fill in various other (unbound) fields on that form.

4. Save that data (as a record) in a table. (which is the "record source"
for this form).

5. Select that (or some other) record from this table and print the data
(only one page) onto a pre-formatted report.

The problems I'm having:

1. Autofilling the form and saving the data. When I view the data on the
screen, it doesn't save into the table. (I'm using "=comboboxname.column(#)"
to autofill, but I understand this is "view only"). How can I autofill with
data that will save in my table?

2. Printing only ONE copy (page) of a report (NOT: the entire file!).

THANKS! in advance for any advice / suggestions...

 

[John W. Vinson]

This is what I am trying to do:

1. On a form, select an employee by name from a combo box (which displays
the employee name and corresponding number, derived from (bound to) a table /
query).

2. Then: Have the next field (corresponding employee number) autofill into
that field.

The combo box does that ALL BY ITSELF. No extra textboxes and no code are
needed!

The RowSource should be a query like

SELECT EmployeeNumber, LastName & ", " & FirstName AS EmployeeName FROM
Employees ORDER BY LastName, FirstName;

The Bound Column should be 1 (to make the combo store the EmployeeNumber), and
the Control Source should be the EmployeeNumber in this form's table. The
Combo's ColumnWidths can be set to "0;1.5" to hide the employee number and
only display the name; you can use the =comboboxname.Column(0) syntax if you
want to display both the name and the number.

3. Then: fill in various other (unbound) fields on that form.

4. Save that data (as a record) in a table. (which is the "record source"
for this form).

Why *UNBOUND*? If you just use bound controls, they'll update the table
directly with no extra effort. You're just making your task more difficult by
not using the tools Access provides!

5. Select that (or some other) record from this table and print the data
(only one page) onto a pre-formatted report.

Base the Report on a query referencing
=Forms!YourFormName!txtEmployeeNumber
as a criterion to select just that one record.


John W. Vinson [MVP]

 

[Guest]

Mr. Vinson,

Thanks for all your help! This is beginning to work more along the lines of
what I was hoping for! I have one question still:

In the SQL statement below (re: the RowSource) where you have an "AS" clause
with the alias "EmployeeName", my question is: When / Where / and for What
would this alias be used?

Thanks again,

Stan