VBA for clearing cbo selection and auto requerying the subform

[redekopp]

Hello, I was hoping someone could help me write the proper VBA for a clear selection button im using for a report. The button is on the form and the report is located in the subform (called DS). the combo box is called cbo.ModUnit . As of now I have it working right that if I change the selection in the dropdown the report requery's properly with the new data by using tempvar. Im trying to make my button tell it that if its 0 or null then to reset back to the default report. The button works in changing the cbo to null. I just need the cbo to respond with the appropriate on change event.
This is what I have so far.

Private Sub cboModUnit_Change()
If Nz(cboModUnit.Value, 0) = 0 Then
    TempVars.Add "tmpModUnit", Me.cboModUnit.Value
    Me.DS.Requery
Else
    TempVars.Add "tmpModUnit", Me.cboModUnit.Value
    Me.DS.Requery
End If

End Sub

I realize this is probably completely wrong, and the If statement needs to tell it to make tmpModUnit default instead, not the value. Or maybe I really don't know what im talking about haha.

Any help appreciated thanks guys!

[June7]

You have a report in ReportView as a sub on a form? Why a report object and not a form? What else is on the main form?

The default report would be unfiltered?

I know about them but never used TempVars. How does tmpModUnit filter the report? Is it a parameter in a query used as report RecordSource? I don't use dynamic parameterized queries. I prefer to set Filter and FilterOn properties.

Post the report RecordSource SQL statement.

I would use AfterUpdate instead of Change event.

[redekopp]

I am not sure what filter and filteron properties do. Im still very new to access so I am doing what a I basically know thus far.
Below is a picture of the screen im working on. afterupdate is already in use on the combo box for when a selection is made in the drop down. the selection filters the report and requeries it using the tempvar. heres the sql code for the rcrd source

SELECT TblModUnits.*, TblShiftDetail.WorkCodeID, TblWorkCodes.WorkCodeDesc, TblShiftDetail.RegHours, TblShiftDetail.OTHours, TblShift.ShiftDate
FROM ((TblModUnits RIGHT JOIN TblShiftDetail ON TblModUnits.ModUnitSerialNum = TblShiftDetail.ModUnitSerialNum) RIGHT JOIN TblShift ON TblShiftDetail.TimeID = TblShift.TimeID) LEFT JOIN TblWorkCodes ON TblShiftDetail.WorkCodeID = TblWorkCodes.WorkCodeID
WHERE (((TblModUnits.ModUnitSerialNum) Like [TempVars]![tmpModUnit] & "*"))
ORDER BY TblModUnits.ModUnitSerialNum;

it works great and it does exactly what I want. My clear selection button works good to it is this:

Private Sub Command86_Click()
Me.cboModUnit.Value = Null
End Sub

it sets it null and that's what I want. So now what I need is to tell the combobox to go back to default if the value is null. that's all I need but I don't know how to type that out properly.
the combobox is this currently when its working

Private Sub cboModUnit_AfterUpdate()
TempVars.Add "tmpModUnit", Me.cboModUnit.Value
Me.DS.Requery
End Sub

So what do I add where to adjust it back to all data when the combobox is null?

[June7]

As far as I can tell, that should pass either Null or empty string and the query should then return all records because Null or empty string concatenated with "*" will return "*" and the query should do wildcard match.

However, if any records have a Null in that field, those records will not be retrieved.

Example of what I prefer to do: http://www.allenbrowne.com/ser-62.html

[redekopp]

so are you saying my code should be right and I haven't done anything wrong??

I see what your saying you do, and ive done that exact same thing for normal searches. But not for when im doing these reports. ive spent a lot of time setting it up this way I would rather just figure out what the vba code would be to reset the recordsource

[June7]

As far as I can see, the query and code are fine. Try it.

[redekopp]

Update: I got it to work, if you were curious this is the code I used for my clear selection button:

Private Sub Command86_Click()
Me.cboModUnit.Value = Null
TempVars.Add "tmpmodunit", Null
Me.DS.Requery
End Sub

Thanks for the help though!

[ItsMe]

I see you have managed to clear the TempVar. I was going to suggest using the Remove method. Although, I have never worked with TempVars so I don't know for sure. I suggest you remove the On Change event of the combo. Maybe include something like the following in your AfterUpdate of the combo.

If Isnull(Me.cboModUnit.Value) or Me.cboModUnit.Value  = "" Then
    TempVars.Remove "tmpModUnit"
    Me.DS.Requery
Else
    TempVars.Add "tmpModUnit", Me.cboModUnit.Value
    Me.DS.Requery
End If