open selection criteria form before record source query

[Guest]

I have a form that allows the users to edit records in a table. I would like
to add the ability to select a subset of records, based on one column of the
table. I created a new form with a combo box which lists all the possible
options of the column to be selected on, which is the bound column. I open
the selection criteria form from the Open event of the main form, and select
on the vaslue of the bound column in the Record Source of the main form. I
want to do it this way so that the selection criteria form will be displayed
regardless of how the main form is opened.

My problem: When I open the main form, the selection criteria in the Record
Source query is treated as a prompt, before the selection criteria form is
displayed. Is there any way to get the main form to display the selection
criteria form before it runs the record source query?

 

[Guest]

This is possible. As with anything, there are multiple ways to do it. I
will give you one.

I assume you are using a form as a dialog box that will allow the user to
pick this criteria. The first thing I usually do is make the OK and Cancel
buttons on that form hide the form rather than close it. Use the
me.visible=false statement.

On the parent form, copy the code out of the record source and put it in the
code of the load event. Delete it from the record source property and set it
with code. In other words, build the SQL dynamically. Here is a sample that
you can modify.

Private Sub Form_Load()
Dim strCriteria As String 'Variable to store results of criteria form
Dim strSQL As String 'Variable to store recordsource

'This line opens the dialog box in a mode that stops the code execution
DoCmd.OpenForm "dlgLocationSelection", , , , , acDialog


strCriteria = Nz(Forms.dlgLocationSelection.cboCriteria, "0")
If strCriteria = "0" Then
Exit Sub
Else
'Set the SQL statement including the criteria in the where condition
strSQL = "Select * "
strSQL = strSQL & "From authors "
strSQL = strSQL & "where Author='" & strCriteria & "'"

Me.RecordSource = strSQL
Me.Requery
End If
DoCmd.Close acForm, "dlgLocationSelection",acSaveNo

End Sub

Hope this helps.

 

[Guest]

Thanks Scott. I'll give it a try.

A follow-up question: I've successfully used the method I described in a
report. That is, in the Open event of the report, I open a form (the same
form I'm attempting to use in the main form) which allows the user to specify
selection criteria, which I then use as selection criteria in the Record
Source query. Why does this method work in a report but not in a form?