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

 

[JCMcAbee]

This is what my form (frmRoute Sheet Operations)looks like:

Phoenix Route Sheet (this is a label)
Preview Route Sheet (this is a command button)

I have 5 of these groups on this form, but lets work on this one.

This is how I envision this process to work:

I click on 'Preview Route Sheet' and a list of available Job Numbers come up. I then click on a Job Number and the report is opened in preview mode displaying the Job Number that I selected and the date associated with it. If all is well, then I print the report.

As you suggested, this is what I have done so far :
1) On the form (frmRoute Sheet Operations)I created an unbound combo box (cboJobNumbers) with the row source of
'SELECT [Job Number] FROM [tblJobNumbers];' When click on the combo box the drop down appears blank.
2) I added 'tblJob Numbers' to the reports record source (qryRouteSheet_1stofMonth-Phx). This query's record source is 'tblLocations_Phx. I added the Job Number field to the query with the criteria 'Forms![frmRoute Sheet Operations]!cboJobNumber'. I also added the Distribution Date field to the query.
3) I added one text box to the report with the control source of Job Number and and another text box with the control source Distribution Date.

Here is what happens when I test:
I click on the combo box to select a job number, but there are no job numbers to select. I then click the "Preview' button and each record from the query has quadrupled itself. So my record count goes from the expected 1300 to about 5800. However, a Date is the Date text box and a Job Number is in the Job Number text box. Unfortunately the date and number is randomly selected from the tblJobNunbers (remember I was not able to select a job number).

When I change the combo box row source to 'tblJobNumbers', the drop drop down has all available job numbers for me to select. Unfortunately, when I preview the report, the Date and Number that I selected is not carried to the report. It still picks a random number.

None the less, this is a HUGE improvement and we have made great progress. So how do I prevent the records from being quadrupled and how do I get the selected Date and Number cariied to the form?

Thanks very much Duane.

 

[dhookom]

Are there duplicate Job Numbers in [tblJob Numbers] (I'm not sure of the spelling since you and I have at least 3 versions)?

If there are duplicate Job Numbers then you need to identify the single record from the table.


Also, I don't think you ever answered my question about why your report's record source is a totals query and not a regular select query.


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 definitely do not have duplicates. When I select a job number from the combo box (the correct source is tbljobnumbers), it does NOT carry the selected number to the report. Rather, it places a random one on the report.

To answer your question about the query: The query gets its records from 'tblLocations_Phx'. The table has approximately 2500 records which has various information about each location. The report that we are working on pulls about 1300 of these records, so I set the criteria to give me only those records that have specific products for those locations.

I thought I created a select query, 'filtering' only those records that meet the criteria. I have totals being reported on the report footer. So, the query is giving me the correct information, and I am able to get totals from that query.

Should I have done the query and the report differently? I have limited experience in Access, so any pointers I receive are very much appreciated.

So back to the problem...a job number and the distribution date are being carried to the report, but a random one is being placed, and no the one I selected from the combo.

Thanks again.

 

[dhookom]

What is your current record source SQL for your report?

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]

SQL View for report record source 'qryRouteSheet_1stofMonth-Phx':
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, tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]
FROM tblLocations_Phx, tblJobNumbers
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, tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]
HAVING (((tblLocations_Phx.[AG(I)]) Is Not Null) And ((tblJobNumbers.[Job Number])=Forms![frmRoute Sheet Operations]!cboJobNumber)) 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 had a lot of ORs that might have been allowing more than one JobNumber.
Try:
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, tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]
FROM tblLocations_Phx, tblJobNumbers
WHERE tblJobNumbers.[Job Number]=Forms![frmRoute Sheet Operations]!cboJobNumber
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, tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]
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];

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 did that and got the error: Extra ) in expression in HAVING tblLocations_Phx.[AG(I)]) Is Not Null Or tblLocations_Phx.[AZG(I)] Is Not Null Or tblLocations_Phx.[FIT(I)]) Is Not Null

So I took the extras out and ran it. The result was that no records were showing. Blank query.

 

[dhookom]

Are you sure your combo box has a correct value of a job number? What happens if you remove the Forms!... from the criteria and create a new column expression

ControlValue: Forms![frmRoute Sheet Operations]!cboJobNumber

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]

The value I select from the combo box is a record from the tblJobNumbers, if that is what you are asking. I deleted the original column from the source query, created a new column using the expression you suggested (I did not include the words 'ControlValue:' in the expression). Here is what happens now:

When I click 'Preview Report', without using the combo box (blank), the report is pulling a job number from the table, however, each page of the report has a different job number. It is cycling through the records from tblJobNumbers. Conversely, when I do select a job number from the combo box, it does the same thing; cycles through all records in the table.
Thanks.

 

[dhookom]

What is the Record Source of your report (sql view)?

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]

SQL View:
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, tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]
FROM tblLocations_Phx, tblJobNumbers
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, tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]
HAVING (((tblLocations_Phx.[AG(I)]) Is Not Null) And ((tblJobNumbers.[Job Number])=Forms![frmRoute Sheet Operations]!cboJobNumber)) 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]

I think your ANDs and ORs are wrong and should be in the HAVING clause. I also don't understand why you are using a GROUP BY query.

Try:

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, 
tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]

FROM tblLocations_Phx, tblJobNumbers

WHERE tblJobNumbers.[Job Number]=Forms![frmRoute Sheet Operations]!cboJobNumber AND 
(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];

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]

When I originally designed the query, I believe that is what I did to get it to run correctly.

When I pasted your code over the existing sql and ran the query, it was blank. No records were pulled. No error codes.

 

[dhookom]

Try set up the query SQL exactly as so that you can view the value that is currently being stored/selected in the control on the form. Does the rightmost column match any job number?

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, 
tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date],Forms![frmRoute Sheet Operations]!cboJobNumber As FrmCtlJobNumber

FROM tblLocations_Phx, tblJobNumbers

WHERE (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];

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 ran the query. The rightmost column, FrmCtlJobNumber, is blank. However, the Job Number & Distribution Date columns for each record are populated with alternating numbers and dates. Which, in essence, should be the same number for each record. At no point was I prompted for a Job Number. So all Job Numbers were populated into the query.

 

[dhookom]

If the rightmost column is blank then you don't have any value selected in the cboJobNumber control. This would also suggest that when using the cboJobNumber in your criteria you won't return any records. Your form must be open and your combo box must contain a value.

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]

Makes sense. However, I opened frmRoute Sheet Operations, populated cboJobNumbers with a value from the tblJobNumbers, and previewed the report. Job numbers and distribution date are carrying to the report, but once again, not only is the one I selected carrying over, but all values from the table are carrying over. Consequently, each page of the report has a differrent number.

 

[dhookom]

Open a new blank query and enter

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, 
tblJobNumbers.[Job Number], tblJobNumbers.[Distribution Date]

FROM tblLocations_Phx, tblJobNumbers

WHERE tblJobNumbers.[Job Number]=Forms![frmRoute Sheet Operations]!cboJobNumber AND 
(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];

Open your form and select a job number from the combo box. When you view the datasheet of the new query, you should only see the one [Job Number] value in the [Job Number] column.

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]

We are very close, but still a problem. I created a new query and pasted the code. Opened the form and selected a job number from the combo box, leaving the form open. Went back to the object query and ran the datasheet view and it was blank. So, I closed the form, ran the object query and it prompted me for a job number, which I entered. Worked great! So, I closed the query and previewed the report from the object reports , whereby I was again prompted for a job number, which I entered. Worked great! As long as I do not have the form open, and manually execute the query or the report, entering a job number when prompted, it works great. So, as a guess, the problem is either with the form, the combo box, or both. Is there an easy fix, or will I be forced to recreate the form and the box? Thanks

 

[dhookom]

What are these properties of the combo box:

Name:
Row Source:
Column Count:
Bound Column:
Column Widths:
Control 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]