Placement of Code in a Form that Opens a 2nd Form for Collecting Parameters

[whdyck]

I'm using MS Access 2003.

I have a tabular form that simply runs a query and displays data. Before the query fires, I want to display a parameter-collection form so the user can filter the results before they appear. The SQL would grab the parameters from the open parameter-collection form before firing. Question: Where do I place the code that opens the parameter-collection form?

I've tried to add the code to the Open event, but apparently it fires after the SQL executes. I've also tried to add it to the Activate event and the Before Query event, but no luck.

BTW, I've successfully done something similar for reports: if I place my VBA code that opens the parameter form in the Open event of the report, it works. But forms must be different animals.

In case it helps, here's my VBA code:

  ' Set public variable to true to indicate that the report is in the Open event
  GblnInReportOpenEvent = True
 
  ' Make Switchboard invisible
  Forms!frmSwitchboard.Visible = False
  DoEvents
 
  ' Open Dialog to collect parms
  DoCmd.OpenForm "frmRptFinTxSummaryByPortTypeClaimTypeParms", , , , , acDialog
 
  ' Cancel Report if User Clicked the Cancel Button
  If fIsLoaded("frmRptFinTxSummaryByPortTypeClaimTypeParms") = False Then
    Cancel = True
  Else
    DoCmd.Maximize
  End If
 
  ' Set public variable to false to indicate that the Open event is completed
  GblnInReportOpenEvent = False

Thanks for any help you can give.

Wayne

 

[josephff]

i would suggest creating a form that has your filter settings, maybe even show a datasheet there for viewing.

you would need to open your report and set the record source in vba via a button or similiar.

heres a snippet from a code i use:

DoCmd.OpenReport "Group1", acViewDesign, , , acHidden
Dim year As String
year = Nz(DLookup("CurrentYear", "CurrentVariables"))
 
Dim mySQL As String
Dim sql As String
If Forms![Reports Menu].Group10Check = True Then
    sql = "UPDATE CurrentVariables SET CurrentVariables.ReportGroup =  10"
 
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True
 
mySQL = "SELECT ProductGroup, PartNumber, Description, FleetPrice, Actual, Shown, Difference, PriceDifference FROM [" & year & "] WHERE ProductGroup=10 AND Shown <> Null;"
 
Reports("Group1").RecordSource = mySQL
 
 
 
'Opens print dialog for current screen (report in this case):
On Error GoTo 10
DoCmd.OpenReport "Group1", acViewNormal
DoCmd.Close acReport, "Group1", acSaveYes

so this one will grab data after a button has been pressed then programatticaly change the record source of that report.


Now i also have a form that shows training records and they can be filtered in real time using either employee name or training title. The subform is bound to a table therefore when i filter it on my form all i need to do is print and it will only print out what is filtered

    If IsNull(Me.EmployeeNameTxt.Value) And IsNull(Me.TrainingTitleTxt.Value) Then
        Exit Sub
    End If
 
        DoCmd.OpenReport "Training View", acViewDesign, , , acHidden
 
        DoCmd.OpenReport "Training View", acViewNormal
 
        DoCmd.Close acReport, "Training View", acSaveYes
 
        Me.TrainingSub.Requery
 
        Me.EmployeeNameTxt.Value = Null
        Me.TrainingTitleTxt.Value = Null
        Me.EmployeeNumberTxt.Value = Null
 
        Me.TrainingSub.Form.RecordSource = ""

and here is the filter i use on my training view

Me.TrainingTitleTxt.SetFocus
    If IsNull(Me.TrainingTitleTxt.Value) Then
        Me.TrainingSub.Form.RecordSource = "Select * From Training Where EmployeeNumber = '" & Me.EmployeeNumberTxt.Value & "'"
    ElseIf IsNull(Me.EmployeeNameTxt.Value) Then
        Me.TrainingSub.Form.RecordSource = "Select * From Training Where ItemName = '" & Me.TrainingTitleTxt.Text & "' AND BranchNumber = '" & Me.Branch.Caption & "'"
    Else
        Me.TrainingSub.Form.RecordSource = "Select * From Training Where EmployeeNumber = '" & Me.EmployeeNumberTxt.Value & "' AND ItemName = '" & Me.TrainingTitleTxt.Text & "'"
    End If

hope this helps