Automatically fill-in a field

[BrookieOU]

I am trying to do a form for billing where a person can enter a PO# and the
Project Name will automatically fill-in. I know to use AutoLoookup, but I
can't figure out how to do it and have the tables set up properly. I've read
that for
the AutoLookup to work the join field cannot 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've read all about AutoLookup and tried and tried 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

 

[Al Campagna]

BrookieOU,
Create a 2 column combo box with a query behind it's RowSource that
delivers the PONo and it's associated ProjectName.
Bind this combo on the form (ex. cboPONo) to the PONo field in your
table.
Since this combo will capture the PONo to your table, there's no need to
also capture the Project name. All we need to do is "display" the
ProjectName.
We can always re-derive the ProjectName by using it's relationship to the
PONo we captured, in any subsequent form, report, or query.

Place an unbound text control on the form with a ControlSource of...
= cboPONo.Column(1)
Every time you select a PONo from cboPONo, the text control will display
the value that's in the second column.
(combo columns are numbered 0, 1, 2, 3, etc... hence column(1) is the second
column)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[BrookieOU]

I'm sorry, but I seem to be confusing myself. I created a 2-column combo box
and it does what you say (it shows the PO# and the Project Name). I would
like to have two separate boxes if possible where you enter the PO# in one
and the Project Name will pop-up in the 2nd. I think you mighta said that
and I just can't figure it out. Your way works, but is there a way to
display both columns at the same time?

 

[John W. Vinson]

Your way works, but is there a way to
display both columns at the same time?

If you follow Al's instructions - a *SECOND TEXTBOX* with a control source
referencing the combo box - it will display both (or many) textboxes.

What exactly did you do, and what are you seeing?

 

[BrookieOU]

OK, I knew I was reading that wrong. I thought he meant if I wanted to do it
on a second, separate form. Let me try again!

Thanks!

 

[BrookieOU]

Thank you sooooo much for all your help! John your explanation helped me
figure it out!

 

[Al Campagna]

John,
Thanks for the assist...
Al Campagna

If you follow Al's instructions - a *SECOND TEXTBOX* with a control source
referencing the combo box - it will display both (or many) textboxes.

What exactly did you do, and what are you seeing?

 

[BrookieOU]

OK, I ran into another problem. What y'all suggested worked perfectly by
filling-in the Project Name field. The problem I'm having now is that it
says it creates a duplicate entry in the primary key field. Which is weird
because the 2,000 entries that I transferred over have the same Project Name
multiple times, but it won't let me add new ones? Any idea why?

John,
Thanks for the assist...
Al Campagna

 

[BrookieOU]

Sorry for the bother, but a little more playing around and I got it figured
out!

Thanks for all your help though!

OK, I ran into another problem. What y'all suggested worked perfectly by
filling-in the Project Name field. The problem I'm having now is that it
says it creates a duplicate entry in the primary key field. Which is weird
because the 2,000 entries that I transferred over have the same Project Name
multiple times, but it won't let me add new ones? Any idea why?