Pre-populating foreign key when creating a new record

[accessdude]

Hello,

I'm trying to pre-populate a foreign key field [client_ID] when creating a record; whether creating a new record in the table itself or a form.

table 1 = Clients
table 2 = Invoices

table Clients has the [Client_ID] field and all client data. The Invoice table contains many invoice records for the respective client record.

I've created a one to many relationship between the two tables, based on [client_ID]. So there is a foreign key field called [client_ID] in the Invoice table.

My problem is, when creating a new record in the Invoice table or a form using the Invoice table data, I cannot seem to find a way to pre-populate the foreign key field.
How would I do this? I have tried adding another field from the Client table, to the Invoice table called [Client_Nickname]. I can select a value in the dropdown field under the Invoice table, for [Client_Nickname] but it does not prepopulate the field in the Invoice table with the [Client_ID] value.

My goal is to have a Invoice form where I can create a new Invoice record, select the Client's name from a dropdown (or nickname) and have that client's ID field on the same form, pre-populate. As it is now, I have to select the [Client_Nickname] from a dropdown field then also select a value for the [Client_ID] dropdown field. Then save the record.
I'd rather just select client nickname and the client ID prepopulate if that makes any sense...

thank you.

[zmbd]

One way, and the way I tend to this,
Parent form to the parent table or query.
Child form to the details.

This way when you make your entry into the subform, the value fot the related field is entered into the child's record.

[GKJR]

There is an easy way to do this on a data entry form that doesn't require a subform at all.

1. Create a combo box or list box (both are essentially the same thing) that is bound to the Client_ID in the Invoice table (Foreign Key).
2. Define the Row Source property to contain the Client_ID and the Client_Name (2 columns) with an SQL statement.
3. Set the Bound Column on the Data tab to 1.
4. Set the Number of Columns on the Format tab to 2.
5. The Column Width property should look something like: 0"; 2" (I usually hide the ID field by setting the width to 0).

This is a critical method for making forms. One of the parts that threw me off when I was learning how to do this was really understanding the difference between the Control Source and the Row Source of your combo or list box. The Control Source determines what field in the underlying record source will take the value that it returns. The Row Source determines what options are available for the user to choose from based on an SQL statement.

[accessdude]

Thank you both, this is great information and the creating of the combo box and defining the row source property to contain both the Client_ID and the Client_Name solved my problem.
The new form works like a charm and allows me to create new records without entering a foreign key separately.