Sub-form button unable to locate data

[mz93]

Hello all,

This is my first post on this forum - albeit I have been using this website as an invaluable resource on obtaining lots of helpful information on building my access database.

I am building a database for work, and I have setup a database with two tables and two forms. There is the main form, entitled "GeneralInfo" which contains address and property identification fields, and then there is a subform entitled "Records_Subform" which contains all of the other various details about the property in question.

For reasons that would take a long time to explain, I have decided to build a report using Microsoft Word, which I have programmed Access to populate the fields by virtue of identifying bookmarks I specified in Word and populating those fields with data from Access. I have setup a button on the subform that you can click to bring up a new word document and it will fill in all the various fields. Works great except there is one problem. The button only works when I am viewing JUST the subform. If I open the main form and click the button in the subform, it gives me a run-time error #2450, "Microsoft Access cannot find the referenced form 'Records_Subform'."

So there lies the first problem: Why doesn't Access recognize the command in the subform when I have the main form open, and what can I do to correct that (see code below).

A second issue I am struggling with is that in addition to the information captured in the subform, I would like for the command button to also populate a few fields in the Word document that are in the main form, GeneralInfo. How do I reference a code to pull information from the main Form (I've tried several references, but they all end up giving me the same run-time error) if the button is only pulling information from the subform right now?

Key note here: I am operating with Access and Word 2010. I only say that because there is a lot of information out there on these topics that I have found for Access/Word 2003 and 2000, so please bear that in mind.

Also another thing is that I am not a programmer by nature, I have limited knowledge on this stuff so please go easy on me. I am learning as much as I can, but I could never quite wrap my head around this stuff.

Private Sub Command195_Click()
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open ("C:\Documents and Settings\****\Desktop\SamplePC.doc")
.ActiveDocument.Bookmarks("ReporterName").Select
.Selection.Text = (CStr(Forms!Records_Subform!Reporting_Party_Name))
.ActiveDocument.Bookmarks("SiteAddress").Select
.Selection.Text = (CStr(Forms!Records_Subform!Street_Address))
.ActiveDocument.Bookmarks("DescriptionofComplaint").Select
.Selection.Text = (CStr(Forms!Records_Subform!Description_Spill))
.ActiveDocument.Bookmarks("SpillRelease").Select
.Selection.Text = (CStr(Forms!Records_Subform!SpillRelease))
.ActiveDocument.Bookmarks("RespStaffMember").Select
.Selection.Text = (CStr(Forms!Records_Subform!Responsible_Staff))

End With
Exit Sub

The area in bold is one example of where the forms information is at...I guess basically I need to know 1) what do I need to change there to make this work in the main form? and 2) what do I need to change to reference a field that is in the main form?

Hope this makes sense, if not I will clarify as much as possible.
Thank you VERY much for reviewing and assisting if possible!
-David

 

[pbaldy]

This should help:

http://www.mvps.org/access/forms/frm0031.htm

 

[mz93]

Paul,

Thank you for the information - although I did see this and tried a few variations in that document.

I had replaced .Selection.Text = (CStr(Forms!Records_Subform!Reporting_Party_Name))

With .Selection.Text = (CStr(Me!Records_Subform.Form.Reporting_Party_Name)) but it tells me it doesn't recognize Me!, and I also tried With .Selection.Text = (CStr(Forms!GeneralInfo!Records_Subform.Form.Reporting_Party_Name)) and variations of this but they all come up with the same run-time error as described above.

Any other ideas? Or maybe I'm choosing the wrong one on the list?

 

[pbaldy]

If the name of the subform control is different than the name of the form, you want the control name. Also, spaces are different than underscores. Can you post the db here?

 

[mz93]

I am unable to post the DB because I do not have enough posts yet.
However, I can tell you that the name of the subform control is Records_Subform (Link Master Fields is Street_Address)
The name of the "subform" itself is Records Subform, with Source being table "Records"

I really feel like this should be the code that I need to be entering but it still gives me the same run-time error:
.Selection.Text = (CStr(Forms!Records_Subform.Form!Reporting_Party_Name))

 

[pbaldy]

No, that's invalid. The syntax of the last one in post 3 should work. The text box name doesn't have spaces, does it?

 

[boblarson]

I am unable to post the DB because I do not have enough posts yet.

That is not true. But what you have to do is to first run COMPACT AND REPAIR on the database file, then right-click on the file and select SEND TO > COMPRESSED FOLDER and then upload the ZIP file that creates.

 

[mz93]

Okay I figured it out...can't believe I didn't see this one.

The code is supposed to be :

.ActiveDocument.Bookmarks("ReporterName").Select
.Selection.Text = (CStr(Me!Reporting_Party_Name))

Apparently just setting it to Me!Fieldname worked. I guess I was trying to be too specific and focused only on the form it was on.

Problem solved.

Thanks for all your help!