Making selections show up in a report

[SheepDog]

I have a form with a subform. In the subform the selections are based off a combo box and an active X calender as the start date.
If I have some with a start date of 10/3/2003 and 10/6/2003 they both show up in the subform, but when you run the query for the report only one the 10/3/2003 shows up in the report instead of two. The combo box is based off the field of Office Held. So if you select ALREM in combo box and select October 3, 2003 two people are in come back in the subform yet the the second person has a start date of 10/6/2003 and will not show up in the report.

What I am trying to say I how can I get all the people that show up in the sub form to be listed in the report when you preview it?

 

[scriverb]

Hi Bill: Why don't you post the RecordSource for the Report. Is it a query or a table. If it is a table then please post the code that creates the criteria expression that is used when you print the report. Someway the criteria is different for the subform that it is for the report. I just need to see how they are being built and what they look like when finished.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SheepDog]

Here is the recordsource: VolunteerInt (This is a query)
Here is the SQL for the query;

SELECT DISTINCT dbo_alumni_recruiters.PEOPLE_CODE_ID, dbo_VOLUNTEERINTEREST.START_DATE, dbo_VOLUNTEERINTEREST.END_DATE, dbo_VOLUNTEERINTEREST.OFFICE_HELD, dbo_alumni_recruiters.FIRST_NAME, dbo_alumni_recruiters.LAST_NAME, dbo_alumni_recruiters.ADDRESS_LINE_1, dbo_alumni_recruiters.ADDRESS_LINE_2, dbo_alumni_recruiters.CITY, dbo_alumni_recruiters.STATE, dbo_alumni_recruiters.COUNTRY, dbo_alumni_recruiters.ZIP_CODE, dbo_alumni_recruiters.DAY_PHONE, dbo_alumni_recruiters.EVENING_PHONE, dbo_alumni_recruiters.EMAIL_ADDRESS
FROM dbo_alumni_recruiters RIGHT JOIN dbo_VOLUNTEERINTEREST ON dbo_alumni_recruiters.PEOPLE_CODE_ID = dbo_VOLUNTEERINTEREST.PEOPLE_ORG_CODE_ID
WHERE (((dbo_VOLUNTEERINTEREST.START_DATE)=[forms]![dbo_CODE_OFFICEHELD].[Cal1]) AND ((dbo_VOLUNTEERINTEREST.END_DATE) Is Null) AND ((dbo_VOLUNTEERINTEREST.OFFICE_HELD)=[forms]![dbo_CODE_OFFICEHELD].[Combo2]))
ORDER BY dbo_alumni_recruiters.LAST_NAME;

 

[scriverb]

Simple fix here is to use the same query SQL as the RecordSource for the Report. Or at least use the same WHERE clause code.

Is the RecordSource for the Report different from the subform SQL? If so, either do what I said above or post it so we may take a look at it.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SheepDog]

Yes the recordsourceis different from the Report, it is dbo_alumni_recruiters.

 

[scriverb]

Try using the same type of references to your form controls in that query. If the tables are the same then why not just use the same query as you are using in the subform.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SheepDog]

The recordsource for the report is VolunteerInt which is a query using tables dbo_VolunteerInterest and dbo_alumni_recruiter the subform
has the recordsource dbo_alumni_recruiter which is a table. So you see one uses a query and the other uses a table.

 

[scriverb]

How about this WHERE statement:

WHERE (((dbo_VOLUNTEERINTEREST.START_DATE)[b][red]>[/red][/b] = [forms]![dbo_CODE_OFFICEHELD].[Cal1]) AND ((dbo_VOLUNTEERINTEREST.END_DATE) Is Null) AND ((dbo_VOLUNTEERINTEREST.OFFICE_HELD) = [forms]![dbo_CODE_OFFICEHELD].[Combo2]))

I have added a greater than sign to compliment the equals. If we left at equals you would only get the first day. If you want all days equal to and beyond then the >= should be used.

Post back if this works.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SheepDog]

Yes the report now shows just on or two depending on what you have selected as the start date.

 

[scriverb]

Bill, are you saying that you are not now seeing all the records that you need to. If so, then the only other difference in the two queries WHERE clause has to do with whether the EndDate is null:

dbo_VOLUNTEERINTEREST.END_DATE) Is Null

Could this be paring down the record list that you are expecting?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SheepDog]

Everything works fine. My problem now is that if I make no seleciton from the calendar or combo box and click on the preview report I am getting to records to show up.

How can I have nothing show up if there is not a selection that is made?

 

[scriverb]

Easiest way to fix this is to check for no selections by the user at the form level before you open the report.

In your code to open the report only execute that code if there is the appropriate selections made in your forms controls. Something like the following:

If Not IsNull(Me![cal1]) and Not IsNull(me![Combo2]) then
    DoCmd.OpenReport "yourreportname"
Else
    MsgBox "You must make selections"
End if

Let me know what you think here.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SheepDog]

Here is the code for the open event of the report:

Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me![Cal1]) And Not IsNull(Me![Combo2]) Then
DoCmd.OpenReport "VolunteerInt"
Else
MsgBox "You must make selections"
End If
End Sub

Here is the error I get:
Miscrosoft Office Access can't find the field Cal1 referred to in your expression.

 

[SheepDog]

I have taken out the code above and it works as it should.
Now the problem I am having is that the report will list one record with just the office held field and all other fields blank. and then other records that have the same office held field.

 

[scriverb]

The code I provided should go in a Command Button on the form dbo_CODE_OFFICEHELD. This is where you are making the selections and the OnClick event of a command button is probably where you are calling for the printing of the report. This code will ensure that they have made a selection before the report is event called.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]