By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,567 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,567 IT Pros & Developers. It's quick & easy.

Check recordcount -table in Ms access(local)

P: 22
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....
Feb 13 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Feb 13 '08 #2

ADezii
Expert 5K+
P: 8,701
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....
Expand|Select|Wrap|Line Numbers
  1. Private Sub Process_Click()
  2. Dim rcst As New ADODB.Recordset
  3. Dim Con As New ADODB.Connection
  4. Dim rst As New ADODB.Recordset
  5.  
  6. Set Con = CurrentProject.Connection
  7.  
  8. If Frame160 = 1 Then
  9.   query1 = "select * from Table a where DMNum LIKE '" & Textboxentry & "*'"
  10.   rst.Open query1, Con, adOpenDynamic, adLockOptimistic
  11.     If Not rst.BOF And Not rst.EOF Then     'valid Record(s) exist
  12.       query1 = "select * from Table a where DMNum LIKE '" & Textboxentry & "*'"
  13.         Me.Mysubform.Form.RecordSource = query1
  14.         Form_Myform.Textboxentry = Clear
  15.         Form_Myform.Textboxentry.SetFocus
  16.         Textboxentry = ""
  17.           rst.Close
  18.     Else
  19.       MsgBox " No Records Available for this search"
  20.       Textboxentry.Text = ""
  21.         rst.Close
  22.         Call Form_Load
  23.           Exit Sub
  24.     End If
  25. ElseIf Frame160 = 2 Then
  26. ....................
  27. ................
  28. End If
  29. End Sub
P.S. - Have a look at this Link also:
How to check for an Empty Recordset
Feb 14 '08 #3

P: 22
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
Feb 15 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Hi, Jaishu.

Read ANSI Standards in String Comparisons article.

Regards,
Fish
Feb 15 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.