-
Hi,
I have a query which extracts some records from a table according to some condition. The problem is when I run this query on Access it works perfectly; but when I use it in my VB program it doesn't work properly.
I have "Table1", and "Table2" and field in each called "Index"
"Table1.Index" "Table2.Index"
3 4
2 7
0 5
1 2
9 10
My query is:
"SELECT [Table1].* FROM [Table1] WHERE [Table1].Index NOT IN (Select [Table2].Index FROM [Table2])"
.. this query should return :
0
9
3
1
.. the problem is that if I create a query in Access and paste this code it work perfectly but when I use it in VB it returns only the first record .. I don’t know why??
Could anyone help
Thank you in advance.
Wesam
------------------
-
Hi,
How are you getting the results? How do you know it is returning only one record?
Preeti
-
Actually I need the result to be processed by another routine, however, I use the following:
Dim Rec As RecordSet
SqlCommand="SELECT ....."
Set Rec = DB.OpenRecordset(SqlCommand)
For Counter = 0 TO Rec.RecordCount-1
'I take each record and pass it to a function
Next
.. when I debug this code and stop at Rec.RecordCount and check its value I always find it 1 !!!!!!!
Hopefully I well explained this problem, I have no idea why this happens in VB though it does't in Access.
I appreciate your help.
Wesam.
------------------
-
Hi,
Here's your problem: Recordcount for some reason returns the number of records accessed, not the total number of records!!!
(Don't ask why, I don't know)
Instead of using a for loop, you could use a do until loop:
do until Rec.eof
'This will loop through the records until you reach the end of the recordset.
'I take each record and pass it to a function
loop
..
HTH,
Preeti
-
Ohhhhh thank you preeti .. that was the problem, and I corrected it as you said and it worked perfectly .. I really appreciate it.
Wesam
------------------
-
Here's your problem: Recordcount for some reason returns the number of records accessed, not the total number of records!!!
(Don't ask why, I don't know)
Record Count Property returns Current Record Position of the table.
You have to movelast to get total no of records.
I hopes this helps in future.
Rs.movelast
Rs.recordcount
The above code gives you exact no of Records.
-
I actually did so, my fault was that I didn't know the exact function of RecordCount property. Anyway thank you very much for your help.
Wesam
------------------
