Filter Form On Open Event

[jgd12345]

Hi, I need todo a filter form to filter out specific records. I originally did it with using vba on the on click event inside the filter form but it now seems that I have to open the form using macros. I simply did an onclick event to run the specific macro to open the form. Then this has an on open event which is the code below. I think it's coded ok It's just that the Forms![frmFilter]![txtMinimumPrice] and the others are returning blank. i'd be greatful if anyone could tell me what I've done wrong. Thanks

Private Sub Form_Open(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim strSQL As String

Rem Filtering Minimum Price
If IsNumeric(txtMinimumPrice) = True Then
stLinkCriteria = "[H_PRICE] >= " & Forms![frmFilter]![txtMinimumPrice]
Else
Rem Added to avoid a possible error
stLinkCriteria = "[H_PRICE] >= 0 "
End If

Rem Filtering Maximum Price
If IsNumeric(frmFilter.txtMaximumPrice) = True Then
stLinkCriteria = stLinkCriteria & " and [H_PRICE] <= " & Forms![frmFilter]![txtMaximumPrice]
End If

Rem Filtering Region
If IsNull(cboRegion) = False Then
stLinkCriteria = stLinkCriteria & " and [H_REGION] = " & "'" & Forms![frmFilter]![cboRegion] & "'"
End If

Rem Filtering number of Rooms
If IsNumeric(txtRooms) = True Then
stLinkCriteria = stLinkCriteria & " and [H_BEDS] = " & Forms![frmFilter]![txtRooms]
End If

strSQL = "SELECT * FROM HOUSES WHERE " & stLinkCriteria

Me.RecordSource = strSQL
End Sub

 

[PHV]

When the Open event is triggered you have no chance to get accurate value from controls inside this form.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884