multi user reporting interface

[JERD1074]

hey guys

I created a form in database that a user can select a start date and an end date, it stores these dates in a table. then the user can run whatever reports they want to. the dates are for their reports... pulling data between start date and end date.

when there is only one user pulling reports, this works just fine. but if there is more than one, we run into problems. their dates are not necassarily the same, so they could pull their reports and end up with different dates than what they put in because the other user changed them. etc...

just trying to brainstorm of some different ideas to create a multi user reporting interface, where several users can enter whatever dates they want and the reports they pull will use those dates.

one idea is to create a new record each time and for the reports to look at the DateID (autonumber)... but that would create a lot of useless records, and i could delete the record when the user closes the reports form... but would that possibly open the wonderful feature access has... bloating?

anyway, i'd like to hear some idea any of you might have. if you would like more info, let me know. i'd be happy to indulge.

Thanks.

 

[BobStubbs]

I would use the CurrentUser variable to get each user's name. This assumes that you are using User and Group security in your database, so each user logs into the database with a different User Name.

First, add a UserName field to your table of dates.

Add the UserName field to your form. Now, you can put the value of CurrentUser into the UserName field, e.g. by writing in the Form_Open event:

UserName = CurrentUser()

The value of CurrentUser will now be stored in the table, along with that user's chosen start and end dates.

You should be able to amend your code which passes the dates to the report, so that it retrieves the correct dates for the logged in user. If you are using DLookup, the code would look like this:

strStartDate=DLookup("[StartDate]","tblReportDates","[UserName]="  & CurrentUser())
etc

I hope this idea will be of some help to you.

Bob Stubbs