Searching, Date Range Dialog boxes & week format

[bajanJedi]

Hey all,

I'm working on this library database and turns out to be more work than I had expected. So on to the problems at hand.

1) This so much isn't a problem, more on the lines of needed advice. I am concerned about searching for records in forms. Do you think it is ok for the user of the database to go to the Edit menu and click Find to look for the record or keyword or should there be a search field? I think in the name of simplicity the user can go through th Edit menu, but any input about this would be greatly appreciated.

2) The librarian is also concerned about not being able to understand the weeklyLoans report because currently the query that generates the report starts counting from the first week of the year. No dates or anything, just numbers. I would like to know if there is a way I can produce actual dates from the week numbers.

3) The last thing for now is the date range dialog box. I have to devise a way to create a date range dialog box to enter the start and end date in order to produce more specific daily, weekl, monthly and yearly book loan reports. I am currently having trouble understanding the coding for it all. Would I need more than one of these date range dialog boxes for each report or just one?

I know this is currently alot to tackle at one time, but I didn't want to be a nuisance and keep posting one or two questions here and there whereas I could post this big thing and get advice on some parts. Help on anything would be greatly received and dearly appreciated

thanks

Lisa...

 

[DreamerZ]

#1) I'd create a "Find" or "Search" button on the form that duplicates the edit/find that you need to do. I've always found that's more user friendly.

#2) Do you store dates somehow or is it just an integer for the week? If it's actual dates, you can use the information from #3 below to get the date range. If it's just an integer, you're still better off creating dates and then using the Format("w"...) function to get the week number. You should be able to do more with an actual date than just an integer.

#3) Depending upon the queries you are running, you can use one date range form to input ranges as described above. If you create a simple unbound form with a start date field and an end date field, use that for the query criteria in the date field (Between FormName!StartDate and FormName!EndDate). Then, when opening the report, create a macro that opens the form, stores the data and then opens the report with the entered date range. In the macro, you need an open report that opens the form, and error check like, "Not IsLoaded("DateRange&quot", an Okay that Sets the Values, and a close that closes the form.

Hope that helps.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[bajanJedi]

Hi, thanks for the reply, I really appreciate it. I'm mainly going to concentrate on #3 for now 'cause I am having a prolem understanding what goes where and maybe my design for this is wrong.

The query I have is used for generating the total number (count) of books loaned on a daily basis. I have other queries which are similar to this and are based on a weekly, monthly and yearly basis. Reports are also generated using these queries.

I have to create a switchboard for the user. The plan for this switchboard is to have the the daily, weekly, monthly and yearly book loan reports on one "board", so when I click on the command button to open the daily report, the date range dialog box should come up and the user can enter the dates click ok and the report opens. I have placed the code you suggested in the criteria area of the daily book loan query under the date. When I run the query an 'enter parameter value' dialog box appears for both the start and end date.

1)Is that supposed to happen? and
2)I think it works fine, 'cept for the frmDateRange!txtStartDate label it carries. Is there a way I can replace that text to say 'Enter the start date"?


Lisa

 

[DreamerZ]

The dialog box that pops up is correct. That's the standard input box for the criteria listed in the query.

I haven't found a way to replace that box with something else other than a form, which you've referenced (frmDateRange). If you create a form named frmDateRange with text boxes for StartDate and EndDate, then create the macro that opens that form on report open, the standard criteria boxes won't open.

The macro name doesn't matter, but the coding does. You should have 5 "macro's" in the one as follows:
Overall Name: ReportbyDate
#1 MacroName: OpenReport
Action: Open Form with the Form Name listed

#2 MacroName:
Condition: Not IsLoaded([FormName])
Action: CancelEvent

#3 MacroName: CloseReport
Action: Close Form with the Form Name listed

#4 MacroName: OkReport
Action: SetValue

#5 MacroName: CancelReport
Action: (Same as #3)

Then, in the Report Properties, put the Open macro (ReportbyDate.OpenReport) in the On Open Event and put the Close macro (ReportbyDate.CloseReport) in the On Close Event.

In the Form, add 2 buttons, Show Report and Cancel (or something similar). In the On Click of the Show button, but the Ok macro (ReportbyDate.OkReport) and in the On Click for the Cancel button, put the Cancel macro (ReportbyDate.CancelReport)

If someone else knows of a way to change the default boxes, I'm all ears too. If not, the form works.

Hope that helps!


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[bajanJedi]

OK, i made the new macro and put on the new buttons. I get an error saying that 'The expression you entered has a function name that Microsft Access can't find'. Then when I click OK, the parameter value dialog boxes pop up.


Lisa

 

[DreamerZ]

You get an error when the date form opens or after you enter dates and click OK on the form?

It sounds like the events aren't correct. You should have:
macroname.macroevent like "ReportDate.OpenReport" Remember, there is only 1 macro with 5 actions in it. The ReportDate is the macro and everything after the . are the individual actions.

In the Northwind sample database, you can see the Event format under the AfterUpdate event on the Customers Form.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[bajanJedi]

Ok, when I double click and open frmDateRange, it's fine. I enter the two dates and click ok. Then nothing happens. Nothing at all. Well, the Cancel button works.

When I open the the DailyLoans report, the frmDateRange form opens, then the error message appears. When I click OK, the two other dialog boxes show up.

 

[DreamerZ]

You're not going to open the FORM directly. You open the report, which opens the FORM (based on the OnOpen macro action in the report).
Make sure the Open macro action is in the OnOpen Event for the Report and open the report.

Try the code below for the criteria in the query:
Between [Forms]![frmDateRange]![StartDate] And [Forms]![frmDateRange]![EndDate]

Make sure the StartDate and EndDate field names in the criteria match the names on the form.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[bajanJedi]

Hey, I know this might be getting off subject, but I was wondering if this was a simple solution of something else. In the same database I have a button called Library Books Due. When the user clicks on it a dialog box appears and prompts the user to enter the current date (to find out which books are due). When the user clicks OK, a report comes up showing all the books due and the information on the person that borrowed the book.

I was wondering if there was any way I could avoid having the user from typing in the current day, but keep the dialog box when the the Library Books Due' button is pressed and have the current date show up in the text box automatically?

bajanJedi

 

[DreamerZ]

If that dialog box comes from the query criteria as we've been discussing, you might be able to set the critera to the current date rather than ask for it. Use maybe:

Format(Now,"m/d/yyyy")

I tried it in a similar query for a DB I have and it worked.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[bajanJedi]

I know I have not replied in long, so let me apologize. Much has changed in the database since I last posted here. I have utilized the activeX date picker control to help with the generation of the Books Due Today report. I find it is much easier to use and less coding. I managed to fix the Daily Books Loaned and implement it also with the date picker. I am still working on the other 3 - weekly, monthly and yearly.

Lisa aka bajanJedi

 

[Jinan74]

can anyone help me or inform me about the activex, how can i added it use it in my DBs.. pls i need complete infos.
Thanks in advance.