Help in Assigning value using recordset

[g]

Hi,

I'm using access 2000. I'm not that super expert in using vb in access so
please help me out on my problem.

Here is my code, I'm trying to assign whatever the first 3 value(Field Name
"Number") I could find in a 3 variable from MR_SelectedCriteria Table.
Example: If the first 3 Number recordset find is 105,108,111 Then
Criteria1=105
Criteria2=108 and Criteria3=111. The value of number in table always change
as I have another function that will do that. I'm thinking of using recordset
but I don't know how to assign the value.



Dim strSql As String
Dim Num As Variant
Dim Criteria1 As Variant
Dim Criteria2 As Variant
Dim Criteria3 As Variant


strSql = "SELECT Number FROM MR_SelectedCriteria;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

Do While Not rs.EOF
Num = rs.Fields("Number")
rs.MoveNext

' I think this is where I need to assign the value on Criteria(1 to 3)

Loop

rs.Close
Set rs = Nothing

 

[Graham Mandeno]

Hi g

I suggest you use an array:

Dim strSql As String
Dim Num As Integer
Dim Criteria(1 To 3) As Long ' or whatever type the Number field is

strSql = "SELECT Number FROM MR_SelectedCriteria;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

For Num = 1 To 3
If Not rs.EOF Then ' just in case there are < 3 records
Criteria(Num) = rs.Fields("Number")
rs.MoveNext
End If
Next
rs.Close
Set rs = Nothing

' do something with the results...
For Num = 1 To 3
Debug.Print "Criteria" & Num & " = " & Criteria(Num)
Next

 

[g]

Hi Graham,

Thank you very much for your reply. It helps me a lot and I never thought of
using array actually. A million thanks

Hi g

I suggest you use an array:

Dim strSql As String
Dim Num As Integer
Dim Criteria(1 To 3) As Long ' or whatever type the Number field is

strSql = "SELECT Number FROM MR_SelectedCriteria;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

For Num = 1 To 3
If Not rs.EOF Then ' just in case there are < 3 records
Criteria(Num) = rs.Fields("Number")
rs.MoveNext
End If
Next
rs.Close
Set rs = Nothing

' do something with the results...
For Num = 1 To 3
Debug.Print "Criteria" & Num & " = " & Criteria(Num)
Next

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I'm using access 2000. I'm not that super expert in using vb in access so
please help me out on my problem.

Here is my code, I'm trying to assign whatever the first 3 value(Field
Name
"Number") I could find in a 3 variable from MR_SelectedCriteria Table.
Example: If the first 3 Number recordset find is 105,108,111 Then
Criteria1=105
Criteria2=108 and Criteria3=111. The value of number in table always
change
as I have another function that will do that. I'm thinking of using
recordset
but I don't know how to assign the value.



Dim strSql As String
Dim Num As Variant
Dim Criteria1 As Variant
Dim Criteria2 As Variant
Dim Criteria3 As Variant


strSql = "SELECT Number FROM MR_SelectedCriteria;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

Do While Not rs.EOF
Num = rs.Fields("Number")
rs.MoveNext

' I think this is where I need to assign the value on Criteria(1 to
3)

Loop

rs.Close
Set rs = Nothing