Row count from RecordSource

[foxy]

Hi,

I have an unbound for that the user enters search criteria into and clicks on a search command. This command then runs some VBA which opens the second form with a SQL statement RecordSource based on the search criteria. The RecordSource is set in the VBA of the first form.

I want to be able to display a message saying that the search returned no results if that is the case. I am trying to do this by setting the 'Visible' propert of different labels to true or false based on whether the ID field of the row source is null or not, but this doesnt seem to be working.

Is there a way that I can count the rows retuned on the record source of the form in order to set the visible properties this way?

Cheers

Foxy

 

[Scooterbug]

A simple loop can do that. I actual did this for someone else on the forums to check to see if the search returns any records.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim SQLStatement as string
 
SqlStatement = "Your SQL statement"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(SqlStatement)

i = 0
With rs
   
Do
    Do Until .EOF
    i = i + 1
        If .EOF Then
            Exit Do
        End If
    .MoveNext
    Loop
Loop Until .EOF
End With
 
If i = 0 Then
    MsgBox "No Records found with the Selected Search Criteria."
    Exit Sub
End If

 

[Atomic Shrimp]

A simple loop can do that. I actual did this for someone else on the forums to check to see if the search returns any records.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim SQLStatement as string
 
SqlStatement = "Your SQL statement"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(SqlStatement)

i = 0
With rs
   
Do
    Do Until .EOF
    i = i + 1
        If .EOF Then
            Exit Do
        End If
    .MoveNext
    Loop
Loop Until .EOF
End With
 
If i = 0 Then
    MsgBox "No Records found with the Selected Search Criteria."
    Exit Sub
End If

Why not just:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim SQLStatement as string
 
SqlStatement = "Your SQL statement"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(SqlStatement)

rs.MoveFirst
rs.MoveLast
i=rs.RecordCount 
 
If i = 0 Then
    MsgBox "No Records found with the Selected Search Criteria."
    Exit Sub
End If

 

[Scooterbug]

That works too Atomic To be honest, i'm not that well versed in loops and dealing with record sources in VBA. Learn something new everyday!

 

[Atomic Shrimp]

I just noticed - in cases where there are no records, movelast and movefirst fail.

it is necessary to do something like:

On Error Resume Next
rs.MoveFirst
rs.MoveLast
On Error GoTo 0

 

[foxy]

At the minute I simply have this code in the search results form:

Private Sub Form_Load()
If IsNull(Me.child_ID) Then
    Me.Label54.Visible = True
    Me.Label44.Visible = False
    Me.Label45.Visible = False
    Me.Label47.Visible = False
    
Else
    Me.Label54.Visible = False
    Me.Label44.Visible = True
    Me.Label45.Visible = True
    Me.Label47.Visible = True
    
End If
End Sub

Dont really understand why this is not working.

Cheers

 

[foxy]

OK,

I now have the following VBA code on the Load event of my 'frmSearchChildResults' form:

Private Sub Form_Load()
Dim strRecSource As String
If IsNull([Forms]![frmSearchChild]![txtcypdref]) Or [Forms]![frmSearchChild]![txtcypdref] = "" Then
    strRecSource = "SELECT tblchild.forename & ' ' & tblchild.surname AS childname, * " & _
                "FROM tblChild " & _
                "WHERE ((tblChild.forename) Like ('*' & [Forms]![frmSearchChild]![txtforename] & '*')) AND ((tblChild.surname) Like ('*' & [Forms]![frmSearchChild]![txtsurname] & '*'))"
    Me.RecordSource = strRecSource
 
    If IsNull(Me.child_ID) Then
        Me.LblNoRecords.Visible = True
        Me.LblHead1.Visible = False
        Me.LblHead2.Visible = False
        Me.LblHead3.Visible = False
    Else
        Me.LblNoRecords.Visible = False
        Me.LblHead1.Visible = True
        Me.LblHead2.Visible = True
        Me.LblHead3.Visible = True
    End If
 
Else
    strRecSource = "SELECT tblchild.forename & ' ' & tblchild.surname AS childname, * " & _
                "FROM tblChild " & _
                "WHERE tblChild.cypd_per_id = [Forms]![frmSearchChild]![txtcypdref]"
    Me.RecordSource = strRecSource
 
    If IsNull(Me.child_ID) Then
        Me.LblNoRecords.Visible = True
        Me.LblHead1.Visible = False
        Me.LblHead2.Visible = False
        Me.LblHead3.Visible = False
    Else
        Me.LblNoRecords.Visible = False
        Me.LblHead1.Visible = True
        Me.LblHead2.Visible = True
        Me.LblHead3.Visible = True
    End If
 
End If
DoCmd.Close acForm, "frmSearchChild"
End Sub

The record sources work fine but it just will not change the visible properties of the labels.

I cannot understand why this is not working? Surely if there are no records returned, then the child_ID should be Null??

Can someone enlighten me?

Cheers

foxy

 

[Scooterbug]

Can you post a copy of the db? It might speed things along for ya if we can see exactly what is going on.

 

[foxy]

Not really I'm afraid mate. I dont mean to be a pain but it has sensitive child data in it.

In the function I posted, all the record sources work and the search works fine.

It is just that if there are no records returned, the visible properties do not change as I would expect.

Am I wrong in thinking the child_ID field would be Null if there are no records returned?

Cheers

 

[Scooterbug]

Understand about the data....would it be possible for you to make a copy of the db, strip out all the data and post it?

Me personally, I do better if I can see things in front of me when trying to figure things out.

As for your code...your record source does not include the Child_ID, so how is that getting populated on your form?

 

[foxy]

Yea, I'll have a go at stripping it down and posting.

I have a star at the end of the 'Select' line on the record source to return all fields from the table. The first part is just to concatenate the forename and surname.

strRecSource = "SELECT tblchild.forename & ' ' & tblchild.surname AS childname[COLOR=red][B], *[/B][/COLOR] " & _
                "FROM tblChild " & _
                "WHERE ((tblChild.forename) Like ('*' & [Forms]![frmSearchChild]![txtforename] & '*')) AND ((tblChild.surname) Like ('*' & [Forms]![frmSearchChild]![txtsurname] & '*'))"

Cheers

 

[foxy]

How do I upload my stripped out db? Even when I zip it up its still 4MB big, and the limit is ~700k!

 

[Scooterbug]

Compact and repair it first. Then zip it.

 

[foxy]

Genius! I knew that function existed in 2003 but could never find it in 2007!

Here you go...

Look in the VBA of 'frmSearchChildResults'. Thats where the function I posted is.

Foxy

 

[Scooterbug]

Genius! I knew that function existed in 2003 but could never find it in 2007!

Here you go...

Look in the VBA of 'frmSearchChildResults'. Thats where the function I posted is.

Foxy

Foxy, got it working. The big problem was how you were building your SQL statement. What I usually do (Because I can never remember the correct syntax for queries) is to build the desired query by creating a new query from the database window, add the fields and use a place holder for the where clauses. Then, I go into SQL view and copy it into my code. From there I can replace the place holders with the variables that I need.

Also, I added some code into a function to check to see if there are records in the recordset. Based on what is returned dictates which labels are displayed.

Any questions, feel free to ask.