Automatically fill-in a box

[BrookieOU]

I have a form where users enter billing information. This includes PO#,
Invoice#, Amt, so on and so forth. This is based on a query that pulls
fields from the following tables:

Project Table: ProjectID, PO#, Project Name
Billing Table: BillingID, Invoice#, Amt
Employee Table: EmployeeID, Employee Name

What I'm trying to do on the form is have a person type in the PO# and it
will automatically fill-in the box containing the project name. Any ideas on
how to do this? I've done it before in a really backwards way and wanted to
do it right this time.

Thanks!

 

[Arvin Meyer MVP]

Use an autolookup query. From an earlier version Access help file:

"For AutoLookup to work, certain conditions must be met:

· The query must be based on more than one table and the tables must have a
one-to-many relationship. (Referential integrity doesn't have to be
enforced.)
· The join field on the "one" side of the relationship must have a unique
index. A unique index means that the field is a primary key or its Indexed
property in table Design view is set to Yes (No Duplicates).

· The join field you add to the design grid must come from the table on the
"many" side of the one-to-many relationship. (In other words, it's the
foreign key for that table.) For AutoLookup to work, this field can't be a
primary key and its Indexed property can't be set to Yes (No Duplicates).
For example, in a query that includes the Customers and Orders tables, drag
to the design grid the CustomerID field from the Orders table, not the one
from the Customers table.

· The value you enter in the join field from the "many" side must already
exist in the join field from the "one" side.

When the value of the join field from the "many" side of the relationship is
added or changed in a record, Microsoft Access automatically finds and
displays the associated values from the table on the "one" side of the
relationship.

While you can always update the join field from the "many" side of a
relationship, you can update the join field from the "one" side only if you
enabled cascading updates when defining the relationship between the tables.
Either way, when you update data, Microsoft Access automatically
recalculates any totals or expressions in the query that are dependent on
the updated data."

 

[BrookieOU]

I thought you weren't supposed to use auto-lookup's?

 

[Arvin Meyer [MVP]]

You are thinking of Lookup Fields in a table. not a query lookup which is
the proper use. In this case, once the query is built, it is being used as
the recordsource of a form. That is the best practice technique for forms.

 

[BrookieOU]

Ohhhh. Thank you! I'll try it this way.

You are thinking of Lookup Fields in a table. not a query lookup which is
the proper use. In this case, once the query is built, it is being used as
the recordsource of a form. That is the best practice technique for forms.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[BrookieOU]

OK, here's the problem I'm having with what you suggested. You say that for
the AutoLookup to work the join field cannont be a Primary Key and it can't
be set to "No Duplicates". I can't wrap my head around this and the proper
way to set up tables. Here is how I currently have stuff set-up.

tblEmployees: EmployeeID (primary), First Name, Last Name
tblProjects: ProjectID (primary), PO#, Project Name
tblProjectBilling: BillingID (primary), EmployeeID (foreign), ProjectID
(foreign), invoice #, invoice amount

I would like to set up a form where the user can enter the PO# and it will
automatically enter the corresponding Project Name. The way I tried to do it
(and it didn't work) was:

I created a qryProjectBillingExtended and placed all of the fields from
tblProjectBilling and the PO# and Project Name from tblProjects and First
Name and Last Name from tblEmployees. But, as I stated earlier, it's not
filling in the blanks when I enter the PO#.

I read what you wrote and just can't figure out how to fix it. Any help
please!!! I've been trying for two weeks on my own now and just can't figure
out how to do it and I know it can't be that difficult. Maybe I'm just
overthinking it.

Thanks,
Brooke