Creating a customer record in a child table

[Avid Fan]

A big thank you again for the help I have been given so far.

I want to create a child record linking to the original customer.

I thought this would be simple

[call]![customer_ID].value = [customer]![ID].value

[customer]![ID].value gives me the ID of the first record in the table,
not the currently selected record.

What is the easy way of doing this?

 

[John W. Vinson]

A big thank you again for the help I have been given so far.

I want to create a child record linking to the original customer.

I thought this would be simple

[call]![customer_ID].value = [customer]![ID].value

[customer]![ID].value gives me the ID of the first record in the table,
not the currently selected record.

What is the easy way of doing this?

What's the context? If you have a Form based on the customer table, and a
Subform based on the calls table, and use the ID as the master/child link
field, Access takes care of all of it for you: synchronizes the calls with the
customer, inserts the ID in new records, etc. with no code at all.

 

[KARL DEWEY]

If you set one-to-many relationship between tblCustomer and tblCallInfo,
using Referential Integerity & Cascade Update, and then form/subform for
customer/call info with Master/Child link set on the related fields it will
automatically add the Customer_ID when you add a new call record.

 

[Avid Fan]

A big thank you again for the help I have been given so far.

I want to create a child record linking to the original customer.

I thought this would be simple

[call]![customer_ID].value = [customer]![ID].value

[customer]![ID].value gives me the ID of the first record in the table,
not the currently selected record.

What is the easy way of doing this?

What's the context? If you have a Form based on the customer table, and a
Subform based on the calls table, and use the ID as the master/child link
field, Access takes care of all of it for you: synchronizes the calls with the
customer, inserts the ID in new records, etc. with no code at all.in

Access2007 Win 7 creating an Access 2003 compatible database

Well I must be doing something wrong. I have set a relation between
customer.id and call.Customer_id

Customer.id is primary autonumber
call.customer_id is a number

Thanks for your help.

 

[Avid Fan]

If you set one-to-many relationship between tblCustomer and tblCallInfo,
using Referential Integerity& Cascade Update, and then form/subform for
customer/call info with Master/Child link set on the related fields it will
automatically add the Customer_ID when you add a new call record.

Does not work

Error

The Microsoft database engine cannot find a record in the table
'customer' with key matching 'customer_ID'

It is as if it searching the wrong way round.


I have checked my relationships it is 1 to many as expected


customer.id = call.customer_ID do the field names have to be the same?

 

[John W. Vinson]

Access2007 Win 7 creating an Access 2003 compatible database

Well I must be doing something wrong. I have set a relation between
customer.id and call.Customer_id

Customer.id is primary autonumber
call.customer_id is a number

Ok, but you don't say what you're DOING. Just creating a relationship won't
create new records (only prevent the addition of invalid records). Do you have
a Form with a Subform as suggested? If so, what are their recordsources?
What's the Master and Child Link Field? What's happening when you do add data
to the two forms?

 

[KARL DEWEY]

I have checked my relationships it is 1 to many as expected
Did you set the relationship in the 'Relationship' window, not just in the
query?

 

[Avid Fan]

Ok, but you don't say what you're DOING. Just creating a relationship won't
create new records (only prevent the addition of invalid records). Do you have
a Form with a Subform as suggested? If so, what are their recordsources?
What's the Master and Child Link Field? What's happening when you do add data
to the two forms?

Sorry.

I get this error when I start ether the form or the subform

"Can't set Value to Null when checkbox = FALSE"

I have a subform in a page of a tab control.
The subform shows only one record

The main Customer table is the recordsource for the main form
the callnotes table is the recordsource for the subform.

I have a save button and a new record button both created by the wizard.

On the sub form I have three controls

A combo box - Call Type
A Date picker - Active X control Microsoft date and time picker Control
6.0 SP4

A large text box linked to a Memo Field.

 

[John W. Vinson]

"Can't set Value to Null when checkbox = FALSE"

Then you have some VBA code or a macro that you have not shared with us.

I'd love to be able to help but you're making it really difficult.

 

[Avid Fan]

Then you have some VBA code or a macro that you have not shared with us.

I'd love to be able to help but you're making it really difficult.

The problem is
A Date picker - Active X control Microsoft date and time picker Control
6.0 SP4

I can stop the error by setting the checkbox = True

A checkbox appears in the combo date picker control but as far a I can
see does not belong here.

There is no default property in this active X control

I have tried setting the default value in the table to =Date() but that
does not stop the error.

 

[Avid Fan]

Did you set the relationship in the 'Relationship' window, not just in the
query?

Yes in the ribbon under "Database Tools" Relations

Set the relation

I ticked

Enforce Referential Integrity.
Cascade Update Related Fields.

I wonder what is going on?

Thanks for your help.

 

[John W. Vinson]

The problem is
A Date picker - Active X control Microsoft date and time picker Control
6.0 SP4

I can stop the error by setting the checkbox = True

A checkbox appears in the combo date picker control but as far a I can
see does not belong here.

There is no default property in this active X control

I have tried setting the default value in the table to =Date() but that
does not stop the error.

I'm sorry, but I have no experience with the DatePicker control; that appears
to be the source of the problem, not your master/child link field or the
subform itself. I'd suggest reposting with "date picker checkbox problem" in
the subject to see if someone more knowledgable can help.

 

[Avid Fan]

I'm sorry, but I have no experience with the DatePicker control; that appears
to be the source of the problem, not your master/child link field or the
subform itself. I'd suggest reposting with "date picker checkbox problem" in
the subject to see if someone more knowledgable can help.

Thank you for all your help on this project. I really do appreciate it

Your response got me thinking if the date picker was interfering. I did
remove the datepicker active X from the subform, hsowever the main
problem remains customer_ID is not being updated and I get this error


Error

The Microsoft database engine cannot find a record in the table
'customer' with key matching 'customer_ID'

 

[John W. Vinson]

Thank you for all your help on this project. I really do appreciate it

Your response got me thinking if the date picker was interfering. I did
remove the datepicker active X from the subform, hsowever the main
problem remains customer_ID is not being updated and I get this error


Error

The Microsoft database engine cannot find a record in the table
'customer' with key matching 'customer_ID'

Is there a customer_ID field in the mainform's recordsource?
Is there data in that field?
Is there a customer record visible on the form?

This is redundant, but I'm not sure what's changed and what hasn't. Please
post:

The Recordsource of the mainform (post the SQL if it's a query, the fieldnames
in the table if it's just the table)

Ditto for the subform

The Master Link Field and Child Link Field properties of the subform control

The datatype of Customer_ID in both tables