Array Help (1 Viewer)

[graviz]

I use the code listed below to create a custom function. I'm able to look at a field "service_code_string" (looks like "D0|D1|D2|D3|;=|KT|T:|2R|T0|T0|NG|A9|LK|U+|U$") and am able to test if a set of codes in in each line in the table. I'd like to try and code better and store them in a table instead of hard coding. When searching for this I tried to learn about recordsets and use that to accomplish what I'm trying to do, however I'm unable to make anything work. Any suggestions on how to do this (I named the table containing the codes "Code_Table")?

Function DishNetActivityCode(service_code_string As String) As Integer
        Dim CodeArray, i As Integer
        CodeArray = Array("60", "64", "74", "8.", "~R", "R=")
        
        For i = 0 To UBound(CodeArray)
            If InStr(service_code_string, CodeArray(i)) Then
                DishNetActivityCode = 1
                Exit Function
            End If
        Next
        DishNetActivityCode = 0
    End Function

Here was my attempt at doing this:

Function SHS_Test(service_code_string As String) As Integer
Dim i As Integer, rs As DAO.Recordset, db As DAO.Database
Set rs = db.OpenRecordset("Code_Table", dbOpenDynaset)
For i = 0 To UBound(rs)
If InStr(service_code_string, rs(i)) Then
SHS_Test = 1
Exit Function
End If
Next
SHS_Test = 0
End Function

 

[jdraw]

Can you step back from vba and ell us in plain English WHAT you are trying to accomplish?

You have shown 2 functions.
The first deals with an Array, the second is a mishmash of array syntax and missing parts of a recordset.

Typically you Dim a recordset , then open it -as you have done in sample 2.

You have a recordset in a Loop
eg Dowhile not rs.eof
.....other code
rs.movenext
Loop

Do while.... rs.movenext... Loop iterates through the recordset.
Movenext moves to the next position(record) in the recordset.

Good luck.

 

[graviz]

Can you step back from vba and ell us in plain English WHAT you are trying to accomplish?

You have shown 2 functions.
The first deals with an Array, the second is a mishmash of array syntax and missing parts of a recordset.

Typically you Dim a recordset , then open it -as you have done in sample 2.

You have a recordset in a Loop
eg Dowhile not rs.eof
.....other code
rs.movenext
Loop

Do while.... rs.movenext... Loop iterates through the recordset.
Movenext moves to the next position(record) in the recordset.

Good luck.

The first set of code I'm using today and it works great. All I'm trying to do is put the codes I'm searching for (in the first section of code) and put it in a table. That is all. Make sense? Again I haven't done anything with recordsets so I'm sure I'm not doing it right. How could I tweak the first code to do this?

 

[jdraw]

Here's a procedure using some of your data. This just puts each array element into a record in a table. It also displays the result in the immediate window.

You must create a table My_Table, with a field My_Field (text) for this to work.

'---------------------------------------------------------------------------------------
' Procedure : ArrayToRecordset
' Author    : Jack
' Date      : 08/01/2014
' Purpose   : To move data from an array into records in a recordset
'---------------------------------------------------------------------------------------
'
Sub ArrayToRecordset()
          Dim CodeArray As Variant, i As Integer
          Dim db As DAO.Database, rs As DAO.Recordset
10       On Error GoTo ArrayToRecordset_Error

20        CodeArray = Array("60", "64", "74", "8.", "~R", "R=")
30        Set db = CurrentDb
40        Set rs = db.OpenRecordset("my_Table")
50        With rs   'using the rs
              'loop through the Array
60            For i = LBound(CodeArray) To UBound(CodeArray)
70                .AddNew  'add a new record
80                !My_Field = CodeArray(i)  'assign this aray element to My_Field
90                .Update
100           Next
              'finished with the array
110       End With
          'show what's in the recordset
120       rs.MoveFirst    'get to the first record in recordset
130       Do While Not rs.EOF
140           Debug.Print rs!My_Field
150           rs.MoveNext
160       Loop
170       rs.Close

180      On Error GoTo 0
190      Exit Sub

ArrayToRecordset_Error:

200       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ArrayToRecordset of Module AWF_Related"

End Sub

 

[graviz]

Here's a procedure using some of your data. This just puts each array element into a record in a table. It also displays the result in the immediate window.

You must create a table My_Table, with a field My_Field (text) for this to work.

'---------------------------------------------------------------------------------------
' Procedure : ArrayToRecordset
' Author    : Jack
' Date      : 08/01/2014
' Purpose   : To move data from an array into records in a recordset
'---------------------------------------------------------------------------------------
'
Sub ArrayToRecordset()
          Dim CodeArray As Variant, i As Integer
          Dim db As DAO.Database, rs As DAO.Recordset
10       On Error GoTo ArrayToRecordset_Error
 
20        CodeArray = Array("60", "64", "74", "8.", "~R", "R=")
30        Set db = CurrentDb
40        Set rs = db.OpenRecordset("my_Table")
50        With rs   'using the rs
              'loop through the Array
60            For i = LBound(CodeArray) To UBound(CodeArray)
70                .AddNew  'add a new record
80                !My_Field = CodeArray(i)  'assign this aray element to My_Field
90                .Update
100           Next
              'finished with the array
110       End With
          'show what's in the recordset
120       rs.MoveFirst    'get to the first record in recordset
130       Do While Not rs.EOF
140           Debug.Print rs!My_Field
150           rs.MoveNext
160       Loop
170       rs.Close
 
180      On Error GoTo 0
190      Exit Sub
 
ArrayToRecordset_Error:
 
200       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ArrayToRecordset of Module AWF_Related"
 
End Sub

Sorry I already have the table with those 6 codes (Table Name: Code_Table, Field Name: Codes) I just need to be able to do the search like I'm doing in my first section of code. Your code looks like it imports the items in the array into a table.

For i = 0 To UBound(CodeArray)
            If InStr(service_code_string, CodeArray(i)) Then
                DishNetActivityCode = 1
                Exit Function
            End If
        Next
        DishNetActivityCode = 0

Basically where is searching the "service_code_string" I use the array "(CodeArray(i))" as the lookup. I'd like to replace the array with the table of codes. Sorry if I didn't explain it better.

 

[jdraw]

I thought your issue was putting the array data into a recordset based on

All I'm trying to do is put the codes I'm searching for (in the first section of code) and put it in a table. That is all. Make sense? Again I haven't done anything with recordsets so I'm sure I'm not doing it right.

Air code:

Function SHS_Test(service_code_string As String) As Integer
Dim i As Integer, rs As DAO.Recordset, db As DAO.Database
Set rs = Currentdb.OpenRecordset("Code_Table", dbOpenDynaset)
Do while not rs.eof
   If InStr(service_code_string,[COLOR="Purple"]      rs!YourCode[/COLOR]) Then
       SHS_Test = 1
       Exit Function
   End If
[COLOR="Purple"]rs.MoveNext[/COLOR]
[COLOR="Purple"]Loop[/COLOR]
SHS_Test = 0
End Function

 

[graviz]

I thought your issue was putting the array data into a recordset based on


Air code:

Function SHS_Test(service_code_string As String) As Integer
Dim i As Integer, rs As DAO.Recordset, db As DAO.Database
Set rs = Currentdb.OpenRecordset("Code_Table", dbOpenDynaset)
Do while not rs.eof
   If InStr(service_code_string,[COLOR=purple]      rs!YourCode[/COLOR]) Then
       SHS_Test = 1
       Exit Function
   End If
[COLOR=purple]rs.MoveNext[/COLOR]
[COLOR=purple]Loop[/COLOR]
SHS_Test = 0
End Function

What is "rs!YourCode"?

 

[graviz]

What is "rs!YourCode"?

I'm assuming it's some code I would like to run but shouldn't it just be that recordset?

 

[jdraw]

It represents the field in your table where you intend to store the value from the array.
THis whole thing assumes your Table is named Code_Table .
I called it yourCode since you named the table Code_Table.

 

[graviz]

It represents the field in your table where you intend to store the value from the array.
THis whole thing assumes your Table is named Code_Table .
I called it yourCode since you named the table Code_Table.

Got it, tried it, recieved "object variable or with block variable not set" error on line

Set rs = Currentdb.OpenRecordset("Code_Table", dbOpenDynaset)