Filter Form Open In Macro

[jgd12345]

Hi, I've just picked up vba to my horror when I realized that the specification was to open any forms using macros. I haven't ever used macros before but I can see that I have opened the form using the DoCmd.OpenForm command.

If anyone could show me how I could open the form with the correct filter using macros I'd be very greatful. Thanks

Here's my code:

Private Sub cmdFilter_Click()
  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim Result As Integer
  Dim rstCheck As ADODB.Recordset
  Dim strSQL As String

  Rem Declaring the formname
  stDocName = "frmHouses"

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

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

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

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

  strSQL = "SELECT * FROM HOUSES WHERE " & stLinkCriteria
  Set rstCheck = New ADODB.Recordset
  rstCheck.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

  If rstCheck.EOF Then
    MsgBox ("No records found for selected criteria.")
  Else
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  End If

  rstCheck.Close
  Set rstCheck = Nothing
End Sub

 

[Calvin]

you need to move/copy your code to the target forms OnOpen or OnLoad events and then set the forms RecordSource property equal to the SQL statment and you should be golden

Private Sub cmdFilter_Click()
  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim Result As Integer
  Dim rstCheck As ADODB.Recordset
  Dim strSQL As String

  Rem Declaring the formname
  stDocName = "frmHouses"

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

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

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

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

  strSQL = "SELECT * FROM HOUSES WHERE " & stLinkCriteria
  Set rstCheck = New ADODB.Recordset
  rstCheck.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

  If rstCheck.EOF Then
    MsgBox ("No records found for selected criteria.")
  Else
    [COLOR=Red]DoCmd.OpenForm stDocName[/COLOR]
  End If

  rstCheck.Close
  Set rstCheck = Nothing
End Sub

[COLOR=Green]'*********************************
'OnOpen event for frmHouses
'you will need to change/correct where the fields get there source data
'I inseted "Forms![frmMain]" in place of wher you had "Me" to reflect the original form[/COLOR][COLOR=Navy]
Private Sub Form_Open(Cancel As Integer)

  Dim stDocName As String
  Dim stLinkCriteria As String  
  Dim Result As Integer
  Dim rstCheck As ADODB.Recordset
  Dim strSQL As String

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

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

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

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

  strSQL = "SELECT * FROM HOUSES WHERE " & stLinkCriteria

   [b]Me.RecordSource = strSQL[/b]

End Sub[/COLOR]

Regardless of whether you open form using code or macro your query criteria should pull from the original form

 

[jgd12345]

Hi, yeah that's a good idea. I did everything you said and changed frmMain to frmFilter because that's the name of the annitial form the values are taken from but it returns an error because the values are returning blank so something's wrong with Forms![frmFilter]![txtMaximumPrice] and the others. I'd be greatful if you could help. Cheers

 

[jgd12345]

So no idea, I really need to get this done. Please

 

[Calvin]

Sorry, for the delay, just enjoying the weekend.

Hmm... Not sure what the problem would be, can you export the two forms and related table to another MDB and upload it so I may have a look?

 

[Calvin]

you may also try replacing Forms![frmFilter]![txtMaximumPrice] with Forms![frmFilter].txtMaximumPrice since your refering to a control and not a memory reference.