How to setup a filtering form to run or export reports?

[knifey]

Hi,
I've created a simple form with 3 validation lists bound to 3 different fields in one table, a print button and an export button. This is backed up by some VBA to build the SQL strings WHERE clauses from the options the user chooses on the filtering form. The idea is to be able to export or print all the reports for e.g. admin staff or secretaries at one button press.
My problem is the filtering form does not work correctly and is deleteing or changing field values in the table instead of just displaying them in the form. I suspect this may be due to the form setup, but can't be sure. Does this method need a bound or unbound form? and how do the property values need to be setup?
I'm very new to Access but do have some experience coding in Excel VBA.
Any advice or pointers in the right direction would be much appreciated.
Kind regards,
Knifey

 

[MajP]

Needs to be unbound controls. Yours are obviously bound.

 

[knifey]

thanks majp

 

[knifey]

Hello again,
I've now designed my 'unbound' filter form and the VBA behind it to filter the results.
Everything seems to be working now upto the point when I preview the report. Nothing happens, it just tries to open the report without success (and without giving an error message) then moves to the VBA that automatically closes the report. Here is my code:

front end class module:
Private Sub OpenReport_Click()
On Error GoTo Err_OpenReport_Click

Dim stDocName As String

Select Case Me!ReportSelector
Case 1
stDocName = "Rep 10 Mandatory Training Report (personal)"
DoCmd.Close acForm, stDocName, acSaveYes
DoCmd.OpenReport stDocName, acPreview
End Select

Exit_OpenReport_Click:
Exit Sub

Err_OpenReport_Click:
Resume Next
End Sub

reports class module:
Private Sub Report_Close()
DoCmd.Close acForm, "frm_Exporting Form With Filters"
End Sub

Private Sub Report_Open(Cancel As Integer)
If (IsNull(Me.OpenArgs)) Then
DoCmd.OpenForm "frm_Exporting Form With Filters", , , , , acDialog, Me.NAME
DoCmd.Close acForm, "frm_Exporting Form With Filters"
End If

End Sub

filtering forms class module:
Private Sub Form_Close()
DoCmd.CancelEvent
End Sub

Private Sub Preview_Click()
Dim sqlFilter As String
Dim ReportName As String

ReportName = Me.OpenArgs

sqlFilter = ""

If (Me.cnt_EmployeeName.Value <> "") Then sqlFilter = " AND [Rpt10 Mandatory Training Report Data].[Complete_Name]=""" & Trim(Me.cnt_EmployeeName.Value) & """"
If (Me.cnt_Area.Value <> "") Then sqlFilter = sqlFilter & " AND [Rpt10 Mandatory Training Report Data].[Area]=""" & Trim(Me.cnt_Area.Value) & """"
If (Me.cnt_Role.Value <> "") Then sqlFilter = sqlFilter & " AND [Rpt10 Mandatory Training Report Data].[Role]=""" & Trim(Me.cnt_Role.Value) & """"

If (sqlFilter <> "") Then
sqlFilter = Right(sqlFilter, Len(sqlFilter) - 4)
Reports(ReportName).RecordSource = "SELECT * FROM [Rpt10 Mandatory Training Report Data] WHERE " & sqlFilter
Else
Reports(ReportName).RecordSource = "SELECT * FROM [Rpt10 Mandatory Training Report Data]"
End If
Me.Visible = False
End Sub

After the End Sub above it should display 'Rpt10 Mandatory Training Report' with the filters applied. Rpt10 Mandatory Training Report Data is a query that contains all of the data.
Can anyone see what I'm doing wrong? Or have I left out somthing straightforward?
I'm a complete beginner at Access so any advice or pointers in the right direction would be much appreciated.
Kind regards,
Knifey