By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Getting #Name? in All Report Fields

P: 55
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintPreview_Click()
  2.  
  3.     Dim strField As String      'Name of your date field.
  4.     Dim strWhere As String      'Where condition for OpenReport.
  5.     Const conDateFormat = "\#mm\/dd\/yyyy\#"
  6.  
  7.     strField = "RecordDate"
  8.  
  9.     If Not IsNull(cmbReports) And cmbReports <> "<select>" Then
  10.         If IsNull(Me.txtStartDate) Then
  11.             If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
  12.                 strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
  13.             End If
  14.         Else
  15.             If IsNull(Me.txtEndDate) Then       'Start date, but no End.
  16.                 strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
  17.             Else                                'Both start and end dates.
  18.                 strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
  19.                     & " And " & Format(Me.txtEndDate, conDateFormat)
  20.             End If
  21.         End If
  22.     Else
  23.         MsgBox ("You Must First Select a Report To Preview!")
  24.         cmbReports.SetFocus
  25.     End If
  26.  
  27.     ' Debug.Print strWhere                  'For debugging purposes only.
  28.     DoCmd.OpenReport cmbReports, acViewPreview, , strWhere
  29. 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.
Dec 10 '07 #1
Share this Question
Share on Google+
11 Replies


JustJim
Expert 100+
P: 407
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintPreview_Click()
  2.  
  3.     Dim strField As String      'Name of your date field.
  4.     Dim strWhere As String      'Where condition for OpenReport.
  5.     Const conDateFormat = "\#mm\/dd\/yyyy\#"
  6.  
  7.     strField = "RecordDate"
  8.  
  9.     If Not IsNull(cmbReports) And cmbReports <> "<select>" Then
  10.         If IsNull(Me.txtStartDate) Then
  11.             If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
  12.                 strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
  13.             End If
  14.         Else
  15.             If IsNull(Me.txtEndDate) Then       'Start date, but no End.
  16.                 strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
  17.             Else                                'Both start and end dates.
  18.                 strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
  19.                     & " And " & Format(Me.txtEndDate, conDateFormat)
  20.             End If
  21.         End If
  22.     Else
  23.         MsgBox ("You Must First Select a Report To Preview!")
  24.         cmbReports.SetFocus
  25.     End If
  26.  
  27.     ' Debug.Print strWhere                  'For debugging purposes only.
  28.     DoCmd.OpenReport cmbReports, acViewPreview, , strWhere
  29. 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
Dec 10 '07 #2

P: 55
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.
Dec 10 '07 #3

JustJim
Expert 100+
P: 407
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
Dec 10 '07 #4

P: 55
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.
Dec 11 '07 #5

JustJim
Expert 100+
P: 407
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.
Expand|Select|Wrap|Line Numbers
  1. Forms![FormName]![SubFormName].Form![ControlName]
  2. Forms("FormName")![SubFormName].Form![ControlName]
  3. Form_FormName![SubFormName].Form![ControlName]
Jim
Dec 11 '07 #6

P: 55
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.
Expand|Select|Wrap|Line Numbers
  1. Forms![FormName]![SubFormName].Form![ControlName]
  2. Forms("FormName")![SubFormName].Form![ControlName]
  3. 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.
Dec 11 '07 #7

P: 55
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.
Dec 12 '07 #8

JustJim
Expert 100+
P: 407
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmViewAllResources", , , , , acHidden
Jim
Dec 12 '07 #9

P: 55
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 13 '07 #10

P: 55
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.
Expand|Select|Wrap|Line Numbers
  1. =Forms!frmViewAllResources!sfrmAdminTab.Form!Requestedby
  2.  
Dec 13 '07 #11

JustJim
Expert 100+
P: 407
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.
Expand|Select|Wrap|Line Numbers
  1. =Forms!frmViewAllResources!sfrmAdminTab.Form!Requestedby
  2.  
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
Dec 13 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.