Access 2007 Subscript out of Range (9)

[GMC -LSND]

Hello all,

I have the following code I am using to pull information out of a SQL
database and list the information on a form. I get the above error when
running the form and don't get any information listed unless I rem out the
mdblZ1 out the line that says: A1(mdblX1, mdblY1, mdblZ1) = A1(mdblX1,
mdblY1, mdblZ1) + 1. If I rem it out, the query runs and get information,
just nothing out of the sex fields. I am nothing close to a coder, so I
have no idea what I am doing wrong and no idea how to fix it. Any
suggestions?

Option Compare Database
Option Explicit
Dim mstrCrit As String
Dim mdblX1 As Double
Dim mdblY1 As Double
Dim mdblZ1 As Double
Dim mydb As DAO.Database
Dim mys As DAO.Recordset
Private Sub Form_Load()
ahtPushStack "Form_Load"
On Error GoTo p_err
ReDim A1(18, 5) As Double
mstrCrit = Me!Field57
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set mys = mydb.OpenRecordset(mstrCrit, dbOpenDynaset, dbSeeChanges)
mdblX1 = 0
mdblY1 = 0
'****** for sex
mdblZ1 = 0
'*****End of add for sex

With mys
.MoveFirst
Do Until .EOF
'****Added for Sex
If !SEX = "F" Then
mdblZ1 = 1
ElseIf !SEX = "M" Then
mdblZ1 = 2
End If
'*******End of Add for sex
If !RACE = "A" Then
mdblX1 = 1
ElseIf !RACE = "B" Then
mdblX1 = 2
ElseIf !RACE = "H" Then
mdblX1 = 3
ElseIf !RACE = "N" Then
mdblX1 = 4
ElseIf !RACE = "W" Then
mdblX1 = 5
Else
mdblX1 = 6
End If
If !AGE < 18 Then
mdblY1 = 1
ElseIf !AGE >= 18 And mys!AGE <= 59 Then
mdblY1 = 2
Else
mdblY1 = 3
End If
'******Added mdblZ1 to next line for Sex
A1(mdblX1, mdblY1, mdblZ1) = A1(mdblX1, mdblY1, mdblZ1) + 1
.MoveNext
Loop
.Close
End With
Set mys = Nothing
Set mydb = Nothing
A17 = A1(1, 1)
B17 = A1(2, 1)
H17 = A1(3, 1)
N17 = A1(4, 1)
W17 = A1(5, 1)
O17 = A1(6, 1)
'****** Added for Sex
AF17 = A1(7, 1)
BF17 = A1(8, 1)
HF17 = A1(9, 1)
NF17 = A1(10, 1)
WF17 = A1(11, 1)
OF17 = A1(12, 1)
AM17 = A1(13, 1)
BM17 = A1(14, 1)
HM17 = A1(15, 1)
NM17 = A1(16, 1)
WM17 = A1(17, 1)
OM17 = A1(18, 1)
'***** End of Add for Sex
A59 = A1(1, 2)
B59 = A1(2, 2)
H59 = A1(3, 2)
N59 = A1(4, 2)
W59 = A1(5, 2)
O59 = A1(6, 2)
'******Added for Sex
AF59 = A1(7, 2)
BF59 = A1(8, 2)
HF59 = A1(9, 2)
NF59 = A1(10, 2)
WF59 = A1(11, 2)
OF59 = A1(12, 2)
AM59 = A1(13, 2)
BM59 = A1(14, 2)
HM59 = A1(15, 2)
NM59 = A1(16, 2)
WM59 = A1(17, 2)
OM59 = A1(18, 2)
'********End of add for sex
A60 = A1(1, 3)
B60 = A1(2, 3)
H60 = A1(3, 3)
N60 = A1(4, 3)
W60 = A1(5, 3)
O60 = A1(6, 3)
'*****Added for Sex
AF60 = A1(7, 3)
BF60 = A1(8, 3)
HF60 = A1(9, 3)
NF60 = A1(10, 3)
WF60 = A1(11, 3)
OF60 = A1(12, 3)
AM60 = A1(13, 3)
BM60 = A1(14, 3)
HM60 = A1(15, 3)
NM60 = A1(16, 3)
WM60 = A1(17, 3)
OM60 = A1(18, 3)
'*******End of Add for sex
TA = A1(1, 1) + A1(1, 2) + A1(1, 3)
TB = A1(2, 1) + A1(2, 2) + A1(2, 3)
TH = A1(3, 1) + A1(3, 2) + A1(3, 3)
TN = A1(4, 1) + A1(4, 2) + A1(4, 3)
TW = A1(5, 1) + A1(5, 2) + A1(5, 3)
TT = A1(6, 1) + A1(6, 2) + A1(6, 3)
Tot = TA + TB + TH + TN + TW + TT
T17 = A17 + B17 + H17 + N17 + W17 + O17
T59 = A59 + B59 + H59 + N59 + W59 + O59
T60 = A60 + B60 + H60 + N60 + W60 + O60
'*****Added for Sex
TF17 = AF17 + BF17 + HF17 + NF17 + WF17 + OF17
TM17 = AM17 + BM17 + HM17 + NM17 + WM17 + OM17
TF59 = AF59 + BF59 + HF59 + NF59 + WF59 + OF59
TM59 = AM59 + BM59 + HM59 + NM59 + WM59 + OM59
TF60 = AF60 + BF60 + HF60 + NF60 + WF60 + OF60
TM60 = AM60 + BM60 + HM60 + NM60 + WM60 + OM60
'****End of Add for Sex
PROC_EXIT:
ahtPopStack
Exit Sub
p_err:
RootErr (Err.Description & " (" & Err.Number & ")")
Resume PROC_EXIT
End Sub

 

[Arvin Meyer [MVP]]

Subscript out of range errors occur because of mismatched datatypes. You are
trying to stuff the wrong type of data into a field with the same name. One
of the errors in Access occurs when trying to put data into a date field
that precedes the date of 100 AD. The Access date field won't handle that.

The easiest way (which is still a pain) I've found to fix it is to build a
new database and import the table from the old one. Then import the data
that's giving you a problem into a new table and compare the data types and
the data itself.

 

[M Skabialka]

ReDim A1(18, 5) As Double
A1(mdblX1, mdblY1, mdblZ1) = A1(mdblX1, mdblY1, mdblZ1) + 1

You have declared array A1 as having two dimensions A1(18, 5) , then used it
with three A1(mdblX1, mdblY1, mdblZ1)

The subscript out of range indicates this.

It looks as though you need a 3 dimensional array A1(race, age, gender)

Mich

 

[GMC -LSND]

That must be it! how you do a 3 dimensional Array?

 

[M Skabialka]

ReDim A1(2,6,3) As Integer
2 gender, 6 race, 3 age
It's an integer because you don't need decimal points if you are just
counting.

This should work now.
A1(mdblX1, mdblY1, mdblZ1) = A1(mdblX1, mdblY1, mdblZ1) + 1

Not sure what A17, B17 etc are; fields on a form?
A17 = A1(1, 1)
B17 = A1(2, 1)
H17 = A1(3, 1)

But this should help
A1(1,2,3) = female, Black(?), age 60+
A1(2,5,1) = male, White(?), under 18
etc

Hope this helps,
Mich