MDB to ADP query issues 2 3

[kelfuego]

Ok so here is my major malfunction. I have a database that tracks work projects. Each month the user is required to run a report that shows the projects that have been completed and the hours associated with doing it.

My Problem is that my user is very low end and I wouldn't anticipate this person going in and manually changing the date range from within the SQL View to collect the appropriate data for the report. They simply want to click a button.

In the past I had a button setup that would print the report based on a query that asked the user for data--ie the criteria for the date field would show :

between [Enter Start Date] and [Enter Ending Date] which would prompt the user to enter this information.

Now that the DB has been converted to SQL and the front end is Access project file I need to find another way to do this.

I have read several other postings with regard to using a form for the input device, but at this point I'm simply not getting the concept.

Can someone breakdown the solution for me?


Kelly Johnson MCP
Volunters of America Oregon

 

[cjowsey]

Kelly,

You don't want your users to be changing queries or anything in the database except through the application that you have written for them.

You need to add a small parameter form to your application to get the date range and launch the report rather than running the report directly from the button the user normally presses. So here is the step by step approach.

Create a form (let's call it "ReportDates") with two unbound text boxes txtStartDate and txtEndDate and label them "Start Date" and "End Date". Set the format of these text boxes to "Short Date" or "Medium Date" according to your preferences.

Also put two command buttons on the form with captions of "Cancel" and "OK". The Cancel button should simply close the form. The wizard will write the code for this by selecting [blue]Form Operations[/blue], then [blue]Close Form[/blue], or you can put [blue]Docmd.Close[/blue] in the click event of the button. The Cancel button just allows your user to exit gracefully at any time.

The OK button will actually run your report, again you can use the wizard to write the code by selecting [blue]Report Operations[/blue] and [blue]Print Report[/blue], or simply copy the code from the click event of your current report button.

Now we need to put a tiny bit of code behind the OK button to add the date range to the report parameters. Move your mouse over the OK button, click the right button and click on "Build Event...". This will open the Visual Basic Editor. Inside the Click event code, there will be a line like

    Dim stDocName As String

directly below this line, add the following code

    Dim stWhere As String
    stWhere = "[red]<mydate>[/red] BETWEEN " & txtStartDate & " AND " & txtEndDate

Where I have put [red]<mydate>[/red], you need to put the date column you are using for the report range.

You will have a line in the event that looks very much like this

    DoCmd.OpenReport stDocName, acNormal

change it to this

    DoCmd.OpenReport stDocName, acNormal, ,stWhere

and you have finished with the report parameters. We just need to call the parameter form from the button that used to open the report.
Right click on the button that you use to invoke the report and and click on "Build Event...". Now replace all the code in the click event with

    DoCmd.OpenForm "ReportDates"

Save your new form and code and run it. It should work fine unless I have dropped a syntax error in here.

Note that this is not truely robust code yet, ideally you should have default start and date parameters and you should also check that the date parameters are not null before running the report.

Hope this helps.

 

[kelfuego]

So I have followed the instructions given by cjowsey and it appears to be running, but the code is hanging up on something.

I end up getting a message back that states the following:

Invalid SQL Statement. Check the server filter on the form record source.

Anyone have any ideas?


Kelly Johnson MCP
Volunters of America Oregon

 

[cmmrfrds]

In the OnOpen event of your Report.

Put.
Me.FilterOn = True

Debug.Print Me.RecordSource

When you get the error, then do a "Control G" and look at the sql statement that was generated as your recordsource. If you need further help, then paste in the generated sql statement.

 

[kelfuego]

Sorry, but I am really quite new to this. I did as you suggested cmmrfrds, but I am recieving the error message while still on the form that I'm filtering from. I'm not actually making it to the report itself. Here is my code from the filter form (though it is much like that presented by cjowsey.



rivate Sub cmdOk_Click()

On Error GoTo Err_cmdOk_Click

Dim stDocName As String
Dim stWhere As String
stWhere = "<WODate> BETWEEN " & txtStartDate & " AND " & TxtEndDate

stDocName = "RptTimeAllocation"
DoCmd.OpenReport stDocName, acNormal, , stWhere


Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub







Kelly Johnson MCP
Volunters of America Oregon

 

[RiverGuy]

Dates need to be enclosed in single quotes.

 

[cjowsey]

Kelly,

Just remove the <> from around WODate and it should work OK.

If you are still having problems, set a break point on the "stWhere =" line by selecting Toggle Breakpoint on the Debug menu in the Visual Basic Editor and then step through the code line by line using F8. When you hit the error, copy it and paste it up here as cmmrfrds suggested.

 

[BobJacksonNCI]

cjowsey,

Enjoy a star from me, too.

I know the process isn't that complicated, but it's nice to have the detailed step-by-step!

I haven't tested doing this in my project, yet, due to a different long-standing issue I'm wrestling with -- thread702-837819

Maybe I should've posted that problem is this forum, but it didn't seem to be ADP-related.

Anyway, with the direction of Roy Vidar, I'm getting close to solving it. Since you've read all of this, please look at that thread to see if you can help me put the final touches in place.

Thanks!
Bob

 

[cjowsey]

Thanks Bob,

I normally wouldn't go to this level of detail (it feels a bit like doing my kids' homeworrk for them) but Kelly asked for a broken down explanation.

I had a quick look at your problem and it seems that Roy and others are doing a great job there. I'll try and work through it properly, but that may not be until the weekend.

Clive

 

[kelfuego]

Yes and I appreciate it. It's difficult for me sometimes to find the appropriate answer to the problems I create.

All of these tips help me grow a bit and I wish to say thank you to all of you for your help. One of these days I'll hopefully be conversant enough with these things to help others out as well.

kj


Kelly Johnson MCP
Volunters of America Oregon

 

[BobJacksonNCI]

ARGH!

I thought I understood...

I want to pass start and end dates from a form to be used by a Stored Procedure to select desired records.

And I've done exactly that using a form / query / report in straight Access.

But I'm getting mind-boggled with passing parameters to a stored procedure using Access 2000 with SQL 7.

Help ? !
Bob

 

[TimKThailand]

Check NortwindCS.ADP out. Then look at Sale By Year report, it's based on Sale By Year stored procedure and getting DateStart/DateEnd from a Sale By Year dialog form.
The trick is in InputParameters property of the report.

 

[BobJacksonNCI]

A star to TimKThailand!

I knew NorthwindCS.ADP was out there, but...

Worked with our DBA to get the front end / back end sorted out.

This will likely help with other questions going forward.

Thanks Tim.
Bob