Adding new record into Subform via Main Form

[hk3dragon]

Hi, I have a question that has been bugging me. I'm new at Access but I have
done work in logical database modeling. Anyway, I cannot get my form/subform
to work. Here is the scenario:

I have two tables:
Client and Company (that the client belongs to)
Client has a many-to-one relationship to Company, and are linked by the
companyID (which is the foreign key in Client and primary key in company).

The Forms wizard only allows me to create a company FORM with client SUBFORM
as that is a 1:* relationship, which makes sense, but I want a client FORM
with company SUBFORM, because the client's data is what is important here.

Anyways, so I created my own form/subform... I placed all the data fields
from BOTH tables, and then manually linked the company SUBFORM's companyID to
the client's companyID field. In the main form (Client), the companyID is a
combo box with the list of companyID's. When I select an existing companyID,
the company subform has all the information appear.

HOWEVER, here is the problem. If I want the user to add a new company(ID)
via the main form's combo box, it doesn't allow me to, giving an error of
"you cannot add a record because a related record is required in the
'company' table." I understand that. SO, I'm trying to figure out: how do I
add a new record, when I type in a new company name in the main form's
combobox, into the subform?

If this isnt clear,please tell me.

 

[axelcore]

Hi, I have a question that has been bugging me. I'm new at Access but I have
done work in logical database modeling. Anyway, I cannot get my form/subform
to work. Here is the scenario:

I have two tables:
Client and Company (that the client belongs to)
Client has a many-to-one relationship to Company, and are linked by the
companyID (which is the foreign key in Client and primary key in company).

The Forms wizard only allows me to create a company FORM with client SUBFORM
as that is a 1:* relationship, which makes sense, but I want a client FORM
with company SUBFORM, because the client's data is what is important here.

Anyways, so I created my own form/subform... I placed all the data fields
from BOTH tables, and then manually linked the company SUBFORM's companyID to
the client's companyID field. In the main form (Client), the companyID is a
combo box with the list of companyID's. When I select an existing companyID,
the company subform has all the information appear.

HOWEVER, here is the problem. If I want the user to add a new company(ID)
via the main form's combo box, it doesn't allow me to, giving an error of
"you cannot add a record because a related record is required in the
'company' table." I understand that. SO, I'm trying to figure out: how do I
add a new record, when I type in a new company name in the main form's
combobox, into the subform?

If this isnt clear,please tell me.

I have a similar database and i have found that the best solution is to
create a button that:

1. closes the complex main form
2. opens the company form in add mode

the user then would add the appropriate info for the new company.
if you wanted to be able to go back to the main form again you could add a
save button to the company form which would then close the company form and
open the main form. Add a command to go to the record that the user just
saved and it would be complete.

 

[axelcore]

Or you can just enter:

Me.SubformName.Form.Recordset.AddNew

 

[hk3dragon]

Could you be more specific for the second option? Where do I add the code?

 

[axelcore]

Add a button to the subform and add to on click event the code below.

 

[hk3dragon]

Or you can just enter:

Me.SubformName.Form.Recordset.AddNew