subform linking (Access 2002)

Topic

New Reply

Hey guys! Thanks for all the past help and I have another question:

I am trying to set up a subform and am using the Subform Linker to link the two forms. I have a combo box on the main form that I want to make a selection from and have the subform update automatically. However, I can only link the two forms, instead of the combo box to the subform. When I link the two forms, I keep getting an error stating that I have a data type mismatch. I believe this has to due with the fact that the linking field in the parent form is an Autonum field and the corresponding field in the subform is a regular text field. However, it is actually the bound field of the combo box that I should be linking to, I think. How do I get around this?

Reply | Quote

Replies

In general, subforms link based on some field in the main form changing. We sometimes use an unbound combo box to apply a filter to a form (I don’t like the way the standard combo box wizard does it), but your Main and Child linking fields should be the same data type. Most often, you have a RecordID on the Main form that is pointed to by Foreign Keys in the Child records – we usually use a long integer for that kind of thing.

Reply | Quote

In general, subforms link based on some field in the main form changing. We sometimes use an unbound combo box to apply a filter to a form (I don’t like the way the standard combo box wizard does it), but your Main and Child linking fields should be the same data type. Most often, you have a RecordID on the Main form that is pointed to by Foreign Keys in the Child records – we usually use a long integer for that kind of thing.

Reply | Quote

The corresponding field can’t be a text field and truly correspond to the autonumber. Why are you trying to link a text field to a long integer?

Reply | Quote

Hi Wendell and Charlotte. Thanks for your responses. I should have known about the field type needing to be the same.

However, I am still have trouble getting my form to update properly. When I first load the form, the subform loads with data associated with the first record of the table bound to the main form. I would expect this, although I need to change it so that it is blank at first. The problem is that changing the selected item in the combo box does not update the subform automatically. Is there some code I should be including? I created an OnChange event proc on the combo box to requery the subform, but that had no effect. Any suggestions?

Just to be clear: The combo box has a hidden bound field that is linked to the corresponding foreign key in the subform. And yes, they are now of the same field type.

Actually, the end result is that I want to be able to make changes/additions to the underlying table bound to the subform. I thought that I had done this before, but it did not allow me to do this just a while ago. Do I need to load a recordset into the subform instead and write the change back to the table, or am I allowed to write back to the table directly. I don’t remember how I did this before.

Again, thanks for all your help.

Reply | Quote

It sounds like your subform is not linked correctly to the parent form. What is in the master and child links in the subform control on the parent?

Reply | Quote

They both point to RecordID of their corresponding tables. It appears to be correct to me.

Reply | Quote

And is RecordID a unique key in the parent form table and a foreign key in the child table?

Reply | Quote

Yes, you are correct. it is a one-to-many relationship.

Reply | Quote

Yes, you are correct. it is a one-to-many relationship.

Reply | Quote

And is RecordID a unique key in the parent form table and a foreign key in the child table?

Reply | Quote

They both point to RecordID of their corresponding tables. It appears to be correct to me.

Reply | Quote

OK, lets try this again. I ended up editing an earlier post instead of replying to yours. If you get this twice, now, I apologize:

The master and child links in the subform control appear to be correct. They are both pointing to the RecordID fields of their corresponding bound tables.

To be clear: The parent form is bound to tblReportDesc. TblReportDesc has an autonum primary key named RecordID, along with a ReportTitle field. The parent form also has a combo box that gets its info directly from tblReportDesc, also. RecordID is the bound field and ReportTitle is the sole field that shows in the cbo. The subform is bound to tblReportItem. The foriegn key for this table is RecordID, corresponding to the primary key in tblReportDesc. Yes, I have set up a relationship between the two tables. Changing the item selected in the combo box should change the data displayed in the subform. It is not.

Ultimately, what I am looking to do is to be able to update (add/modify/delete) records in tblReportItem directly from the subform. I thought I’ve done this before, but can’t find my old code. I don’t seem to be able to type directly into the subform datasheet as it is now. Any thoughts?

Reply | Quote

Setting up a relationship between tables is not going to change the way a form or combobox acts. If you want to add a record to the subform, you still have to take action to populate the linking field, RecordID. The easiest way might be to set the default property of the control to =[Parent].[RecordID], which should insert the current recordID from the parent form into the new record in the subform. Of course, that assumes that your combobox actually moves the parent form to the selected record, which should filter the linked child form down to only the child records that exist for the parent record. At that point, you should be able to add, modify or delete records in the subform.

Ijust noticed your reference to a datasheet. Is that what you’re using? I must say I never use them because they’re much harder to control than a continuous subform, so if you are using a datasheet, someone else may have to chime in on this.

Reply | Quote

Thanks for your help, Charlotte.

A continuous form is probably a better way to go.

As to my parent form, I really have no other data on that form than what is present in the combo box. It is a form used to run a report. There are two other text boxes in the parent form used to limit the date range for the report. The combo box selects which report is to be run. The sub form lists the different groups and subgroups that will be included in that report. Other than that, no data actually appears in the forms.

I’m sure that there is probably a better way to approach this, but this is the way I came up with. Does anyone else have a different idea?? Charlotte, this is why I like getting direction from you and others here because you have so much more experience as this than I do. I haven’t done this enough to know that continous forms behave better than datasheets do.

Reply | Quote

Can you post a stripped down database with a copy of this form and the related tables/queries? Subforms (and their recordset) are normally linked to a parent form and *its* recordset through a linking field. If this is just a form for selecting report criteria, then I’m confused by what you’re trying to do and why you have subforms at all.

Reply | Quote

Charlotte, thanks for your assistance.

In complying with your request I stumbled upon a partial workaround to the problem I was having. I am posting the stripped-down database as you requested. Run the frmReport form and you get the simplified version of the form I was having problems with. It works, now: change the dropdown value and the values in the subform change like I wanted them to. The values in the datasheet will later be used in another query to process the report. I realize they don’t need to be visible in order to use them later, but I want my end users to see what values are going to be used in the report. However, like you said, datasheets pose their own problems.

The embedded object isn’t really a form, but rather just a query’s datasheet. This will work to view the values, except that I cannot make changes to the values in the datasheet directly. I thought this was only a small nuisance since I could create and open another form to make changes to the values listed. To this end, open qryReportItems subform. I thought you could update data in a table through a form based on a query.

Just to be complete: frmReport will have textboxes for dates to limit the report and a command button to run the report, which will trigger a whole series of queries.

Reply | Quote

Charlotte, thanks for your assistance.

In complying with your request I stumbled upon a partial workaround to the problem I was having. I am posting the stripped-down database as you requested. Run the frmReport form and you get the simplified version of the form I was having problems with. It works, now: change the dropdown value and the values in the subform change like I wanted them to. The values in the datasheet will later be used in another query to process the report. I realize they don’t need to be visible in order to use them later, but I want my end users to see what values are going to be used in the report. However, like you said, datasheets pose their own problems.

The embedded object isn’t really a form, but rather just a query’s datasheet. This will work to view the values, except that I cannot make changes to the values in the datasheet directly. I thought this was only a small nuisance since I could create and open another form to make changes to the values listed. To this end, open qryReportItems subform. I thought you could update data in a table through a form based on a query.

Just to be complete: frmReport will have textboxes for dates to limit the report and a command button to run the report, which will trigger a whole series of queries.

Reply | Quote

Can you post a stripped down database with a copy of this form and the related tables/queries? Subforms (and their recordset) are normally linked to a parent form and *its* recordset through a linking field. If this is just a form for selecting report criteria, then I’m confused by what you’re trying to do and why you have subforms at all.

Reply | Quote

Thanks for your help, Charlotte.

A continuous form is probably a better way to go.

As to my parent form, I really have no other data on that form than what is present in the combo box. It is a form used to run a report. There are two other text boxes in the parent form used to limit the date range for the report. The combo box selects which report is to be run. The sub form lists the different groups and subgroups that will be included in that report. Other than that, no data actually appears in the forms.

I’m sure that there is probably a better way to approach this, but this is the way I came up with. Does anyone else have a different idea?? Charlotte, this is why I like getting direction from you and others here because you have so much more experience as this than I do. I haven’t done this enough to know that continous forms behave better than datasheets do.

Reply | Quote

Setting up a relationship between tables is not going to change the way a form or combobox acts. If you want to add a record to the subform, you still have to take action to populate the linking field, RecordID. The easiest way might be to set the default property of the control to =[Parent].[RecordID], which should insert the current recordID from the parent form into the new record in the subform. Of course, that assumes that your combobox actually moves the parent form to the selected record, which should filter the linked child form down to only the child records that exist for the parent record. At that point, you should be able to add, modify or delete records in the subform.

Ijust noticed your reference to a datasheet. Is that what you’re using? I must say I never use them because they’re much harder to control than a continuous subform, so if you are using a datasheet, someone else may have to chime in on this.

Reply | Quote

OK, lets try this again. I ended up editing an earlier post instead of replying to yours. If you get this twice, now, I apologize:

The master and child links in the subform control appear to be correct. They are both pointing to the RecordID fields of their corresponding bound tables.

To be clear: The parent form is bound to tblReportDesc. TblReportDesc has an autonum primary key named RecordID, along with a ReportTitle field. The parent form also has a combo box that gets its info directly from tblReportDesc, also. RecordID is the bound field and ReportTitle is the sole field that shows in the cbo. The subform is bound to tblReportItem. The foriegn key for this table is RecordID, corresponding to the primary key in tblReportDesc. Yes, I have set up a relationship between the two tables. Changing the item selected in the combo box should change the data displayed in the subform. It is not.

Ultimately, what I am looking to do is to be able to update (add/modify/delete) records in tblReportItem directly from the subform. I thought I’ve done this before, but can’t find my old code. I don’t seem to be able to type directly into the subform datasheet as it is now. Any thoughts?

Reply | Quote

It sounds like your subform is not linked correctly to the parent form. What is in the master and child links in the subform control on the parent?

Reply | Quote

Hi Wendell and Charlotte. Thanks for your responses. I should have known about the field type needing to be the same.

However, I am still have trouble getting my form to update properly. When I first load the form, the subform loads with data associated with the first record of the table bound to the main form. I would expect this, although I need to change it so that it is blank at first. The problem is that changing the selected item in the combo box does not update the subform automatically. Is there some code I should be including? I created an OnChange event proc on the combo box to requery the subform, but that had no effect. Any suggestions?

Just to be clear: The combo box has a hidden bound field that is linked to the corresponding foreign key in the subform. And yes, they are now of the same field type.

Actually, the end result is that I want to be able to make changes/additions to the underlying table bound to the subform. I thought that I had done this before, but it did not allow me to do this just a while ago. Do I need to load a recordset into the subform instead and write the change back to the table, or am I allowed to write back to the table directly. I don’t remember how I did this before.

Again, thanks for all your help.

Reply | Quote

The corresponding field can’t be a text field and truly correspond to the autonumber. Why are you trying to link a text field to a long integer?

Reply | Quote