Replies

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

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?

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?

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

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

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.

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.

Actually, the solution I’m creating is in Access. I can never get Access Charts to do what I want them to do, so I have created an Excel spreadsheet with charts formated as I want them. The end result of this Access solution is a report. When the report is run, one of its actions displays a chart that is linked from Excel. it is included in my report using Paste Special… . When the report is opened in Access, the Excel Chart updates in data prior to being displayed. Its datasource is a linked query from the Access solution I’m creating. So, when I open the report in Access, it pulls in a chart from Excel, but before the chart is displayed, the chart updates it datasource by re-querying Access. Neat little circular logic, I think. This worked well when the Access query did not include parameters. I was just using static criteria in the query grid. It worked fine. When I changed the criteria to use parameters, that is when it broke.

So, Adam, while your solution doesn’t quite fit my design, you got me thinking. Is it possible to construct an SQL statement in Access and present it to Excel to be used in MSQ? I know how to construct the SQL statement, but I wouldn’t know where to begin to get MSQ to use it.

Actually, the solution I’m creating is in Access. I can never get Access Charts to do what I want them to do, so I have created an Excel spreadsheet with charts formated as I want them. The end result of this Access solution is a report. When the report is run, one of its actions displays a chart that is linked from Excel. it is included in my report using Paste Special… . When the report is opened in Access, the Excel Chart updates in data prior to being displayed. Its datasource is a linked query from the Access solution I’m creating. So, when I open the report in Access, it pulls in a chart from Excel, but before the chart is displayed, the chart updates it datasource by re-querying Access. Neat little circular logic, I think. This worked well when the Access query did not include parameters. I was just using static criteria in the query grid. It worked fine. When I changed the criteria to use parameters, that is when it broke.

So, Adam, while your solution doesn’t quite fit my design, you got me thinking. Is it possible to construct an SQL statement in Access and present it to Excel to be used in MSQ? I know how to construct the SQL statement, but I wouldn’t know where to begin to get MSQ to use it.

Hans–

Thanks for the suggestion. I looked into transferring the recordset to Excel through TransferDatabase method, but that will create a new spreadsheet file each time. I have several charts already formated that I just need to update data to. If need be, I could create a chart in Excel using automation from Access, but that is very advanced for my skills and I’m not sure I’d be able to get it done. Is there code already written for this sort of automation that I could use as a template? Again, thanks for your help.

Hans–

Thanks for the suggestion. I looked into transferring the recordset to Excel through TransferDatabase method, but that will create a new spreadsheet file each time. I have several charts already formated that I just need to update data to. If need be, I could create a chart in Excel using automation from Access, but that is very advanced for my skills and I’m not sure I’d be able to get it done. Is there code already written for this sort of automation that I could use as a template? Again, thanks for your help.

I think I answered my own question: When I placed the SQL code into the criteria row, it needed to be wrapped by parentheses. Adding the keyword IN in from on the Select statement before the parentheses solved the problem. I guess I just needed to talk out loud.

I think I answered my own question: When I placed the SQL code into the criteria row, it needed to be wrapped by parentheses. Adding the keyword IN in from on the Select statement before the parentheses solved the problem. I guess I just needed to talk out loud.

Good suggestion! This works great, except that the OoO assistant sends a message with the subject Out of Office Autoreply. Within the OoO setup, you can set a rule to send a template in response to an incoming message, but this, then, produces 2 message–the OoO autoreply and the one produced by the template. Can the OoO Autoreply template be altered, or can the rule be set NOT to send an OoO message, but to only process the associated rules? I could not find a way to do either.

Good suggestion! This works great, except that the OoO assistant sends a message with the subject Out of Office Autoreply. Within the OoO setup, you can set a rule to send a template in response to an incoming message, but this, then, produces 2 message–the OoO autoreply and the one produced by the template. Can the OoO Autoreply template be altered, or can the rule be set NOT to send an OoO message, but to only process the associated rules? I could not find a way to do either.