Create variable array name

[crossy5575]

Hi there,
i am creating a form which has lots of small fields (which will be colored dependent on value).
There are lots of rows of these fields and as such need to have a variable array name which changes on a loop.

the array is called myha, myhb, myhc... and so on.
so i need to adapt this code to allow it - however i dont know how to do it... can you help??

I also need to know what to declare these variables as!
I have an issue with the line Str(vmax)(i, 2)

' Gives the number of repetitions i need
For aa = 0 To 13
'I have a "myhm" field for something else so need to skip this!
  If aa = 12 Then GoTo xloop
    If Letter = "A" Then GoTo leta
    Letter = Chr(Asc(Letter) + 1)
leta:
'creates the variable Myha, myhb etc which will give a different arrray each time
    vmax = "myh" & LCase(Letter)
       
'looping code for each of the individual textboxes 
    For i = LBound(myday) To UBound(myday)
        If Not IsEmpty(myday(i, 1)) Then
            rs.Filter = "[staff no] =" & myh(aa + 1, 0) & "And [StartDate] >=" & myday(i, 0)
                Set rsfiltered = rs.OpenRecordset
                    Do While (Not rsfiltered.EOF)
'am having this problem - where vmax should be the arrayname, then followed by the array variable
                        If IsEmpty((vmax) (i, 2)) Then
                        Str(vmax)(i, 2) = rsfiltered![Type]
                        End If
                    rsfiltered.MoveNext
                    Loop
        End If
    Next i
xloop:
  Next aa

 

[CJ_London]

with respect, no idea what you are asking - and please use the advanced editor and code blocks to preserve indentation - your code may mean something to you, but to us it is just plain difficult to read. To use a phrase, if you can't be bothered to present your question is a readable format, why should someone else bother to try to understand it.

 

[crossy5575]

Sorry CJ I have leant how to show code as code! it was intended when I looked at it! I hope this helps, and thanks for the info, point taken.
S

 

[gemma-the-husky]

it's hard to know exactly what you are trying to do.

Are you trying to populate a number of unbound controls in a similar way?

 

[CJ_London]

can you show the entire code for this function - at least to show the variable declarations

Still not clear what you are trying to do or what the problem is but I note you are using lbound and ubound.

an array with 6 elements would be identified 0...5

you code is using 1...6 - so will fail on element 6 because that element does not exist - and you will not be referencing element 0

so change this

For i = LBound(myday) To UBound(myday)

to this

For i = LBound(myday)-1 To UBound(myday)-1

 

[crossy5575]

Reallly appreciate your patience with this, thank you.

I want to populate myha(i,2) with with a letter (relating to the holiday type the person is taking), where myha(i,0) will be date over a quarter (i=0 to 80)

having populated this i then need it to populate
myhb(i,2) for the 2nd employee

thus the myh being static and the variable letter referring to a variable to complete the array. It is working until it hits Str(vmax)(i, 2) = rsfiltered![Type] where the vmax isnt recognised as the array mhy+letter, despite being shown as "mhya" etc...

The full code is below;

Option Compare Database
Option Explicit
Private intmonth As Integer
Private intyear As Integer
Public todayx As Integer
Private firstdate As Long
Private firstmon As Integer
Private firstmon2 As Long
Private mymonth(0 To 20, 0 To 2) As Variant
Private myday(0 To 80, 0 To 3) As Variant
Private myarray(0 To 41, 0 To 3) As Variant
Private myha(0 To 80, 0 To 2) As Variant
Private myhb(0 To 80, 0 To 2) As Variant
Private myhc(0 To 80, 0 To 2) As Variant
Private myhd(0 To 80, 0 To 2) As Variant
Private myhe(0 To 80, 0 To 2) As Variant
Private myhf(0 To 80, 0 To 2) As Variant
Private myhg(0 To 80, 0 To 2) As Variant
Private myhh(0 To 80, 0 To 2) As Variant
Private myhi(0 To 80, 0 To 2) As Variant
Private myhj(0 To 80, 0 To 2) As Variant
Private myhk(0 To 80, 0 To 2) As Variant
Private myhl(0 To 80, 0 To 2) As Variant
Private myhn(0 To 80, 0 To 2) As Variant
Private masterf As Integer

Public Sub loadarray()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsfiltered As DAO.Recordset
Dim i As Integer
Dim strsql As String
Dim Letter  As String
Dim aa As Integer
Dim vmax As String
Dim staffrec As Integer
 
Letter = "A"
masterf = Me.OpenArgs

'create sql for 02holiday query
    If masterf = 1 Then
        strsql = "SELECT [12b Holsub].[staff no], [01 Name].Cname, [01 Name].Sname, [12b Holsub].ayear, [12 Holiday].startdate, [12 Holiday].enddate, [12 Holiday].type, [12 Holiday].reason, [12 Holiday].daterequested, [09 Jobs].department, [09b Departments].[select], [select].[select] " _
        & "FROM ([09b Departments] " _
        & "INNER JOIN ((([01 Name] INNER JOIN [12b Holsub] " _
        & "ON [01 Name].[ID] = [12b Holsub].[staff no]) LEFT JOIN [select] ON [01 Name].ID = [select].ID) INNER JOIN [09 Jobs] ON [01 Name].ID = [09 Jobs].Staffno) ON [09b Departments].ID = [09 Jobs].department) INNER JOIN [12 Holiday] ON [12b Holsub].ID = [12 Holiday].holidayno " _
        & "WHERE ((([12b Holsub].ayear)=Year(Date())) AND (([select].[select])=Yes));"
    
    ElseIf masterf = 2 Then
        strsql = "SELECT [12b Holsub].[staff no], [01 Name].Cname, [01 Name].Sname, [12b Holsub].ayear, [12 Holiday].startdate, [12 Holiday].enddate, [12 Holiday].type, [12 Holiday].reason, [12 Holiday].daterequested, [09 Jobs].department, [09b Departments].[select], [select].[select] " _
        & "FROM ([09b Departments] " _
        & "INNER JOIN ((([01 Name] INNER JOIN [12b Holsub] " _
        & "ON [01 Name].[ID] = [12b Holsub].[staff no]) LEFT JOIN [select] ON [01 Name].ID = [select].ID) INNER JOIN [09 Jobs] ON [01 Name].ID = [09 Jobs].Staffno) ON [09b Departments].ID = [09 Jobs].department) INNER JOIN [12 Holiday] ON [12b Holsub].ID = [12 Holiday].holidayno " _
        & "WHERE ((([12b Holsub].ayear)=Year(Date())) AND (([09b Departments].[select])=Yes));"
    End If

Set db = CurrentDb
Set rs = db.OpenRecordset(strsql)

         If Not rs.BOF And Not rs.EOF Then

staffrec = myh(1, 2)

For aa = 0 To 13
  If aa = 12 Then GoTo xloop
    If Letter = "A" Then GoTo leta
    Letter = Chr(Asc(Letter) + 1)
leta:
    vmax = "myh" & LCase(Letter)
   
 ' variablenames (vmax())
        
         For i = LBound(myday) To UBound(myday)
         
             If Not IsEmpty(myday(i, 1)) Then
                                        
                    rs.Filter = "[staff no] =" & myh(aa + 1, 0) & "And [StartDate] >=" & myday(i, 0) & "And [EndDate] <= " & myday(i, 0)
                        
                        Set rsfiltered = rs.OpenRecordset
                        
                                Do While (Not rsfiltered.EOF)
                                  ' myarray(i, 2) = rsfiltered!reason
                                   If IsEmpty((vmax) (i, 2)) Then
                                   Str(vmax)(i, 2) = rsfiltered![Type]
                                   End If
                                    'Debug.Print axray & " - " & axray(i, 3)
                                    rsfiltered.MoveNext
                                Loop
            End If
        
        Next i
xloop:
  Next aa
        
    End If

 rsfiltered.Close
    rs.Close
    
Set rsfiltered = Nothing
Set db = Nothing
Set rs = Nothing

End Sub

 

[gemma-the-husky]

is it crashing.

I don't really understand, but a couple of thoughts.
a) It might be a problem with arrays being zero based
b) it might be to do with your test for aa=12 skipping the loop entirely.

Put a breakpoint in the code, and step through. Then you can examine your variables carefully.

Generally, I would have thought this sort of thing would be more easily managed with a query/queries, maybe a make-table query, and then a bound continuous form or data sheet to display the data.

Far easier than manipulating arrays.

 

[BeeJayEff]

I don't think you can use str() like that. As far as I'm aware, the only way of creating dynamic code like this is by using the Eval() function.

 

[stopher]

I would create a class to hold my array. Then add class instances to a collection. Arrays can then be addressed as unique strings and the array elements easily accessed through the class.

hth