Passing Master Autonumber ID to SubForm New Record

[Swinky]

I have a form "AccountInf o" that contains company names. I have
inserted a subform "Contacts" with contact names and have established
parent/child relationships between the two forms. All works well.

I created an "Add New Contact" command button which passes the
CompanyID (unique key set to autonumber) to the new record. This
successfully works if the parent record (AccountInfo) has at least one
existing contact. But if there are no contacts associated with the
AccountInfo record, I get the error "Applicatio n-defined or
Object-defined error" when I click the Add New Contact command button.
This happens because the Contact form CompanyID doesn't have a number
yet---the number is "autonumber ".
This is the code I am using:

DoCmd.OpenForm "AddContact ", DataMode:=acFor mAdd
Forms("AddConta ct").CmpnyID = Me.CompanyID

How do I pass the parent form CompanyID to the 'Add New' child form?

[pietlinden@hotmail.com]

Re: Passing Master Autonumber ID to SubForm New Record

Sounds like you never created a relationship between the two tables.
Try that and then see if that doesn't fix the problem.

[Swinky]

Re: Passing Master Autonumber ID to SubForm New Record

pietlinden@hotm ail.com wrote:

Sounds like you never created a relationship between the two tables.
Try that and then see if that doesn't fix the problem.

Relationships already exist with referential integrity.

I can't put my finger on the problem, but somehow I need to pass the
CompanyID from the parent form to the sub-form CompanyID (which is the
field that relates the two tables).

As mentioned, when there is an existing (child) record that matches the
parent company, the CompanyID is successfully passed to the Add Record
function. It is pulling the CompanyID from the already existing
(child) record. The problem occurs only when there is NO existing
(child) record. If no child record exists, there is no CompanyID to
pass to the Add Record function.

I think what I need to do is make the Add Record (for the child form)
look to the parent record for the CompanyID rather than trying to find
it from the child record. But I don't know how to do that.

[pietlinden@hotmail.com]

Re: Passing Master Autonumber ID to SubForm New Record


Swinky wrote:

pietlinden@hotm ail.com wrote:

Sounds like you never created a relationship between the two tables.
Try that and then see if that doesn't fix the problem.

>
Relationships already exist with referential integrity.
>
I can't put my finger on the problem, but somehow I need to pass the
CompanyID from the parent form to the sub-form CompanyID (which is the
field that relates the two tables).
>
As mentioned, when there is an existing (child) record that matches the
parent company, the CompanyID is successfully passed to the Add Record
function. It is pulling the CompanyID from the already existing
(child) record. The problem occurs only when there is NO existing
(child) record. If no child record exists, there is no CompanyID to
pass to the Add Record function.
>
I think what I need to do is make the Add Record (for the child form)
look to the parent record for the CompanyID rather than trying to find
it from the child record. But I don't know how to do that.

Do you have the LinkChild and LinkMaster fields in the subform set? If
you open the Main form, and then click on the subform inside it, (in
design view), you should see the LinkChild and LinkMaster properties on
the Data tab. Are those filled in?

[Swinky]

Re: Passing Master Autonumber ID to SubForm New Record



pietlinden@hotm ail.com wrote:

Swinky wrote:

pietlinden@hotm ail.com wrote:

Sounds like you never created a relationship between the two tables.
Try that and then see if that doesn't fix the problem.

Relationships already exist with referential integrity.

I can't put my finger on the problem, but somehow I need to pass the
CompanyID from the parent form to the sub-form CompanyID (which is the
field that relates the two tables).

As mentioned, when there is an existing (child) record that matches the
parent company, the CompanyID is successfully passed to the Add Record
function. It is pulling the CompanyID from the already existing
(child) record. The problem occurs only when there is NO existing
(child) record. If no child record exists, there is no CompanyID to
pass to the Add Record function.

I think what I need to do is make the Add Record (for the child form)
look to the parent record for the CompanyID rather than trying to find
it from the child record. But I don't know how to do that.

>
Do you have the LinkChild and LinkMaster fields in the subform set? If
you open the Main form, and then click on the subform inside it, (in
design view), you should see the LinkChild and LinkMaster properties on
the Data tab. Are those filled in?

Yes, the properties has both the Link Parent & Link Child fields filled.

[Swinky]

Re: Passing Master Autonumber ID to SubForm New Record


Swinky wrote:

pietlinden@hotm ail.com wrote:

Swinky wrote:

pietlinden@hotm ail.com wrote:
Sounds like you never created a relationship between the two tables.
Try that and then see if that doesn't fix the problem.
>
Relationships already exist with referential integrity.
>
I can't put my finger on the problem, but somehow I need to pass the
CompanyID from the parent form to the sub-form CompanyID (which is the
field that relates the two tables).
>
As mentioned, when there is an existing (child) record that matches the
parent company, the CompanyID is successfully passed to the Add Record
function. It is pulling the CompanyID from the already existing
(child) record. The problem occurs only when there is NO existing
(child) record. If no child record exists, there is no CompanyID to
pass to the Add Record function.
>
I think what I need to do is make the Add Record (for the child form)
look to the parent record for the CompanyID rather than trying to find
it from the child record. But I don't know how to do that.

Do you have the LinkChild and LinkMaster fields in the subform set? If
you open the Main form, and then click on the subform inside it, (in
design view), you should see the LinkChild and LinkMaster properties on
the Data tab. Are those filled in?

>
Yes, the properties has both the Link Parent & Link Child fields filled.

UPDATE: I think I found the answer to my question. FYI, my code said:

DoCmd.OpenForm "AddContact ", DataMode:=acFor mAdd
Forms("AddConta ct").CmpnyID = Me.CompanyID

By changing the 2nd line to read "Forms("AddCont act").CmpnyID =
Parent!CompanyI D", the ID number is now pulled from the parent record
and inserted into the new child record being created.

Now I can move on to my next challenge :-) .....