Check recordcount -table in Ms access(local)

[jaishu]

Hi all,

I am in the process of developing a small Access application. i have the table locally in Access and I am using the following code to get the recordcount ( or to check if any record exists), but when i check for rst.eof condition, it is always true..but when i actually run the query i see, many rows returned, i m not clear about how to specify the connection parameter with the recordset, as i m using the tables locally, but i think something is wrong with the ADODB connection???

[code starts here]

Private Sub Process_Click()
Dim rcst As New ADODB.Recordset
Dim Con As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set Con = CurrentProject.Connection

If Frame160 = 1 Then

query1 = "select * from Table a where DMNum LIKE '" & Textboxentry& "*'"
rst.Open query1, Con, adOpenDynamic, adLockOptimistic
If rst.EOF = True Then

MsgBox " No Records Available for this search"
Textboxentry.Text = ""
rst.Close
Call Form_Load
Exit Sub

Else

query1 = "select * from Table a where DMNum LIKE '" & Textboxentry& "*'"
Me.Mysubform.Form.RecordSource = query1
Form_Myform.Textboxentry= Clear
Form_Myform.Textboxentry.SetFocus
Textboxentry= ""
rst.Close

End If

ElseIf Frame160 = 2 Then
....................
................
End If
End Sub
[code ends here]

Is there anyother way that i can check for recordcount for the Query1?


Any help is highly appreciated.
Thanks so much in advance....

[FishVal]

Hi, jaishu.

Just two suggestions.

• get a runtime value of query1 variable and try to execute in Query builder
• try to use "%" wildcard instead of "*"

Regards,
Fish

[ADezii]

Hi all,

I am in the process of developing a small Access application. i have the table locally in Access and I am using the following code to get the recordcount ( or to check if any record exists), but when i check for rst.eof condition, it is always true..but when i actually run the query i see, many rows returned, i m not clear about how to specify the connection parameter with the recordset, as i m using the tables locally, but i think something is wrong with the ADODB connection???

[code starts here]

Private Sub Process_Click()
Dim rcst As New ADODB.Recordset
Dim Con As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set Con = CurrentProject.Connection

If Frame160 = 1 Then

query1 = "select * from Table a where DMNum LIKE '" & Textboxentry& "*'"
rst.Open query1, Con, adOpenDynamic, adLockOptimistic
If rst.EOF = True Then

MsgBox " No Records Available for this search"
Textboxentry.Text = ""
rst.Close
Call Form_Load
Exit Sub

Else

query1 = "select * from Table a where DMNum LIKE '" & Textboxentry& "*'"
Me.Mysubform.Form.RecordSource = query1
Form_Myform.Textboxentry= Clear
Form_Myform.Textboxentry.SetFocus
Textboxentry= ""
rst.Close

End If

ElseIf Frame160 = 2 Then
....................
................
End If
End Sub
[code ends here]

Is there anyother way that i can check for recordcount for the Query1?


Any help is highly appreciated.
Thanks so much in advance....

Private Sub Process_Click()
Dim rcst As New ADODB.Recordset
Dim Con As New ADODB.Connection
Dim rst As New ADODB.Recordset
 
Set Con = CurrentProject.Connection
 
If Frame160 = 1 Then
  query1 = "select * from Table a where DMNum LIKE '" & Textboxentry & "*'"
  rst.Open query1, Con, adOpenDynamic, adLockOptimistic
    If Not rst.BOF And Not rst.EOF Then     'valid Record(s) exist
      query1 = "select * from Table a where DMNum LIKE '" & Textboxentry & "*'"
        Me.Mysubform.Form.RecordSource = query1
        Form_Myform.Textboxentry = Clear
        Form_Myform.Textboxentry.SetFocus
        Textboxentry = ""
          rst.Close
    Else
      MsgBox " No Records Available for this search"
      Textboxentry.Text = ""
        rst.Close
        Call Form_Load
          Exit Sub
    End If
ElseIf Frame160 = 2 Then
....................
................
End If
End Sub

P.S. - Have a look at this Link also:
How to check for an Empty Recordset

[jaishu]

Hi Fishval and Adezii,

Thank you very much, yes it works now.. i used both your techniques
used " % " instead of " * " when checking EOF condition, and for requery(when record exists to populate values) used the ' * '.
It worked, but any reason why * did not work for EOF/BOF checking and only % worked?????

BUT THANK YOU SO MUCH FOR CORRECTING THE FLOW OF CODE,
Really appreciate your help!!!


Bye,
Jaishu

[FishVal]

Hi, Jaishu.

Read ANSI Standards in String Comparisons article.

Regards,
Fish