AutoLookup Query subform

[Guest]

I've created an AutoLookup query i.e. joining Customer and Order
tables...using the CustomerID from order table and auto fields from Customer
table. I'm using the Autolookup query as a subform in my Order form. Here's
my problem...when i click in the "customerID' field in my subform I get an
error message " INDEX OR PRIMARY KEY CAN NOT CONTAIN A NULL VALUE". The
customerID field in the Order table is not a primary key. what am I doing
wrong??? I would like to have the subform auto generate the customer info.
Is it possible that my "CustomerID" field in the customer table is a "primary
key" with AutoNumber configuration that might be causing the probem? What
needs to be done?

 

[Jeff Boyce]

If you are using a main-form/subform construction, and if you have drawn the
relationship (one-to-many) between Customer and Order, and if your
main-form's subform control properly reflects the Parent & Child related
fields, then you could get that message if you were to have NO record in the
main-form (i.e. the Customer), but attempt to add something into the subform
(i.e., the Order).

Access seems to be telling you that it can create a "child" record when it
doesn't know who the "parent" is...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

My bad -- Access is telling you that it CANNOT create a child record without
a parent.

Jeff

 

[Guest]

thanks Jeff...

the relationship have been established via Tables and AutoLookup Query...one
to many, parent to child and so on...

here's my deal. the "Customer ID" field in the Table:Customer is a primary
key with "AutoNumber" configuration. Whereas, the "Customer ID" field in
Table: Order is a non-primary key with "number' configuration.

An Autolook-upQuery was created joining the two tables. one (table:
customer) to many (table: order). extracting Customer ID from Table:Order
and the lookup fields from Table:Customer.

Alright, here is my problem...I created a main form with Table:Order fields
and subform with Autolookup query flieds. When I try entring data into the
subform is giving me the error message as stated below. How can I modify my
joins or FORM to make it work??????

 

[Jeff Boyce]

Please re-read my earlier response.

Open your main form in design mode. Select the subform control. Check the
subform control's properties. Do you have your [CustomerID] field showing
as both the "Parent" and the "Child" fields?

Next, how do you select a particular "parent" (i.e., Customer) in the main
form? Are you selecting a Customer in your main form BEFORE you try to set
up an order in your subform? If you do NOT select a customer in the main
form, the subform has NO [CustomerID] to refer to.

Regards

Jeff Boyce
Microsoft Office/Access MVP