Pop Up Calendar 2

[JCMcAbee]

Access 2003. I have a form that has a command button to print a report. However, I would like for the button to popup a calendar to input a date on the report before it prints it. I have tried the date function, but it only asks for the date to be entered. What I would like is for a calendar to pop up, have the user select a month/date, and have that date entered into a location on the report, before it prints the report.

Any help is very much appreciated. Thank you

 

[dhookom]

The generally accepted solution (IMHO) is to have a form open first that allows for date entry. There are quite a few great calendar links at

http://www.accessmvp.com/JConrad/accessjunkie/calendars.html

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

Duane - Thanks for the reply. I have tried many of the calendars posted, but I have difficulty in getting them to put the date into the report. I have managed to get a calendar to input the date into a table, however, I am stumped as to how to get that information into a report. I am not sure if this is clear, but I am really stumped. Thanks again.

 

[dhookom]

You could have an unbound text box on a form that gets its value from the popup calendar. The report text box can then have a control source of:

=Forms!frmGetDate!txtDateFromCalendar

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

Thanks again. I thought I would provide more information, and run this method by you: I have a working form with the Popup Calendar that saves the date, along with a job number to a table called Job Numbers. On a different form I have a control that opens a report in design view. However, this method gives the user the freedom to make any change he/she wants to the report layout, which I do not want them to be able to do. So, what I would like to happen is this; The user 1) Clicks on the desired report, then 2) A parameter window pops up asking for either the date of the job or the job number itself, then 3) The user selects the appropriate job or date, and 4) That information (job & date) is placed into a specific place (I am thinking maybe a text box) on the form, and finally 5) The report opens in preview mode ready for printing.

I hope this helps and does not convolute the issue.

 

[dhookom]

Why does the report open in design mode?

I generally create a form with a listbox of reports and various controls that can be used for filtering those reports. Users select a report, enter criteria (as desired), and click a button to display the report. This is all done on a single form. Code is used to build a where condition that is added to the DoCmd.OpenReport method.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

Thanks again. I am sure you are getting frustrated with me. I thought I had a handle on this Access thing, but apparently not. I have the various reports setup and I tried the listbox option, however, when a list item is selective, I am stumped as to how to get to the report I selected.

I had the reports open in design view because I am unable to get values I need, placed on the report.

I have consulted the help feature within the program, but to no avail.

 

[dhookom]

I generally create a table of reports in most MDBs.
[tt]
tblReports
==================
rptName rptTitle rptStatus rptDescription
....
[/tt]
I use this as the Row Source of a list box that displays the rptTitle but is bound to rptName. The rptName matches the report object name. Then my code is something like:

Dim strRptName as String
strRptName = Me.lboReport
DoCmd.OpenReport strRptName, ....

What do you want to place on the report that isn't in your data/records or a value from a control on a form?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

I have a table called tblJob Numbers that contains a job number and the date of that job. I generate one report in which the layout never changes, but the job number and the date will. When a user clicks on a button to generate the report, I need a popup window asking the user for a job number, and then have that number, along with the date associated with it, carried to the report. That job number and date has already been created from a control on another form. Hope this helps.

Thanks again.

 

[dhookom]

Doesn't the record source of the report contain the job number and date? The user "clicks on a button" so there must be a form involved. Add a combo box to the form that has all of the job numbers as its row source. The user can select a job number from the combo box and then open the report. Use the command button wizard to create the button and modify it like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboJobNumber) Then
    'assuming JobNumber is a numeric field
    strWhere = strWhere & "AND [JobNumber]=" & Me.cboJobNumber
End If
DoCmd.OpenReport "rptJobs", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

Thanks again. I can't seem to get that to work. I think perhaps I am not providing you with the proper information. I have a form that has multiple report command buttons. The report we have been working on has its record source in one table. The Job Numbers themselves, that I need the user to input to this report has its record in the table "tblJobNumbers" whose fields are JobNumber, and DistributionDate. When a user clicks this particular report button, I would like for an input window to popup asking for the Job Number. When the number is entered and the user clicks OK, then that Job Number and the Distribution Date is carried to the report. Then when the report is previewed or printed, then that Job Number and Distribution is shown.
Hope this helps

 

[dhookom]

Please provide additional information about the report's record source. Are you suggesting there is no JobNumber field in the report's record source?

Why can't you add tblJobNumbers to the record source of the report if it isn't already?

I provided a suggestion for adding a combo box to your existing form. Why didn't you do this? Or, why can't you do this?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

Duane - The reports record source is'qryRouteSheet1stofMonth-Phx". The record source for combo box 'Job Numbers' is tblJobNumbers. When I create the combo box on the report itself, there is no code for for me to edit. When I run the report, a parameter window pops up asking for the Job Number, which I enter, but it does not carry it to the report.

Why would I place a combo box on the form that I have the report options on, instead of on the report I want the data carried to?

I have a seperate form called 'Job Number Tracking' that users enter Job Numbers into the tblJob Number.

Thanks again.

 

[dhookom]

I'm not sure why you can't answer my questions. Maybe they aren't as clear as I expect.
1) is the job number field in the record source of the report?

2) Did you try place a combo box on your form?

3) My second reply to you suggested how to have a value from a form displayed in a report. That is why you want to put your combo box on the form and not the report.

4) What is the SQL view of is'qryRouteSheet1stofMonth-Phx?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

Hi Duane- To answer your questions: 1; The job number field is not in the record source of the report. It resides in its own table called 'tblJobNumbers'. 2; I placed the combo box on the same form as the report option, and that works fine, but the next step should be to 'click' a button that previews the report and it showing the selected job number on the report. 3; I understand about needing the combo box on the form. 4; The SQL of the 'qryRouteSheet_1stofMonth-Phoenix' is:
SELECT DISTINCTROW tblLocations_Phx.Loc_ID, tblLocations_Phx.[Main Zone], tblLocations_Phx.[Main Seq], tblLocations_Phx.Name, tblLocations_Phx.Address, tblLocations_Phx.City, tblLocations_Phx.Zip, tblLocations_Phx.Category, tblLocations_Phx.Phone, tblLocations_Phx.Hours, tblLocations_Phx.[X-Ref], tblLocations_Phx.[Apt Guide], tblLocations_Phx.[AZ Golfer], tblLocations_Phx.Fitness, tblLocations_Phx.[FIT RK], tblLocations_Phx.[AG(I)], tblLocations_Phx.[AZG(I)], tblLocations_Phx.[FIT(I)], tblLocations_Phx.Pays, tblLocations_Phx.Fuel, tblLocations_Phx.OSA
FROM tblLocations_Phx
GROUP BY tblLocations_Phx.Loc_ID, tblLocations_Phx.[Main Zone], tblLocations_Phx.[Main Seq], tblLocations_Phx.Name, tblLocations_Phx.Address, tblLocations_Phx.City, tblLocations_Phx.Zip, tblLocations_Phx.Category, tblLocations_Phx.Phone, tblLocations_Phx.Hours, tblLocations_Phx.[X-Ref], tblLocations_Phx.[Apt Guide], tblLocations_Phx.[AZ Golfer], tblLocations_Phx.Fitness, tblLocations_Phx.[FIT RK], tblLocations_Phx.[AG(I)], tblLocations_Phx.[AZG(I)], tblLocations_Phx.[FIT(I)], tblLocations_Phx.Pays, tblLocations_Phx.Fuel, tblLocations_Phx.OSA, tblLocations_Phx.Zip
HAVING (((tblLocations_Phx.[AG(I)]) Is Not Null)) OR (((tblLocations_Phx.[AZG(I)]) Is Not Null)) OR (((tblLocations_Phx.[FIT(I)]) Is Not Null))
ORDER BY tblLocations_Phx.[Main Zone], tblLocations_Phx.[Main Seq];

 

[dhookom]

You can add a text box to the report with a control source like:

=Forms!frmYourFrmName!cboYourComboName

I'm confused by you need this value on a report like this but that's my issue.

I'm also not sure why your query is a totals query where there are no aggregates like Sum() or Avg() or Count().

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

I added the combo to the form as you suggested. There are 6 report buttons for the user to choose from on the reports form. I chose to use 'command' buttons vs. list box/combo box. I amended the combobox code as you suggested. When I click on a report button, a window pops up asking for a job number(like I want it to), however, I now get 'Run-time error '3071'. Expression is either too complex or mistyped'. In the line 'DoCmd.OpenReport "rptJobs", acPreview, , strWhere' I changed the "rptJobs" to the report I want the number to carry. Since I have 6 report options, do I have to have a combo box specifying each report to open?

Thanks again.

 

[dhookom]

Why do you want the "window pops up asking for a job number" when this should already be available in the combo box.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JCMcAbee]

I think through all of this, our communications have been convoluted. I have a form which is called Job Tracking. Users will enter the following into this form; Job Number, Distribution Date, and products delivered. This information goes into tblJobNumbers.

I also have a form that has 6 different report buttons on it. The reports are all formatted for a particular product and each one has two text boxes. One box for Job Number and the other for Distribution Date. These reports are route sheets for my drivers to follow. These route sheets (reports)change very little from one use to the next. However, for tracking purposes, each time a route sheet (report) is generated, it is assigned a different job number and distribution date.

In my mind, I see that when a report is clicked, a window pops up either asking for a Job Number, if the user knows the number, or if a combo box pops up allowing the user to select the number from the tblJobNumbers. Any Job Number can be assigned to any report, but each Job Number can only be used once.

Once the user enters the job number, or selects it from a list, the Job Number and the Distribution Date needs to be carried to that particular report and placed into the two text boxes. The next step is for the user to preview the route sheet to ensure that the Job Number, Distribution Date, and Products are correct. If everything looks correct, then the route sheet is printed.

Now. I can get the Job Number, Distribution Date, and products into the tblJob Numbers. Where I believe I am confused is, How do I get the Job Number data from the table into the report?

I really hope this rather lengthy explaination helps us through this. Thank you.

 

[dhookom]

Apparently this process will also add a new record to tblJobNumbers or will it just select a record from the table? You mention getting values "into the tblJobNumbers" and also getting values "from the table into the report".

If you have the job numbers in a table and want to select a single record from the table, just do as I suggested and add a combo box on your form with a row source like:
SELECT [Job Number]
FROM [tblJob Numbers];
Then add tblJob Numbers to your report's record source and set the criteria under the Job Number to something like:
Forms![form that has 6 different report buttons on it]!cboJobNumber

This puts the jobnumber and other fields from tblJob Numbers in you report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]