Subform Question

[SeanMatyas]

I appreciate any help that is available, and I thank you in advance
for your attention...as you will be able to tell, im really new at all
of this.

Alright, I am in the process of making a database from scratch, and I
have my tables all set up how I would like them, and i am making forms
now for record entry.

I have one main table: Company Information (self explanatory, primary
key is company name. I know, i know, it should be an ID number)

I have a form where users enter information on a company, and can call
up basic information on a company. I added a button that, when
pressed, calls up a form that allows the user to enter information
regarding fee payments (we charge an annual fee, and we record the
date and amount of the fee on the aptly titled "fee" table.) The
primary key on this table is the company name as well.

Now, i thought that when i create a 1 to 1 relationship between the
tables, and select "enforce referential integrity", and "cascade
update related fields", that when i create a "company" record on my
main form and select my subform button, it would update the "company"
record on my fee table. However, it does not, and when i put in the
fee information i get the error message "index or primary key cannot
contain a null value".

This leads me to my question: is there a way that I can get this to
work as I envision it working? Is it possible to update the "company"
record on my fees table when I add my "company" record on my Company
Information table?

Any help is greatly appreciated....

Thank you
Sean

 

[Carl Rapson]

I appreciate any help that is available, and I thank you in advance
for your attention...as you will be able to tell, im really new at all
of this.

Alright, I am in the process of making a database from scratch, and I
have my tables all set up how I would like them, and i am making forms
now for record entry.

I have one main table: Company Information (self explanatory, primary
key is company name. I know, i know, it should be an ID number)

I have a form where users enter information on a company, and can call
up basic information on a company. I added a button that, when
pressed, calls up a form that allows the user to enter information
regarding fee payments (we charge an annual fee, and we record the
date and amount of the fee on the aptly titled "fee" table.) The
primary key on this table is the company name as well.

Now, i thought that when i create a 1 to 1 relationship between the
tables, and select "enforce referential integrity", and "cascade
update related fields", that when i create a "company" record on my
main form and select my subform button, it would update the "company"
record on my fee table. However, it does not, and when i put in the
fee information i get the error message "index or primary key cannot
contain a null value".

This leads me to my question: is there a way that I can get this to
work as I envision it working? Is it possible to update the "company"
record on my fees table when I add my "company" record on my Company
Information table?

Any help is greatly appreciated....

Thank you
Sean

If your command button is opening another form, that form has no way of
knowing the primary key of the record your main form is showing. What you'll
need to do is pass that company name to the second form, in the OpenArgs
parameter of the OpenForm call. Then, in the Current (or maybe BeforeInsert)
event of the second form, pick up the name from the OpenArgs variable and
use it to populate the company name field in your fee table.

If your subform were embedded right into your main form, you wouldn't have
this problem because you can specify the link between the parent and
subform.

Carl Rapson

 

[SeanMatyas]

If your command button is opening another form, that form has no way of
knowing the primary key of the record your main form is showing. What you'll
need to do is pass that company name to the second form, in the OpenArgs
parameter of the OpenForm call. Then, in the Current (or maybe BeforeInsert)
event of the second form, pick up the name from the OpenArgs variable and
use it to populate the company name field in your fee table.

If your subform were embedded right into your main form, you wouldn't have
this problem because you can specify the link between the parent and
subform.

Carl Rapson- Hide quoted text -

- Show quoted text -

Carl,

Thank you for your help... however, I am slightly confused on how to
go about doing what you recommended. I completely understand the
concept you are trying to convey, bu not the implementation. Any
additional assistance you could provide would be greatly appreciated.

Sean

 

[Carl Rapson]

Carl,

Thank you for your help... however, I am slightly confused on how to
go about doing what you recommended. I completely understand the
concept you are trying to convey, bu not the implementation. Any
additional assistance you could provide would be greatly appreciated.

Sean

It's hard to give very many implementation details because I can't see your
database. But if your main form has a text box called, let's say,
txtCompanyName, that is bound to the CompanyName field in your table, you
can pass this value to the other form in the OpenForm call:

DoCmd.OpenForm "other form",,,,,Me.txtCompanyName

In the other form's BeforeInsert event, add this value to the fee table
CompanyName field:

Me.CompanyName = Me.OpenArgs

Along the way somewhere (probably the fee form's Open event), you'll
probably want to check OpenArgs to make sure it isn't blank, or you'll still
have the same problem with a null value.

The other alternative is to embed the fee form onto your main form as a
subform. To do this, simply drag and drop the fee form onto the main form.
When prompted, set up the link between the parent (main) form and sub (fee)
form to be the CompanyName. Now, whenever you select a company in your main
form, all records from the fee table will automatically display. You can add
new fee records to the fee subform and the CompanyName will automatically be
filled in for you.

HTH,

Carl Rapson

 

[SeanMatyas]

It's hard to give very many implementation details because I can't see your
database. But if your main form has a text box called, let's say,
txtCompanyName, that is bound to the CompanyName field in your table, you
can pass this value to the other form in the OpenForm call:

DoCmd.OpenForm "other form",,,,,Me.txtCompanyName

In the other form's BeforeInsert event, add this value to the fee table
CompanyName field:

Me.CompanyName = Me.OpenArgs

Along the way somewhere (probably the fee form's Open event), you'll
probably want to check OpenArgs to make sure it isn't blank, or you'll still
have the same problem with a null value.

The other alternative is to embed the fee form onto your main form as a
subform. To do this, simply drag and drop the fee form onto the main form.
When prompted, set up the link between the parent (main) form and sub (fee)
form to be the CompanyName. Now, whenever you select a company in your main
form, all records from the fee table will automatically display. You can add
new fee records to the fee subform and the CompanyName will automatically be
filled in for you.

HTH,

Carl Rapson- Hide quoted text -

- Show quoted text -

Carl,

Thank you very much for your help... I managed to get it running and
it works like a charm.

Again, Thank you!
Sean