Date Entry & Passing Parameters

Topic

New Reply

The Access97 Help file shows an example using a dialog box scheme to enter beginning and ending dates for, say, a report or query. It is a rather complicated affair that uses a macro to call up the dialog box and then make it disappear after entering dates followed by calling the query or report.

Isn’t there a simpler process where one can, for example, open a form, enter the dates, and have the date variables declared public so that they could be picked up and used by any subsequent report or query

Reply | Quote

Replies

Chuck

There are dataBases that come with Access 97. One is Order Entry. This dataBase has just dialog Box that you are looking for.
If you have any problems drop me a line I’ll walk you through it.

Reply | Quote

Hi John,

Thanks for the offer to help. I looked at the Samples files on my Access97 disk and opened all three of the data bases. Couldn’t find a form called “Order Entry” anywhwere. The closest I found was in a database called SOLUTION.MDB and the form was called Employee Sales. It was a form for entering starting and ending dates; however, I get an error message when I click on any command button on the form. Perhaps I don’t have the sample data bases loaded properly or something.

I’m a beginner at Access having come from a DBaseIV background where it would be extremely simple to do what I’m trying to do here. Access seems to complicate things so much, and much of the code is anti-intuitive – at least to an old DBase programmer like myself.

If you have some advice I would welcome it. I would like to come up with a form that permits entry of start and ending dates that are public, i.e. not limited to code within the form used to enter the dates. I would then like to run queries that use these dates to select the data that the query passes. It seems like such a simple thing to do. In DBase I would simply declare them a public variable and any other routine could pick them up and use them. Surely the same thing can be done in Access97?

Thanks again,

Chuck Tucker
ctucker@ieee.org

Reply | Quote

Hi Chuck,

I’ve thrown together a little example for you. Use “frmDates” to enter the dates. You can use the Calendar button to open a popup calendar that makes entering a little easier – I didn’t write it, and I can’t remember where I found it, but it’s a lifesaver!

Hope this helps

Reply | Quote

Hi Mark,

Your example works just fine, as I was able to create the report with data selected between the dates. However, here’s the next challenge. I wanted to have the Startxt and Stoptxt dates displayed in the header segment of the report. I did this by including the variable names Starttxt and Stoptxt in header area. But now, before running the report, it stops to ask for input of the Starttxt and Stoptxt values, i.e. it isn’t getting them passed from the date entry form to the query to the report.

Can’t Access97 have public variables that are picked up by ANY routine that calls on them? It seems like such a simple thing to do.

Reply | Quote

Hi Chuck,

Sure. All you need to do is add a public variable declaration in your report. Then assign it to the variable from the form in the Report_Open event. Something like this:

Public StartDate as Date
Public StopDate as Date

Private Sub Report_Open(Cancel As Integer)
     StartDate = Form_frmDates.Starttxt
     StopDate = Form_frmDates.Stoptxt
End Sub

That should do it. Although there is probably a more efficient way to get there.

One more thing – be sure that the macro opening the report fires before the macro closing the form.

Reply | Quote

However, public variables, while they may seem like a good idea, can let you down badly, since they get reset when an unhandled error occurs. You can’t code in VBA the way you did in dBase, so you’re going to have to work hard to learn a whole new language model. It actually is easier once you get the hang of it, but is isn’t procedural, and that confuses procedural programmers.

Reply | Quote