Error 2450 making me crazy

[Jim Stanicki]

The code below displays the form and allows me to enter the dates then gives me an runtime 2450 error on the next statement. I would appreciate someone taking a look.
Thanks
Jim

Private Sub Report_Open(Cancel As Integer)
Dim StrBegDate As String
Dim StrEndDate As String

' Put the form and get the begin and end dates
DoCmd.OpenForm "FrmStockLevelDates", acNormal, , , , acDialog

'If dates are blank load the defaults
StrEndDate = Nz(Forms("FrmStockLevelDates")!txtEndDate, "12/31/2010")
StrBegDate = Nz(Forms("FrmStockLevelDates")!txtBegDate, "01/01/2006")

'Close the form
DoCmd.Close acForm, "FrmStockLevelDates"

End Sub

 

[chergh]

What next statement?

I'm guessing the issue is your reffering to the form after you've closed it.

 

[Jim Stanicki]

Sorry, I guess that wasn't clear, by next statement I meant the next executable statment after the form is displayed.

StrEndDate = Nz(Forms("FrmStockLevelDates")!txtEndDate, "12/31/2010")

Thanks for looking
Jim

 

[chergh]

Try changing that to:

nz(Forms!frmstockleveldates!txtenddate, "12/31/2010")

or

nz(forms("frmstockleveldates").txtenddate.value, "12/31/2010")

 

[Jim Stanicki]

Thanks chergh, but I get the same 2450 error on both of your suggestions.
Is this the way you would prompt for date to be used in a report? I need to reference the dates in the reports query as well as for headings in the report. I put the code in the report _open event.
Thanks
Jim

 

[chergh]

You won't, I think, be able to use your strenddate to pass that value to a query as it is a string and the query will require a date datatype, though I suppose you could use the cdate function.

Can you post the whole error message you receive when running the code.

 

[Jim Stanicki]

Runtime error 2450
Microsoft Office Access can't find the form 'frmStockLevelDates' referred to in the macro expression or visual basic code

 

[chergh]

If you remove the nz function does it work?

 

[rolaaus]

Have you set the Format property of the control to Short Date?

Also, you can try make the values into Dates rather than Text strings.

StrEndDate = Nz(Forms("FrmStockLevelDates")!txtEndDate, "#12/31/2010#")
StrBegDate = Nz(Forms("FrmStockLevelDates")!txtBegDate, "#01/01/2006#")

 

[Jim Stanicki]

No, Just a plane old form

 

[chergh]

Try removing the nz function then use

if strenddate = "" then
strenddate = "12/31/2010"
end if


and the same for strbegdate

 

[boblarson]

When you open a form as acDialog, it opens as modal which means it won’t do ANYTHING until you interact with that form and close it. So, since you’ve opened it as dialog it will not do the other three lines of code until the form is closed which means that the form isn’t open for the code to work. Remove the acDialog from the OpenForm part and it should work.

 

[Jim Stanicki]

Rolaaus,
I confirmed The foramt is short date. Then I tried your code and I got the same results.
Thanks
Jim

 

[Jim Stanicki]

Bob,
When I remove the AcDialog the form does not display at all it just launches the report. Should I put another option in its place?
Thanks
Jim

 

[boblarson]

yeah, why not assign the values in the On Close event of the form so you can keep the acDialog.

 

[Jim Stanicki]

Bob,
When I move the date assignments to the Form_Close event the dates are not available to the report (I am prompted for them). How do I do the assignments so the report can see them? Also I would like to use them for the SQL that feeds the report.
Thanks
Jim

 

[boblarson]

Make sure your variables are accessible to the report as public in a standard module and then refer to them via a function for the inputs. Also, why are you using string values for dates? Your dates should be stored as dates and not strings in the tables.

To do a function that returns the variables you can do (in a standard, not form module):

Public Function StartDate() As Date
     StartDate =  dteStartDate 
End Function

And in your report you can reference
=StartDate()

and you then build one for your end date too.

 

[rolaaus]

Correct me if I am misunderstanding - usually breaking down what you want done specifically is better than "how do you do ....", because like you said an "old dog" might be trying to figure out the procedure to accomplish a task, when what we need to determine is what is the "business need".

So, if I am correct, then you are wanting to pass the date values into the report.

If this is true, then you 2 options. Consider using the WhereCondition clause, or OpenArgs.

docmd.OpenReport ReportName:="rptName", & _
  View:=acViewNormal, Filtername:="DontUseInThisCase", & _
  WhereCondition:="[strBegDate] = #" & Nz(Forms("FrmStockLevelDates")!txtEndDate, "12/31/2010") & "#" & _
"and [strEndDate] = #" & Nz(Forms("FrmStockLevelDates")!txtBegDate, "01/01/2006")
& "#" _
, WindowMode:=acViewNormal, OpenArgs:=""

OpenArgs might be a tad bit tricky because you are passing in 2 dates, but with a bit of creativity with Variants or string manipulation you should be good to go. Consider using a field seperator like the PIPE | character.

 

[Jim Stanicki]

Rolaaus,
I think the problem is too many options. Do I invoke the report from the form or the form from the report?
Soapbox = On
I am an old dog programmer trying to learn Access and also Microsoft terminology. I believe the problem I am having is with something we used to call scope. That refers to where a variable is visible from. As in a local variable would only be available to the function it is in. A global variable is available everywhere. Bob's answer is to make the dates global. You suggest I pass it as a local. Both are option I need to learn about. My problem now is understanding more about scoping in Access. Apparently Access refers to it a different a way because I can not find "scope" in the help files. I need a book that tells me how Access works not how to do things. Once I know how it works I can figure out how to do stuff.
Anybody know where I can get such information? or what we call scope now?
I feel better now
Soapbox = off
Thanks
Jim

 

[rolaaus]

I re-read your original post and now realize you need to reference these dates not only in the recordsource (filter/clause) but for report header info.

Are you comfortable enough with string manipulations? Pulling the left half of an arguement and the right half into 2 seperate variables? If so, then go with this option.

strOpenArg = Nz(Forms("FrmStockLevelDates")!txtEndDate, "12/31/2010") & "|" & Nz(Forms("FrmStockLevelDates")!txtBegDate, "01/01/2006")

DoCmd.OpenForm "FrmStockLevelDates", acNormal, , , , acDialog, strOpenArg

Then in the On_Open event of the report, capture your 2 dates into some variables that you would declare as Public, or just place those date values right into the controls.value property.