populating form after selecting record

[elff]

I have a table for Purchase Orders with PONumber, POEmployeeNo, POJobNo, and POInvoiceCost (there are other fields, but they aren't relevent here). POEmployeeNo is linked to an Employee table which includes EmployeeName. POJobNo is linked to a JobInfo table which includes JobName. During the course of operations, most of the fields in the Purchase Order record are filled in on other forms. At one point, we need to fill in just POInvoiceCost. The idea was to have a separate form, that has a combo-box field that the user selects the PONumber from. Once selected, the other fields from the Purchase Order table seem to fill in. However, I can't get the EmployeeName or JobName to fill in. So, to summarize, what we want is to select a record based on PONumber, populate the fields, including those from linked tables, start the tab stops at POInvoiceCost and enter the info for the field, save the record, then select a new record or exit. I've tried all sorts of methods. I had a working solution that involved a form where the PONumber was selected and once selected it brought up a second form with the data, but the boss wants it all on one form. I'm not set on the combo box - I'm all ears for any method that will work! Many thanks.

 

[gusbrunston]

[tt]
Hi:

Tables can be related, as one-to-one, one-to-many, many-to-many. Make sure your tables are related to accomplish what you want. Building table relationships is one of the first steps in starting your project.

Make sure your tables are properly related, and base your forms on queries. For example, you might build a query based on the following SQL statement (open a new query in design view, and enter the following statement in the SQL view):

SELECT tblPurchaseOrder.PrimaryKey, tblPurchaseOrder.EmployeeNo, tblEmployee.EmployeeName
FROM tblEmployee RIGHT JOIN tblPurchaseOrder ON tblEmployee.PrimaryKey = tblPurchaseOrder.EmployeeNo;

Of course, you'll have to use your own tables and the fields from your own tables. But keep this first one simple with just a few field names.

When you look at the query in design view, you'll see your two tables with a line between them, showing they are related on the EmployeeNo (tblPurchaseOrder) and PrimaryKey(tblEmployee).

You can use a query like this as the data source of a form, and the employee name pops into the form from tblEmployee because the tables are properly "related". That's why what we're building is called a "relational" database.

Of course, there is a lot of information available on table relationships and queries in "Help".

Hope this is of some help.[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[gusbrunston]

[tt]
If you'd like to post your email address, I'll send you a tiny database that illustrates two tables, a query and a form as I've described above.[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[elff]

Thank you for the information. I believe that all of my tables are related correctly. I can bring up the relationship "picture" and see all of my links from the key field in one record to the corresponding related field in the other. I gave a very simplistic description of the total database I've got going. The real problem I'm having is creating a form that has some sort of selection method where the user selects the record to view, the fields - including related field info (POEmployee number plus EmployeeName from Employee record - link is POEmployee to EmployeeNo)display on the form, then the user fills in a few fields, saves the record and gets to select a new one. I've tried using a combo box for the selection, but then not had luck getting the related field info to fill in. I'd be interested in seeing the database you've offered. There's always something to learn from a new approach! If you have any suggestions on the selection part, that would be great.

elff@kendra.com

 

[gusbrunston]

[tt]
Hi:

I hope this isn't a doubled message!

I think I misunderstood your first post.

For what it's worth, I sent you an email telling how you could download the demo database I wrote.

If you're having trouble with combo boxes, when I have trouble, I let the wizard do them for me.

If you're having trouble synchronizing combo boxes (the first cbo determines the second, the second the third...) this is discussed in many threads and there is an excellent article about it MS' knowledge database on Microsoft.com.

Another excellent aid for combo boxes and many other techniques is found in MS' sample database "FrmSmp00" which you can download from Microsoft.com. FrmSmp00 is for Access2000, but there are versions for other versions of Access. This provides 25-30 different techniques that are frequent problems for people. You can see the demos in action, and each has a link to the Microsoft article on how to implement the technique.

In the event the email went astray, here's the URL again (for you or anyone else who wants it). Availability will expire in 4 weeks.[/tt]

http://plus.xdrive.com/s/845609740Ax29jh4NPYaX9bMM7FX&partner=plus

[tt]
Cheers,[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]