Filter Records - None Found

[jgd12345]

Hi, sorry to bring this back up but I felt due to the high number of replies the post was getting ignored. Here's my problem. The code below is what I use to filter a form. A quick scan over makes it pretty simple to understand what's going on. When it filters the form it runs the command DoCmd.OpenForm stDocName, , , stLinkCriteria. I only want it to run that command if it finds a records otherwise it displays a msgbox saying "Not Found".

I'd be greatful if anyone could help. Thanks

Private Sub cmdFilter_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim Result As Integer

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

DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[OnTheFly]

This is how I would do it.

Add these to your variables:

Dim rstCheck as ADODB.Recordset
Dim strSQL as String

Then after you build stLinkCriteria do this:

strSQL="SELECT * FROM YourTableName 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



Hope this helps.

OnTheFly

 

[jgd12345]

Hi, it went to this line on the debugger "Dim rstCheck As ADODB.Recordset" and said user defined type as not defined.

 

[OnTheFly]

Are you using Access97 or Access2000?

If you are using 97 then try this instead

Dim DB as Database
Dim rstCheck as Recordset
Dim strSQL as String

Then after you build stLinkCriteria do this:

strSQL="SELECT * FROM YourTableName WHERE " & stLinkCriteria
Set DB=CurrentDB()
Set rstCheck=DB.OpenDatabase(strSQL,DBOpenSnapshot)

If rstCheck.EOF then
Msgbox "No records found for selected criteria."
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

rstCheck.Close
Set rstCheck=Nothing
DB.Close
Set DB=Nothing



Hope this helps.

OnTheFly

 

[jgd12345]

Nope am using Access 2000. Here's my ammended code which returns the error:

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

 

[OnTheFly]

I would check your referances and see if one is MISSING: or if the ADO (ActiveX Data Object Library) reference is not there at all. That is thoe only tiem I have ever gotten an error like the one you are describing.

Hope this helps.

OnTheFly

 

[Golom]

You need a reference to the ADODB library to get rid of the "...user defined type as not defined..." message. In your code I think that you are missing spaces before the "and" conditions that you are concatenating.

 

[jgd12345]

Hi, thanks for your support so far. I found how togo to the references by going viewing the code and clicking tools - references. Then there's a big list of things and checkboxes. I tried finding something saying ADODB but there was nothing. I know this probably sounds quite easy to you but I've never had to use this before, if you could help i'd be greatful. Thanks

 

[Golom]

You need to find

Microsoft ActiveX Data Objects x.x Library

where "x.x" will be values like "2.1" depending on the release that you have installed.

 

[jgd12345]

Cheers it worked. Thanks