forms parameters

I would like to design a form which supplies criteria to open another form.

I want to be able to edit data using a form but I do not want to display all
the records when i open the form. I went in the SQL query builder in the form
properties and created parameters and it worked but the only problem was - if
I don't supply the exact criteria (contract number in this case) the form
draws a blank. To circumvent this problem I would like to be able to select
the criteria from a combo box rather than type it.

Please help!!

youngturk1968

The basic strategy is to include an invisible textbox control on the form
where you can build an SQL filter string, and then use the string as a
parameter to the OpenForm method. A similar strategy can be used to filter a
report. I find it most convenient to name each combo box control by the name
of corresponding field with a three-character prefix, i.e., cbo for a combo
box. Then you can use the name of the control to generate the field name
required for the SQL string.

The procedure is called in the AfterUpdate event procedure of each combo box.

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize invisible SQL-string control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If ctl.ControlType = acComboBox Then

If (Nz(ctl.Value) 0 And Nz(ctl.Value) "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) - 3)) _
& "]=" & ctl.Value & " AND "
End If

End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Then a command button opens the form using the filter string:

Private Sub YourCommandButton_Click()
Dim strDocName As String
Dim strFilter As String

strDocName = "YourForm"
strFilter = ""

' If no criteria is selected, open with no filter
If IsNull(Me!txtFilterString) Then
DoCmd.OpenForm strDocName, acNormal
Else
strFilter = Me!txtFilterString
DoCmd.OpenForm strDocName, acNormal, , strFilter
End If

End Sub

Where a combo box is not practical, you can use a textbox, and match the
entered value as a wildcard using the Like operator. It would then be more
convenient to use a Select Case statement

Select Case ctl.ControlType
Case acComboBox

If (Nz(ctl.Value) 0 And Nz(ctl.Value) "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) - 3)) _
& "]=" & ctl.Value & " AND "
End If

Case acTextBox
If (Nz(ctl.Value) 0 And Nz(ctl.Value) "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) - 3)) _
& "] Like " & "'" & "*" & ctl.Value & "*" & "'" & " AND "
End If
End Select

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "

Hope that helps.
Sprinks

"youngturk1968" wrote:

I would like to design a form which supplies criteria to open another form.

I want to be able to edit data using a form but I do not want to display all
the records when i open the form. I went in the SQL query builder in the form
properties and created parameters and it worked but the only problem was - if
I don't supply the exact criteria (contract number in this case) the form
draws a blank. To circumvent this problem I would like to be able to select
the criteria from a combo box rather than type it.

Please help!!

youngturk1968