Help with Subforms/Combo Boxes

[mikel]

Hi,



First off, I am new to Access so I'm learning as I go. I've been trying to create a simple gage calibration program but have run into a problem. I have some tables setup and linked and now am trying to create some forms to enter data and I can't figure out how to get it to work.

I have a main form called frmGage (it's linked to a table called Gage) which gives details about the gage (manufacturer, owner, type, size, status, and so forth). On this form I would like to add a button called "Calibrate Gage" which would open a form that I have created called "frmCalibration" (this form is linked to a table called Calibration). But when this form opens I would like to have the general information about the gage (gage S/N, manufacturer, gage type, size, etc) on the top of the form for the gage that was being viewed on the main form. This information should not be editable. On the bottom is where I would like to be able to enter information about the calibration such as calibration date, done by, type of calibration, results, status, etc.

Since there will be multiple calibrations for each gage I have the relationship setup as one to many with the gage on the one side and the calibrations on the many side. I have primary key of Gage_ID in both tables and this part of it seems to be working fine.

If this is not possible with forms or subforms I am open to any other option such as using a combobox on the Calibration form to select the gage before entering the calibration information or any other method that works.

Any thoughts?

[RuralGuy]

Your choice of forms is initially correct. A MainForm/SubForm is an excellent way to deal with a 1:M relationship. Why not just put a SubForm on your frmGage?

I have primary key of Gage_ID in both tables and this part of it seems to be working fine.

This comment is a bit troubling. You do not want the same value as the PrimaryKey of more than one table.

[mikel]

I think I mis-spoke about the primary key in both tables. I'm still trying to understand the terminology. I have a primary key in the Gage table and it is linked to a foreign key (called Gage_ID) in the Calibration table. The primary key in the Calibration table is Calibration_ID. Does that seem right?

I've done this and it doesn't work quite right. On the top is the general information of the gage and on the bottom the calibration information. when I enter new calibration information it seems like the data goes to the right spot in the database, but the all of the information in the text boxes on top disappear. Is there a way to have it leave that information there so I know which gage the calibration information is being entered into?

Another thought. My subform has the top and bottom sections. Would it be better to have the gage information on the main form and then change the subform to just have the calibration information? I think I'll try that.

[mikel]

Having the general gage information on the main form seems to work.

One more question. I would like to add some buttons to add a record, delete a record, and copy a record but when I do this the button affects the records on the main form and not the subform. How do I get the buttons to work on the subform?

[RuralGuy]

I think I mis-spoke about the primary key in both tables. I'm still trying to understand the terminology. I have a primary key in the Gage table and it is linked to a foreign key (called Gage_ID) in the Calibration table. The primary key in the Calibration table is Calibration_ID. Does that seem right?.

This is more like it. Name the two fields the same so it is easy for someone to know they are the same value.

My subform has the top and bottom sections. Would it be better to have the gage information on the main form and then change the subform to just have the calibration information? I think I'll try that.

This is the correct approach. Keep us informed.

[RuralGuy]

One more question. I would like to add some buttons to add a record, delete a record, and copy a record but when I do this the button affects the records on the main form and not the subform. How do I get the buttons to work on the subform?

Are you wanting to delete records from the Calibration table?

[mikel]

Yes, add, delete, and copy items from the Calibration table. When I add a button to the form it affects the Gage table. How do I change the table that the buttons affect?

[RuralGuy]

Have the SubForm in Continuous Form mode rather than Datasheet mode. Then put your delete button on this form and it will display along with the other control.

[mikel]

RG, thank you so much for the help. Your suggestions worked great!

[RuralGuy]

You're very welcome. Is this thread ready for the link in my signature?

[mikel]

I already marked it as Solved. Thanks!

[RuralGuy]

I already marked it as Solved. Thanks!

Sorry Mikel, I missed that. Have a great day.