Tables/Relationships/Look-ups....Please help

[jarhead1]

Please help. I am a rooky Access 2000 user trying to solve the following.

I have three tables that I am trying to get to work together on a form that I call the Project Entry form The Project entry form includes Client information and order items. The following tables are what I am trying to set up.

FORM = Project Entry

TBL 1: JobNo/Info

JobNo
Client
ClientContact

Client field for Tbl 1 is a look-up from my client list table. Tied to the client table is a contact table. On my project entry form I would like the CLIENTCONTACT field to list only the contacts related to that client. Right now it is populated with all of my contact names.

Subform for Project Entry

Tbl 2: Order Items

Currently I have tried to set up a subform with the following info. Based on the client I would like my table 2 ITEMNO’s to look up data from ITEMNO’s in table 3. Once Itemno is picked I would like the desc field to fill in automatically.

JobNo ‘currently picks up JobNo entered in table 1
Client ‘ I want this field to read table one entry and set itemno to read from related itemno’s in table 3
ItemNo
Desc ‘ fill in automatically based on Itemno entry


Related table with client item numbers and descriptions

Tbl 3: ClientItemNumbers

Client
ItemNo
Desc
EstTime

I have spent many hours trying to get this to work. With some help I know the light will come on. Thank you……….

 

[KenReay]

HI

FORM = Project Entry

TBL 1: JobNo/Info

JobNo
Client
ClientContact

Client field for Tbl 1 is a look-up from my client list table. Tied to the client table is a contact table. On my project entry form I would like the CLIENTCONTACT field to list only the contacts related to that client. Right now it is populated with all of my contact names.
--------------------------------------------------

OK, so make the record source of the combo box, a query, which has for criteria the Customer ID diosplayed on the form, the building will help you do this.

Just for good measure you need to requery the contact combo box, in the after update event of the Customer Combo box, and in the Got Focus event of the Customer Contact Combo, I would put code like so

If IsNull(cboClient) Then
MsgBox "Please Specify Client First"
cboClient.SetFocus
Exit Sub
End If

------------------------------------------------


Subform for Project Entry

Tbl 2: Order Items

Currently I have tried to set up a subform with the following info. Based on the client I would like my table 2 ITEMNO’s to look up data from ITEMNO’s in table 3. Once Itemno is picked I would like the desc field to fill in automatically.

JobNo ‘currently picks up JobNo entered in table 1
Client ‘ I want this field to read table one entry and set itemno to read from related itemno’s in table 3
ItemNo
Desc ‘ fill in automatically based on Itemno entry


Related table with client item numbers and descriptions

Tbl 3: ClientItemNumbers

Client
ItemNo
Desc
EstTime

------------------------------------------------------------

First have you set up the Master/Child link in the subform control?, from what you say this should be on JobNo

Regards

Ken Reay