populate text boxes in a subform with multicolumn Combo Box

[Bradders]

Hello

This is my 1st post as a new user to the forum - so hello everybody. Please forgive me if i have posted in the wrong area.

my problem is that i am creating a database to be used at work which will allow a user to create a workshop job and add job lines to the job. Each job can have multiple job lines.

My database has the following tables / fields:

Jobs:


Job_Number AutoNumber
Fleet_Number Number
Depot Text
Booked_Date Date/Time

Job_Lines:
ID AutoNumber
Job_Description Text
Time_Allowed Date/Time
Repair_Code Text

Job_Job_Lines:
ID AutoNumber
JJL_Job_Number Number
JJL_Job_Desc Text
JJL_Job_Time Date/Time

I have created a form with a subform from tables: Jobs / Job_Job_Lines.

On the main form I have added a combo box (cboJob_Line_Select that selects the Job_Description and Time_Allowed from the Job_Lines Table) that will allow a user to select a Job_Description from the Job_Lines Table and I wanted the subform (Job_Job_Lines) to auto populate with the Job_Description and the Time_Allowed.

I used an After Update event procedure:

Private Sub cboJob_Line_Select_AfterUpdate()
Me.JJL_Job_Desc = Me![cboJob_Line_Select].Column(1)
Me.JJL_Job_Time = Me![cboJob_Line_Select].Column(2)
End Sub

However when I select a Job_Description using the combo box I get the following error:

Compile Error:

Method or Data Member not Found.

I tried a demo of auto populating main forms using a combo box here: http://support.microsoft.com/kb/319482 and this worked ok.

All the help received will be very much appreciated.

Thanks


Craig

[rpeare]

How are you relating the tables

Jobs and Job_Lines?

There's no link between them in your example is that an oversight in your post? There should be a field in the Job_Lines table that's carrying the parent ID (Job_Number) from your Jobs table.

Secondly you don't want to populate your second subform with the Job_Description and Time_Allowed fields, especially if it's your intent to have these fields re-occur on every line of your Job_Job_Lines form. This information is already being stored in your Job_Lines table and there is no reason to duplicate it on any subsidiary tables. If you correctly link the Job_Lines to Job_Job_Lines subform through the (ID from Job_Lines to JJL_Job_Number from Job_Job_Lines I'm assuming) you can look up this information rather than duplicating it.

If you make the ID field from the JOB_LINES table your primary key, then in your JOB_JOB_LINES subform instead of basing it on a table, base it on a query, in that query link your table JOB_JOB_LINES to your JOB_LINES table and add the two fields you're interested in. When you then enter a new record the two fields you added should automatically populate you just have to make sure you set the LOCKED property of those two new fields to YES or TRUE so that nobody changes them by accident.

If you do not follow this or have trouble implementing it or if I've misunderstood your problem please post a sample of your database with garbage data in it

[Bradders]

[Bradders]

Sorry for the delay but been on holiday. I actually used a query as you suggested and an on event expression to sort this. Many thanks. Craigs