Retrieving a field from the current record from a subreport plus complication

[Jake1776]

So the real problem is that I have a field named Print (reserved word). I can't change the field name because of reasons outside my control.

The goal of this code is to be able to click on a field and have that field populate a txtbox which can then be used as search criteria.

The code below gives an error because the subreport is not open. (Coincidentally this is the reason for the clunky code being used to populate the txtbox, so if you know a better way to do that please speak up.)

Private Sub Print_Click()
 
    Dim strWorkOrder As String
    strWorkOrder = Reports!rptsubIPACHistory!("Print")
 
    Forms!frmIPACHistory!txtSearchWorkOrder.SetFocus
    Forms!frmIPACHistory!txtSearchWorkOrder.Text = strWorkOrder
 
 
End Sub
 

[MikeTheBike]

Hi
You do not say what the error is, but I don't think you can access a report's (or form's) properties if it is closed.
But I will stand corrected if this is the case!!??

Also, if it is open, I don't think you can refer to a SubReport or SubForm without going through (referring to) its main From/Report.

You would need something like
Reports!rptMain!rptsubIPACHistory.Report.Print
Or
Reports.rptMain.rptsubIPACHistory.Report.Print

Not very helpful, but if true, then you will need to come up with a different scenario to achieve what you want.

With more information on Form/Report/controls/field relationships and the process required someone may be able to help further.

MTB

[Jake1776]

Okay that will not work because Print is reserved. How do I get around this?

But the error it gives is that "the report isn't open or does not exist" but it does exist, and it's open on the form.

Any other sugestions?

[jforbes]

Mike is right, the Report needs to be opened first. That is also what your error is telling you.

Here is how you go about opening a Report: DoCmd.OpenReport Method (Access)

Once it's Open, you can use some syntax like this to get the value of a Control. Note this is the Report's Control Name, not the Field Name:

strWorkOrder = Reports("rptsubIPACHistory").Controls("Print")

[Jake1776]

So I have to open the report in the background and then make sure both are closed when exiting the form. Seems like a lot of work.

So here's another question.. why does this code work with the report not being open?

Private Sub Req_Num_Click()
 
    Dim strReqNum As String
    strReqNum = Req_Num
    Forms!frmIPACHistory!txtSearchReqNum.SetFocus
    Forms!frmIPACHistory!txtSearchReqNum.Text = strReqNum
 
End Sub
 

Thanks for all the help so far guys! I'm new to VBA so I really appreciate it.

[Jake1776]

So I made it work, though I'm still not certain I understand why the code in Post #1 needs the subreport launched and the code in Post #5 does not, but I think it's because Post #5 links to the txtBox and not to the actual control source (on the subreport). Like I said, I'm not sure.

To solve my problem I simply changed the name of the text box from Print to PrintNum so that I could use the exact same format as in Post #5 and not worry about the reserved word at all. That said I still appreciate that bit of code for getting the value of a control, I'm sure that will come up in the future!

Thanks again!