Getting #Name? in All Report Fields

[BASSPU03]

Tomorrow's my final presentation of my DB and I ran into an unexpected problem: I access a main form called frmViewAllResources with several tabbed subforms through my switchboard. There's a preview and print button on the main form that generates a report called "Commodities." It previews and prints all the relevant fields from the main form and subform just fine.

On my switchboard, I can also access a form to print reports using a date range. My sample report "Commodities," which was auto-wizarded on tblResources (frmViewAllResources) worked just fine an hour ago. Then, I opened the report and started adding textboxes and comboboxes that linked to other forms (i.e. the subforms in frmViewAllResources). After flipping back to form view, all the boxes displayed the correct information for all 4 dummy records.

However, once I opened up the form to print reports by date range and selected "Commodities," the report opened up with "#Name?" in all fields. This is the code I'm using for my date range form:

Private Sub cmdPrintPreview_Click()
 
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"
 
    strField = "RecordDate"
 
    If Not IsNull(cmbReports) And cmbReports <> "<select>" Then
        If IsNull(Me.txtStartDate) Then
            If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
                strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
            End If
        Else
            If IsNull(Me.txtEndDate) Then       'Start date, but no End.
                strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
            Else                                'Both start and end dates.
                strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
                    & " And " & Format(Me.txtEndDate, conDateFormat)
            End If
        End If
    Else
        MsgBox ("You Must First Select a Report To Preview!")
        cmbReports.SetFocus
    End If
 
    ' Debug.Print strWhere                  'For debugging purposes only.
    DoCmd.OpenReport cmbReports, acViewPreview, , strWhere
End Sub

Would anything here having anything to do with my form's phenomenon? Or, should I instead have my report's fields refer to the tables instead of the forms? Linking to the tables makes much more sense, so I'm going to try that. I apologize for not trying it before posting, but my time is limited so I'll need some ready advice if it doesn't work. Thanks.

[JustJim]

Tomorrow's my final presentation of my DB and I ran into an unexpected problem: I access a main form called frmViewAllResources with several tabbed subforms through my switchboard. There's a preview and print button on the main form that generates a report called "Commodities." It previews and prints all the relevant fields from the main form and subform just fine.

On my switchboard, I can also access a form to print reports using a date range. My sample report "Commodities," which was auto-wizarded on tblResources (frmViewAllResources) worked just fine an hour ago. Then, I opened the report and started adding textboxes and comboboxes that linked to other forms (i.e. the subforms in frmViewAllResources). After flipping back to form view, all the boxes displayed the correct information for all 4 dummy records.

However, once I opened up the form to print reports by date range and selected "Commodities," the report opened up with "#Name?" in all fields. This is the code I'm using for my date range form:

Private Sub cmdPrintPreview_Click()
 
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"
 
    strField = "RecordDate"
 
    If Not IsNull(cmbReports) And cmbReports <> "<select>" Then
        If IsNull(Me.txtStartDate) Then
            If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
                strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
            End If
        Else
            If IsNull(Me.txtEndDate) Then       'Start date, but no End.
                strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
            Else                                'Both start and end dates.
                strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
                    & " And " & Format(Me.txtEndDate, conDateFormat)
            End If
        End If
    Else
        MsgBox ("You Must First Select a Report To Preview!")
        cmbReports.SetFocus
    End If
 
    ' Debug.Print strWhere                  'For debugging purposes only.
    DoCmd.OpenReport cmbReports, acViewPreview, , strWhere
End Sub

Would anything here having anything to do with my form's phenomenon? Or, should I instead have my report's fields refer to the tables instead of the forms? Linking to the tables makes much more sense, so I'm going to try that. I apologize for not trying it before posting, but my time is limited so I'll need some ready advice if it doesn't work. Thanks.

I think that it is likely to be the way you are referring to the sub-forms. Check out this thread for details on how to do this elegantly.

Jim

[BASSPU03]

I think that it is likely to be the way you are referring to the sub-forms. Check out this thread for details on how to do this elegantly.

Jim

Thanks, Jim. I've used the expression builder and believe that I've correctly linked to the sub-forms through the loaded forms. The problem appears to extend to the main form as well. I'll keep plugging away on my end, but any further thoughts would be appreciated.

[JustJim]

Thanks, Jim. I've used the expression builder and believe that I've correctly linked to the sub-forms through the loaded forms. The problem appears to extend to the main form as well. I'll keep plugging away on my end, but any further thoughts would be appreciated.

Post a copy of the Control Source of a control on the main form and one from a sub form and we'll have a look.

Jim

[BASSPU03]

Post a copy of the Control Source of a control on the main form and one from a sub form and we'll have a look.

Jim

Here's some new information: The main form control sources are now working. It turns out the report is based on the table (tblResources) and only needs a field reference (e.g. AutoNumber) and not the whole "Forms!frmView...etc." syntax. They would work some of the time because they were referencing the right controls on the right form when it was loaded (i.e. open). When I'd close the corresponding form that was open in the background, the "#Name?" would appear in the fields of the report. So, not only did I solve that "#Name?" mystery, but I've also corrected the references.

However, I'm still having a problem with the subform references. In control source "Requestedby," I've tried the following:

=Forms!frmViewAllResources!sfrmAdminTab.Form!Admin Requestedby

No such luck with the subform references. Any ideas? Thanks.

P.S. A space inadvertently appears in "AdminRequestedby" on this post. It looks like "Admin Requestedby," but should be the former.

[JustJim]

Here's some new information: The main form control sources are now working. It turns out the report is based on the table (tblResources) and only needs a field reference (e.g. AutoNumber) and not the whole "Forms!frmView...etc." syntax. They would work some of the time because they were referencing the right controls on the right form when it was loaded (i.e. open). When I'd close the corresponding form that was open in the background, the "#Name?" would appear in the fields of the report. So, not only did I solve that "#Name?" mystery, but I've also corrected the references.

However, I'm still having a problem with the subform references. In control source "Requestedby," I've tried the following:

=Forms!frmViewAllResources!sfrmAdminTab.Form!Admin Requestedby

No such luck with the subform references. Any ideas? Thanks.

P.S. A space inadvertently appears in "AdminRequestedby" on this post. It looks like "Admin Requestedby," but should be the former.

The easy one first... use Code Tags around code and you will get a better appearance. Add an equals sign and either vb or sql or text just before the closing square bracket of the opening tag for added functionallity.

Second, try the other two methods listed below, shouldn't make a jot of difference, but hey, you never know.

Forms![FormName]![SubFormName].Form![ControlName]
Forms("FormName")![SubFormName].Form![ControlName]
Form_FormName![SubFormName].Form![ControlName]

Jim

[BASSPU03]

The easy one first... use Code Tags around code and you will get a better appearance. Add an equals sign and either vb or sql or text just before the closing square bracket of the opening tag for added functionallity.

Yeah, sorry about that! I always forget to encircle one-line codes with the Code Tags.

Second, try the other two methods listed below, shouldn't make a jot of difference, but hey, you never know.

Forms![FormName]![SubFormName].Form![ControlName]
Forms("FormName")![SubFormName].Form![ControlName]
Form_FormName![SubFormName].Form![ControlName]

Jim

I tried them--no luck. I think I'm going to try just using the wizard to create the subforms within the reports. I initially created the report with all unbound controls (in order to design it "freehand") and then wanted to associate all the controls accordingly afterwards. Well, I don't know why I'm still having trouble even with the expression builder.

I'd be happy to hear any other thoughts. In the meantime, I'll work on it and then post back any progress. Thanks.

[BASSPU03]

OK, the main problem is this: The report is bound to "tblResources," which makes it easy to bind any unbound control, as long as the control source is a field (e.g. Resource) in tblResources. However, if I have an unbound control refer to another table's/form's control, then I get "#Name?" in those controls. Why? Apparently, it's because the form (i.e. the one to which these controls refer) isn't loaded (i.e. open.)

So, it's not so much that my references are incorrect. If I open the form (frmViewAllResources) to which the controls refer and then run the report over that open window, all the "subreport" controls work. How do I load the form frmViewAllResources hidden in the background when I preview the report? Is this a good way to approach it?

Thanks.

[JustJim]

OK, the main problem is this: The report is bound to "tblResources," which makes it easy to bind any unbound control, as long as the control source is a field (e.g. Resource) in tblResources. However, if I have an unbound control refer to another table's/form's control, then I get "#Name?" in those controls. Why? Apparently, it's because the form (i.e. the one to which these controls refer) isn't loaded (i.e. open.)

So, it's not so much that my references are incorrect. If I open the form (frmViewAllResources) to which the controls refer and then run the report over that open window, all the "subreport" controls work. How do I load the form frmViewAllResources hidden in the background when I preview the report? Is this a good way to approach it?

Thanks.

Try this

DoCmd.OpenForm "frmViewAllResources", , , , , acHidden

Jim

[BASSPU03]

A short follow-up to my attempt to auto-wizard drag-&-drop subforms into the report:

The beauty of just dropping an auto-wizard subform into the report is that it allows you to link the controls using a parent/child relationship. I tried this and formatted each subform dropped into the report as if I were using unbound controls that I could just move around and manipulate. I was very pleased. Sadly, when I went back to frmViewAllResources and clicked on the tabbed subforms, all the subforms' designs were changed to look like what I had formatted on the report! It's a GOOOOOD thing that I'd saved many backups of my DB. All I had to do to recover the design of each subform was copy the controls from an old DB version and paste 'em in the current copy.

Try this

DoCmd.OpenForm "frmViewAllResources", , , , , acHidden

Jim

Jim, that's a great suggestion. This allows me to format the report like I want with unbound controls. Loading frmViewAllResources in the background works well to successfully display the information. However, because these unbound controls aren't linked (by AutoNumber) to the main form's report controls, the records don't change. It displays File Number 130 with all the main form controls and the correct subform controls, but the subform controls don't change when I print preview pages and view File Number 131.

If I tried (and I have) to link the unbound controls to the tables (as opposed to the forms), I believe I'd still have no way to make the records correspond to the main forms' controls. I'm pretty sure I'd get "Enter Parameter Value" for each unbound subform control upon initializing the report.

Is there a way to make the control source of the report be multiple forms/subforms, which'll then allow the controls to draw from various sources? I'd say this report issue is keeping me from fixing and finishing my DB. All your help thus far has been grand.

[BASSPU03]

Oh, would it help if I used and based the report on a query instead? I'm guessing this way I can combine all the records I need.

UPDATE:
It appears to have worked. Now I don't need all the long references like below:

e.g.

=Forms!frmViewAllResources!sfrmAdminTab.Form!Requestedby
 

[JustJim]

Oh, would it help if I used and based the report on a query instead? I'm guessing this way I can combine all the records I need.

UPDATE:
It appears to have worked. Now I don't need all the long references like below:

e.g.

=Forms!frmViewAllResources!sfrmAdminTab.Form!Requestedby
 

Yes, my next suggestion was to take a deep breath and a short step back. Lets limit the number of places your report has to look to get it's data from.

Ideally this would be one source (1 table or 1 query or 1 form etc). Let's make it 1 query but use a preceding step to use 1 form to gather any operator input into parameters of that query. Sounds good in theory, doesn't it?

Jim