Report does not recognize ref to form

[fredk]

I tried to post this in the forms section and in the general questions but have not received any feedback to help me. Therefore, I am trying to post in reports section.

I have several reports that use a startdate, enddate and a market field from a form. In my report I refer to the form to describe what is on the report - here is an example:

=IIf(IsNull([Forms]![frmreport]![market]),"all markets",[Forms]![frmreport]![market] & " " & "market only") & " " & "- dates between" & " " & [Forms]![frmreport]![startdate] & " " & "and" & " " & [Forms]![frmreport]![enddate]

For some reason, I occasionally get an error that states:

Microsoft Access can't find the field 'forms' referred to in your expression

Like I said, it only happens occasionally - when it does, I must run a different report first and then I can run the report that gives me the problem.

any help would be greatly appreciated!!!

Thanks!!!

 

[dhookom]

Are you closing the form at any point while the report is open or in its On Close event?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredk]

Ah, Duane.... I have been hoping you would help.

I am not closing the form. However, I have a frmdata that is open. There is a button on frmdata to open frmreports.

The frmdata has some code (nothing I would think that would effect this problem) to allow the data to be edited when you click either the edit or add button - (i.e oncurrent event has

Me.AllowEdits = False
Me.AllowAdditions = True

The only other thing that I want to mention is that the frmreports has a tab control and I used 3 fields that will be used for criteria accross all tabs. Meaning, when I created them, I dropped them in the detail section so they would be seen accross all tabs. I then moved them to the header.

Duane, thanks again for taking the time!!!!!

Fred

 

[fredk]

Duane, one more piece of information - I jusr added some code to my buttons used to run the reports - I just added:

Me.startdate.SetFocus

I am not getting the error now (at least the 50 times that I just tested I did not get the error)

Could if have something to do with the tab control and focus being on that rather than that actual form?

Thanks again!!

Fred

 

[dhookom]

I would think it is something else.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredk]

I have looked through everything an unable to figure out a reason - One other issue that is going on (I mention it because maybe it will mean something to you) is that occasionally, the sub-form on frmgroups is blank.

Any other thoughts? Thanks!!!!

Fred

 

[dhookom]

I'm really getting confused since you earlier mentioned forms:
[li]frmreport[/li]
[li]frmdata[/li]
and now you are adding mention of
[li]frmgroups[/li]
[li]a subform on frmgroups[/li]
What's up with all these forms?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredk]

I mentioned from groups with the sub-form because I thought that form being open may somehow effect my from data. Here is what I am doing:

frmgroups is a data entry form that also contains a sub-form. From the frmgroups form there is also a button to open up frmreport. frmreport is the form I use to run reports from. When you open frmreport, frmgroups remains open and I was wondering if that was somehow effecting my issue with frmReport.

Like I said, the strange thing is it is happening infrequently.

Thanks!!!

 

[fredk]

Duane, one more update, I had another user test this database - they received an error that said:

"there was a font initialization error"

Thanks!!

 

[dhookom]

I can't imagine what would cause the initial error. When the error occurs, I would try open the debug window (press Ctrl+G) and enter:

? [Forms]![frmreport]![market] & [Forms]![frmreport]![startdate] & [Forms]![frmreport]![enddate]

"font initialization error" - what have you done to troubleshoot this issue?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredk]

the font initiatlization error only happened when the other user first tried to run a report - it has not happened since - I cannot recreate that error - I just mentioned it because I was not sure if they were somehow related but I guess not.

I reproduced the error and then ran the debug window - when I type in above, I get:

1/1/0612/31/06

However, if I type just [forms]![frmreport]![market] I get

Null

The query is set up so that if the market value is null, include all markets. ??

Thanks!!!!!

Fred

 

[dhookom]

This only proves that even if the report generates an error, the reference to the form and controls works. I can't provide any other direction other than to suggest you have some corruption or something that you are telling us about.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredk]

Okay - I appreciate your time and help.

 

[dhookom]

If a form is not used for data entry, consider cleaning it up by setting:
Scroll Bars: Neither
Record Selectors: No
Navigation Buttons: No
Dividing Lines: No



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ProgramError]

fredk can i just check with you that these are the results you expect using the formula at the top of this thread.

IIf(IsNull([Forms]![frmreport]![market]),"all markets",[Forms]![frmreport]![market] & " " & "market only") & " " & "- dates between" & " " & [Forms]![frmreport]![startdate] & " " & "and" & " " & [Forms]![frmreport]![enddate]

if the market textbox is null you get
"all markets - dates between STARTDATE and ENDDATE"

if its not null you get
"MARKET market only - dates between STARTDATE and ENDDATE"

The capitalised words relate to the input form data.
Is this what you expect as a result?



Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.