Help in Assigning value using recordset

  • Thread starter Thread starter g
  • Start date Start date
G

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
 
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
 
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




Graham Mandeno said:
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

g said:
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
Click to expand...
Click to expand...
 
Back
Top