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.Connectio n
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, adLockOptimisti c
If rst.EOF = True Then

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

Else

query1 = "select * from Table a where DMNum LIKE '" & Textboxentry& "*'"
Me.Mysubform.Fo rm.RecordSource = query1
Form_Myform.Tex tboxentry= Clear
Form_Myform.Tex tboxentry.SetFo cus
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.Connectio n
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, adLockOptimisti c
If rst.EOF = True Then

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

Else

query1 = "select * from Table a where DMNum LIKE '" & Textboxentry& "*'"
Me.Mysubform.Fo rm.RecordSource = query1
Form_Myform.Tex tboxentry= Clear
Form_Myform.Tex tboxentry.SetFo cus
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....

[CODE=vb]
Private Sub Process_Click()
Dim rcst As New ADODB.Recordset
Dim Con As New ADODB.Connectio n
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, adLockOptimisti c
If Not rst.BOF And Not rst.EOF Then 'valid Record(s) exist
query1 = "select * from Table a where DMNum LIKE '" & Textboxentry & "*'"
Me.Mysubform.Fo rm.RecordSource = query1
Form_Myform.Tex tboxentry = Clear
Form_Myform.Tex tboxentry.SetFo cus
Textboxentry = ""
rst.Close
Else
MsgBox " No Records Available for this search"
Textboxentry.Te xt = ""
rst.Close
Call Form_Load
Exit Sub
End If
ElseIf Frame160 = 2 Then
............... .....
............... .
End If
End Sub[/CODE]
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