Auto Populate

[astrom33]

I have two Tables:

1. PaymentsTbl: has four fields--TruckerID, Name, PaymentDate, Payment Amount.

2. TruckersTbl: has two fields--Name, TruckerID


I want to create a form that updates the PaymentsTbl. In other words, the form would have the same exact fields as the PaymentsTbl. The first field on the form I want to enter data into is "TruckerID". Once I enter the Trucker ID I would like the form to automatically populate the "Name" field, using the data from TruckersTbl.

Can someone please help?

 

[Galaxiom]

Don't do this. The name should be recorded in one table and the other related to it via the ID field.

Search the site for normalization.

 

[georgedwilkinson]

Agreed. You can use a lookup (for instance with a combo-box) to display the Trucker's name on your form.

 

[astrom33]

Thank you both for your answers. Unfortunately, I don't know how to do either of the things you suggested. I will do some research into both your suggestions. Thank you very much for pointing me in the right direction.

 

[georgedwilkinson]

It's pretty easy. Just use the form wizard to create a form based on the Payments table.

When the wizard is done, delete the "Name" text box, right click on the TruckerID text box and change it to a combo-box. On the data tab under properties for your new combo-box, in the row source property put:

select TruckerID, Name from TruckersTbl

change "Limit to list" to yes, click on the Format tab and change "Column count" to 2, and "Column widths" to 0;1.

You'll need to put a primary key on this table, too. If it was your intent to use the TruckerID as a primary key, you'll have problems entering more than 1 payment per trucker (not a good thing).

Your design is missing a lot of necessary stuff but you should get past this hurdle first.

 

[Galaxiom]

Best to not use a field called Name. This is a reserved word and although it can be used when surrounded by square brackets it is really best avoided.

Best to avoid spaces in object and field names. So [PaymentAmount] rather than [Payment Amount]. You don't have to use the brackets then.

To put the trucker's names in alphabetical order add the following to the end of the Row Source query:
Order By [Namefield]

TruckerID certainly cannot be the Primary Key (PK) of the Payments Table.
It will be a Foreign Key (FK) used to relate the records to the Trucker's table.

PKs are essential to refer to and relate a particular record from the table. You would probobably find it useful in the payments table so you should include it. Payment systems usually have a record for the Payment itself and then a related table to link the PaymentID to the InvoiceIDs it is posted against.

However the common belief that best practice demands all tables must have a PK is a myth.