subform

[Eric]

I have three tables in my database. One is main, rest of them i want to
use as a sub form in tabular format in main form. I have no experience
in creating subforms. Can any one please help me step by step how to i
create subform in my main form.

Table Customer (Primary)
Table Invoice (Child)
Table Voucher (Child)

2nd do i create links between all of these table when i create tables
or in the form level.

Thanks in advance.

 

[Guest]

Hi Eric,

Access has wizards and functionality to help you do all this. The major
steps are:

1. Relate your tables.
2. Create your tabular subforms
3. Create your main form and incorporate your subforms.

To relate your tables you must include the primary key of the Customer table
in the design of the Invoice and Voucher tables as a foreign key. How have
you keyed the Customer table? Have you used autonumber as the primary key?
If you have then simply include a column in your child tables of type Long
Integer and call it something appropriate such as CustId. I suggest that you
specify the default value as null.

This next bit is optional but highly recommended as Access will 'protect'
and manage your relationships. Go to Tools/Relationships from the main menu
bar. Select and add all three tables to the graphic display if they are not
already shown. Drag 'n drop the primary key from the Customer table to the
foreign key on the Invoice table. A dialog box will appear allowing you to
specify the properties of the relationship. Click on [Join Type]; I suggest
you choose to 'include all records from Customer and only those from Invoice
that match' (or whatever the exact wording is). This implies you can have
Customer records without Invoice records. Return to the dialog and select
'Cascade Delete' if you wish. This means that if you delete a Customer
Access will automatically delete all linked Invoices. Now click [Create].
Repeat the process for the Customer-Voucher relationship.

Now for step 2: Create two tabular (continuous) forms, one for Invoice and
one for Voucher. If you're really new to all this use the form creation
wizards to guide you through the process.

Finally create your Customer form. When you are ready to add the subforms
make sure the wizard option in the toolbox is on. Select the subform control
from the toolbox and place it on your Customer form. A dialog will ask you
the source for the subform. Select your tabular Invoice form. The dialog
will now ask how the forms are to be linked and will suggest the link based
on the relationship or upon the column names. If it's wrong then alter the
link but I believe you will find the wizard's suggestion to be correct.
Repeat for the Voucher sub form and you're finished except for the seemingly
endless task of sizing and re-arranging the forms and controls.

Access will add the foreign key (CustId) to all new records in the subforms
so it is better to disable or hide this value in the subforms.

Regards,

Rod

 

[Eric]

I didnt find this option 'Cascade Delete' during creating relationship.
Can you please tell me where this option is?

Return to the dialog and select 'Cascade Delete' if you wish. This
means that if you delete a Customer Access will automatically delete
all linked Invoices. Now click [Create]. Repeat the process for the
Customer-Voucher relationship.

Hi Eric,

Access has wizards and functionality to help you do all this. The major
steps are:

1. Relate your tables.
2. Create your tabular subforms
3. Create your main form and incorporate your subforms.

To relate your tables you must include the primary key of the Customer table
in the design of the Invoice and Voucher tables as a foreign key. How have
you keyed the Customer table? Have you used autonumber as the primary key?
If you have then simply include a column in your child tables of type Long
Integer and call it something appropriate such as CustId. I suggest that you
specify the default value as null.

This next bit is optional but highly recommended as Access will 'protect'
and manage your relationships. Go to Tools/Relationships from the main menu
bar. Select and add all three tables to the graphic display if they are not
already shown. Drag 'n drop the primary key from the Customer table to the
foreign key on the Invoice table. A dialog box will appear allowing you to
specify the properties of the relationship. Click on [Join Type]; I suggest
you choose to 'include all records from Customer and only those from Invoice
that match' (or whatever the exact wording is). This implies you can have
Customer records without Invoice records. Return to the dialog and select
'Cascade Delete' if you wish. This means that if you delete a Customer
Access will automatically delete all linked Invoices. Now click [Create].
Repeat the process for the Customer-Voucher relationship.

Now for step 2: Create two tabular (continuous) forms, one for Invoice and
one for Voucher. If you're really new to all this use the form creation
wizards to guide you through the process.

Finally create your Customer form. When you are ready to add the subforms
make sure the wizard option in the toolbox is on. Select the subform control
from the toolbox and place it on your Customer form. A dialog will ask you
the source for the subform. Select your tabular Invoice form. The dialog
will now ask how the forms are to be linked and will suggest the link based
on the relationship or upon the column names. If it's wrong then alter the
link but I believe you will find the wizard's suggestion to be correct.
Repeat for the Voucher sub form and you're finished except for the seemingly
endless task of sizing and re-arranging the forms and controls.

Access will add the foreign key (CustId) to all new records in the subforms
so it is better to disable or hide this value in the subforms.

Regards,

Rod

I have three tables in my database. One is main, rest of them i want to
use as a sub form in tabular format in main form. I have no experience
in creating subforms. Can any one please help me step by step how to i
create subform in my main form.

Table Customer (Primary)
Table Invoice (Child)
Table Voucher (Child)

2nd do i create links between all of these table when i create tables
or in the form level.

Thanks in advance.

 

[Eric]

I create subform of invoice in customer form when i run the form it
gives that error what does it means
Index or primary key can not contain a null value

Second in invoice form there is a variable name item which should be
linked with the item table I need help how to create link in invoice
table with item table and then how to i create a drop down in invoice
form of items

Thanks in advance.

Hi Eric,

Access has wizards and functionality to help you do all this. The major
steps are:

1. Relate your tables.
2. Create your tabular subforms
3. Create your main form and incorporate your subforms.

To relate your tables you must include the primary key of the Customer table
in the design of the Invoice and Voucher tables as a foreign key. How have
you keyed the Customer table? Have you used autonumber as the primary key?
If you have then simply include a column in your child tables of type Long
Integer and call it something appropriate such as CustId. I suggest that you
specify the default value as null.

This next bit is optional but highly recommended as Access will 'protect'
and manage your relationships. Go to Tools/Relationships from the main menu
bar. Select and add all three tables to the graphic display if they are not
already shown. Drag 'n drop the primary key from the Customer table to the
foreign key on the Invoice table. A dialog box will appear allowing you to
specify the properties of the relationship. Click on [Join Type]; I suggest
you choose to 'include all records from Customer and only those from Invoice
that match' (or whatever the exact wording is). This implies you can have
Customer records without Invoice records. Return to the dialog and select
'Cascade Delete' if you wish. This means that if you delete a Customer
Access will automatically delete all linked Invoices. Now click [Create].
Repeat the process for the Customer-Voucher relationship.

Now for step 2: Create two tabular (continuous) forms, one for Invoice and
one for Voucher. If you're really new to all this use the form creation
wizards to guide you through the process.

Finally create your Customer form. When you are ready to add the subforms
make sure the wizard option in the toolbox is on. Select the subform control
from the toolbox and place it on your Customer form. A dialog will ask you
the source for the subform. Select your tabular Invoice form. The dialog
will now ask how the forms are to be linked and will suggest the link based
on the relationship or upon the column names. If it's wrong then alter the
link but I believe you will find the wizard's suggestion to be correct.
Repeat for the Voucher sub form and you're finished except for the seemingly
endless task of sizing and re-arranging the forms and controls.

Access will add the foreign key (CustId) to all new records in the subforms
so it is better to disable or hide this value in the subforms.

Regards,

Rod

I have three tables in my database. One is main, rest of them i want to
use as a sub form in tabular format in main form. I have no experience
in creating subforms. Can any one please help me step by step how to i
create subform in my main form.

Table Customer (Primary)
Table Invoice (Child)
Table Voucher (Child)

2nd do i create links between all of these table when i create tables
or in the form level.

Thanks in advance.

 

[Guest]

Eric,

It's the bottom check box on the left of the dialog box. I also should have
advised you to check the uppermost box, the one about enforcing relationships.

Regards,

Rod

I didnt find this option 'Cascade Delete' during creating relationship.
Can you please tell me where this option is?

Return to the dialog and select 'Cascade Delete' if you wish. This
means that if you delete a Customer Access will automatically delete
all linked Invoices. Now click [Create]. Repeat the process for the
Customer-Voucher relationship.

Hi Eric,

Access has wizards and functionality to help you do all this. The major
steps are:

1. Relate your tables.
2. Create your tabular subforms
3. Create your main form and incorporate your subforms.

To relate your tables you must include the primary key of the Customer table
in the design of the Invoice and Voucher tables as a foreign key. How have
you keyed the Customer table? Have you used autonumber as the primary key?
If you have then simply include a column in your child tables of type Long
Integer and call it something appropriate such as CustId. I suggest that you
specify the default value as null.

This next bit is optional but highly recommended as Access will 'protect'
and manage your relationships. Go to Tools/Relationships from the main menu
bar. Select and add all three tables to the graphic display if they are not
already shown. Drag 'n drop the primary key from the Customer table to the
foreign key on the Invoice table. A dialog box will appear allowing you to
specify the properties of the relationship. Click on [Join Type]; I suggest
you choose to 'include all records from Customer and only those from Invoice
that match' (or whatever the exact wording is). This implies you can have
Customer records without Invoice records. Return to the dialog and select
'Cascade Delete' if you wish. This means that if you delete a Customer
Access will automatically delete all linked Invoices. Now click [Create].
Repeat the process for the Customer-Voucher relationship.

Now for step 2: Create two tabular (continuous) forms, one for Invoice and
one for Voucher. If you're really new to all this use the form creation
wizards to guide you through the process.

Finally create your Customer form. When you are ready to add the subforms
make sure the wizard option in the toolbox is on. Select the subform control
from the toolbox and place it on your Customer form. A dialog will ask you
the source for the subform. Select your tabular Invoice form. The dialog
will now ask how the forms are to be linked and will suggest the link based
on the relationship or upon the column names. If it's wrong then alter the
link but I believe you will find the wizard's suggestion to be correct.
Repeat for the Voucher sub form and you're finished except for the seemingly
endless task of sizing and re-arranging the forms and controls.

Access will add the foreign key (CustId) to all new records in the subforms
so it is better to disable or hide this value in the subforms.

Regards,

Rod

I have three tables in my database. One is main, rest of them i want to
use as a sub form in tabular format in main form. I have no experience
in creating subforms. Can any one please help me step by step how to i
create subform in my main form.

Table Customer (Primary)
Table Invoice (Child)
Table Voucher (Child)

2nd do i create links between all of these table when i create tables
or in the form level.

Thanks in advance.

 

[Guest]

Send me a copy of your mdb to (e-mail address removed) and I'll debug it for you

Rod

I create subform of invoice in customer form when i run the form it
gives that error what does it means
Index or primary key can not contain a null value

Second in invoice form there is a variable name item which should be
linked with the item table I need help how to create link in invoice
table with item table and then how to i create a drop down in invoice
form of items

Thanks in advance.

Hi Eric,

Access has wizards and functionality to help you do all this. The major
steps are:

1. Relate your tables.
2. Create your tabular subforms
3. Create your main form and incorporate your subforms.

To relate your tables you must include the primary key of the Customer table
in the design of the Invoice and Voucher tables as a foreign key. How have
you keyed the Customer table? Have you used autonumber as the primary key?
If you have then simply include a column in your child tables of type Long
Integer and call it something appropriate such as CustId. I suggest that you
specify the default value as null.

This next bit is optional but highly recommended as Access will 'protect'
and manage your relationships. Go to Tools/Relationships from the main menu
bar. Select and add all three tables to the graphic display if they are not
already shown. Drag 'n drop the primary key from the Customer table to the
foreign key on the Invoice table. A dialog box will appear allowing you to
specify the properties of the relationship. Click on [Join Type]; I suggest
you choose to 'include all records from Customer and only those from Invoice
that match' (or whatever the exact wording is). This implies you can have
Customer records without Invoice records. Return to the dialog and select
'Cascade Delete' if you wish. This means that if you delete a Customer
Access will automatically delete all linked Invoices. Now click [Create].
Repeat the process for the Customer-Voucher relationship.

Now for step 2: Create two tabular (continuous) forms, one for Invoice and
one for Voucher. If you're really new to all this use the form creation
wizards to guide you through the process.

Finally create your Customer form. When you are ready to add the subforms
make sure the wizard option in the toolbox is on. Select the subform control
from the toolbox and place it on your Customer form. A dialog will ask you
the source for the subform. Select your tabular Invoice form. The dialog
will now ask how the forms are to be linked and will suggest the link based
on the relationship or upon the column names. If it's wrong then alter the
link but I believe you will find the wizard's suggestion to be correct.
Repeat for the Voucher sub form and you're finished except for the seemingly
endless task of sizing and re-arranging the forms and controls.

Access will add the foreign key (CustId) to all new records in the subforms
so it is better to disable or hide this value in the subforms.

Regards,

Rod

I have three tables in my database. One is main, rest of them i want to
use as a sub form in tabular format in main form. I have no experience
in creating subforms. Can any one please help me step by step how to i
create subform in my main form.

Table Customer (Primary)
Table Invoice (Child)
Table Voucher (Child)

2nd do i create links between all of these table when i create tables
or in the form level.

Thanks in advance.

 

[Eric]

On the left side of the dialogue box it said Enforce Referential
Integrity. I click on that and then the rest of the options wer enabled
and i click on Cascade Delete Related Records too. But when i click on
Ok it said:

Relationship must be the same number of fields with the same data types
I also send form at your email address. Thanks for that.

Send me a copy of your mdb to (e-mail address removed) and I'll debug it for you

Rod

I create subform of invoice in customer form when i run the form it
gives that error what does it means
Index or primary key can not contain a null value

Second in invoice form there is a variable name item which should be
linked with the item table I need help how to create link in invoice
table with item table and then how to i create a drop down in invoice
form of items

Thanks in advance.

Hi Eric,

Access has wizards and functionality to help you do all this. The major
steps are:

1. Relate your tables.
2. Create your tabular subforms
3. Create your main form and incorporate your subforms.

To relate your tables you must include the primary key of the Customer table
in the design of the Invoice and Voucher tables as a foreign key. How have
you keyed the Customer table? Have you used autonumber as the primary key?
If you have then simply include a column in your child tables of type Long
Integer and call it something appropriate such as CustId. I suggest that you
specify the default value as null.

This next bit is optional but highly recommended as Access will 'protect'
and manage your relationships. Go to Tools/Relationships from the main menu
bar. Select and add all three tables to the graphic display if they are not
already shown. Drag 'n drop the primary key from the Customer table to the
foreign key on the Invoice table. A dialog box will appear allowing you to
specify the properties of the relationship. Click on [Join Type]; I suggest
you choose to 'include all records from Customer and only those from Invoice
that match' (or whatever the exact wording is). This implies you can have
Customer records without Invoice records. Return to the dialog and select
'Cascade Delete' if you wish. This means that if you delete a Customer
Access will automatically delete all linked Invoices. Now click [Create].
Repeat the process for the Customer-Voucher relationship.

Now for step 2: Create two tabular (continuous) forms, one for Invoice and
one for Voucher. If you're really new to all this use the form creation
wizards to guide you through the process.

Finally create your Customer form. When you are ready to add the subforms
make sure the wizard option in the toolbox is on. Select the subform control
from the toolbox and place it on your Customer form. A dialog will ask you
the source for the subform. Select your tabular Invoice form. The dialog
will now ask how the forms are to be linked and will suggest the link based
on the relationship or upon the column names. If it's wrong then alter the
link but I believe you will find the wizard's suggestion to be correct.
Repeat for the Voucher sub form and you're finished except for the seemingly
endless task of sizing and re-arranging the forms and controls.

Access will add the foreign key (CustId) to all new records in the subforms
so it is better to disable or hide this value in the subforms.

Regards,

Rod

:

I have three tables in my database. One is main, rest of them i want to
use as a sub form in tabular format in main form. I have no experience
in creating subforms. Can any one please help me step by step how to i
create subform in my main form.

Table Customer (Primary)
Table Invoice (Child)
Table Voucher (Child)

2nd do i create links between all of these table when i create tables
or in the form level.

Thanks in advance.