Another auto populate question, hopefully simple

[Guest]

Okay, I did my research first and none of the solutions, so far, have helped.

I'm creating, from scratch, a problem report database for defective material. I planned it out on paper and have created my form with all the fields that I need.

However, I have about three or four fields that I need to auto populate based on combo boxes. By the way, I am not using primary keys because I have too many duplicate records (trust me on that one). However, I am using auto number.

The first obstacle should be simple in my mind. I want to use a combo box to select the part number and then it auto populate the next field with the part number's description. I have a table called "description list" where the part number and it's description reside. I don't want the combo box to display the description. I need it in a different box all together. The main table where all the data from the form is called "problem report." The form is bound to that table. I have made sure that the data types for both are the same in each table(text and memo respectively.) I tried using a query but I get everything, or I get a pop up window asking for the part number. That seems to work, but that's not how I want it to work. It should be automatic.

Next, I want the vendor to show up in another field. I have a table called "vendorlist" which is simply the part number (the same one from above) and the vendor that supplied it. I have established the relationships to these tables, but I can never remember which way I need to go with them.

I also want the department name of the person entering the problem report in another text box to show up when they choose their name from a combo box. Once again, I have a table called "employeelist."

Please, any help would be appreciated!!

Thanks!

 

[John Viescas]

Do the descriptions of the part numbers ever change? Does a vendor ever
change? If not, then your ProblemReport table should use the PartNumber and
VendorID as foreign keys to the Parts and Vendors tables. If you need to
display the name(s) on the form, create a query on ProblemReport that
includes the Parts table linked to ProblemReport by PartNumber and the
Vendors table linked to ProblemReport by VendorID. Include the part
description field from Parts and the Vendor Name from Vendors and display
these on the form in locked text boxes. You'll find that when you set a
PartNumber in the combo box, Access will "autolookup" the matching part
description for you. Ditto vendor. Including these field in ProblemReport
is redundant and a bad relational design.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Okay, I did my research first and none of the solutions, so far, have helped.

I'm creating, from scratch, a problem report database for defective

material. I planned it out on paper and have created my form with all the
fields that I need.

However, I have about three or four fields that I need to auto populate

based on combo boxes. By the way, I am not using primary keys because I
have too many duplicate records (trust me on that one). However, I am using
auto number.

The first obstacle should be simple in my mind. I want to use a combo box

to select the part number and then it auto populate the next field with the
part number's description. I have a table called "description list" where
the part number and it's description reside. I don't want the combo box to
display the description. I need it in a different box all together. The
main table where all the data from the form is called "problem report." The
form is bound to that table. I have made sure that the data types for both
are the same in each table(text and memo respectively.) I tried using a
query but I get everything, or I get a pop up window asking for the part
number. That seems to work, but that's not how I want it to work. It
should be automatic.

Next, I want the vendor to show up in another field. I have a table

called "vendorlist" which is simply the part number (the same one from
above) and the vendor that supplied it. I have established the
relationships to these tables, but I can never remember which way I need to
go with them.

I also want the department name of the person entering the problem report

in another text box to show up when they choose their name from a combo box.
Once again, I have a table called "employeelist."

 

[Guest]

Okay, I gave that a try and so far no luck. What I would assume when you say "use the PartNumber and VendorID as foreign keys" would be to pull that information in from their respective tables to be put in to the master table called "Problem Report" by using combo or list boxes? If not, please clarify.

The answer to your first two questions is sometimes. Sometimes part number descriptions change when an item changes rev, but the part number doesn't(not my choice by the way). And, vendors too change. There can also be multiple vendors for one item, but that's usually the exception.

So, when I created the query to get the vendor name and description, I added the three tables. I linked the three tables by item. That's what they have in common. And, the query itself works. I get a description and a vendor name for each item. However, when I go to the text box in my form and set the control source as that query and use either description or vendor name, I just get "#Name?." What gives? Am I over my head here? Do I need to remove the description and vendor fields from the master table where all the information entered on the form will end up? I think I'm just not seeing the solution? Should I use primary keys on all my tables?

Thanks, John for your help and any more would be greatly appreciated.

 

[John Viescas]

If the part description can change over time and you want to preserve the
original name of the part description in the error report, then you'll need
to copy it to the problem report record. Ditto the vendor name. You have
to write code to do that - Access won't do it for you.

So, the ProblemReport table might look like:

ReportID (PKey), PartNumber, PartDescription, VendorID, VendorName,
ReportDate, ProblemDescription, etc...

Parts:
PartID, VendorID, PartDescription, etc...

Vendors:
VendorID, VendorName, VendorAddress, etc...

In your form that edits ProblemReport, include a combo box bound to
PartNumber that has a Row Source that includes PartDescription in the second
column. In the AfterUpdate event of the combo box, execute VB code that
looks something like:

Me.PartDescription = Me.cmbPartID.Column(1)

This assumes that the field in ProblemReport is named PartDescription, the
name of the combo box is cmbPartID, and the description is in the second
column of the Row Source.

You can do a similar thing with VendorID and VendorName.

And yes, you might be in over your head... <s> Do you own any Access
books?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Okay, I gave that a try and so far no luck. What I would assume when you

say "use the PartNumber and VendorID as foreign keys" would be to pull that
information in from their respective tables to be put in to the master table
called "Problem Report" by using combo or list boxes? If not, please
clarify.

The answer to your first two questions is sometimes. Sometimes part

number descriptions change when an item changes rev, but the part number
doesn't(not my choice by the way). And, vendors too change. There can also
be multiple vendors for one item, but that's usually the exception.

So, when I created the query to get the vendor name and description, I

added the three tables. I linked the three tables by item. That's what
they have in common. And, the query itself works. I get a description and a
vendor name for each item. However, when I go to the text box in my form
and set the control source as that query and use either description or
vendor name, I just get "#Name?." What gives? Am I over my head here? Do
I need to remove the description and vendor fields from the master table
where all the information entered on the form will end up? I think I'm just
not seeing the solution? Should I use primary keys on all my tables?

 

[Guest]

That's a funny question because I do have a couple of
books but they don't cover that much detail. I few years
ago I took Access 97 training at Productivity Point and
that class, even though a week long, didn't cover any
code. Also, while in Borders last night, it occured to
me to go check out an Access book and see if it could
possibly cover this. I forgot about as soon as I thought
of it because I noticed a new edition of CAR magazine on
the newstand. I didn't think of it again until just
now. I'll give your suggestion a shot and see what I can
come up with. I didn't think it had to be this hard!!

Thanks for your help!!