Creating a RunTime Array or Matrix to Edit data

[MsfStl]

I am creating a research db and am looking for a shortcut for some data editing.
THE SITUATION: I have a series of questions that ask a respondent to check any and all reasons why they believe something. Obviously since it is an "any or all" (there are 8 options) I had to create individual variables for each option. Additionally, I used check boxes for the data entry. Since these are boolean Access assigns a 0 for false and a -1 for true. Our boolean convention here is a 1 for false and a 5 for true.
THE QUESTION: What I would like to do is create a runtime array or matrix that would look at each variable and edit the value from 0 to 1 and from -1 to 5. The variable names follow this naming convention: ETH191 - ETH198, ETH201 - ETH208, etc. How can I do this?
MY LONG WAY ATTEPMT: I know there has to be an easier way but, given my 'neophyte' programming status here is what I am doing. Presently I am looking at creating a bunch of If..Then statements that read something like:
IF ETH191 = 0 Then
ETH191 = 1
End if
IF ETH191 = -1 Then
ETH191 = 5
End if
I would continue this from 191-198 through 291-298 or 88 times. That is a lot of code for a simple task. I know there is a better way.

SIDEBAR NOTES: It would be easier if I could actually change the default values of the checkboxes then I wouldn't have to do this. Also, this data will ultimately be exported, most likely to SAS for analysis. I could do the conversion in SAS, but if someone else wants to run any analysis I want them to be able to use the data straight away.

I thank you in advance for any help you may be able to provide!

 

[izyrider]

how about something along these lines

[UNTESTED]

dim myBox(1 to 88) 'you say you have a known number of checkboxes
dim myFrm as form
dim myCtl as control
dim myPointer as integer

set myFrm = me
myPointer = 1

for each myCtl in myFrm.controls

if myCtl.ControlType = acCheckBox then
'you could also play with myCtl.Name and other properties here if
'you like, perhaps using .....if left$(myCtl.name, 3) = "ETH" then ......
myBox(myPointer) = iif(myCtl.value, 5, 1)
myPointer = myPointer + 1
endif

next

set myFrm = nothing

[/UNTESTED]

izy

 

[izyrider]

...you should pick up the control name at the same time

dim myName(1 to 88)
for....
myName(myPointer) = myctl.name
...next


or go straight into a table


dim strSQL as string
for...
strSQL = "INSERT INTO myTable (strName, intResult) VALUES '" & myCtl.Name & "', " & iif(myCtl.value, 5, 1) & ");"
currentdb.execute strSQL
...next

izy

 

[MsfStl]

Thankyou very much for looking at this. I pretty much just copied and pasted what you gave me. Below is the code. Unfortunately, I am having a problem with the program picking up the CheckBoxes when following it with the debugger it doesn't pick up:
If myCtl.ControlType = acCheckBox Then
it just skips out continuously.

This is the VBA script as I have it:

Public Sub ETHUPDATE()
Dim myBox(1 To 88) 'you say you have a known number of checkboxes
Dim myName(1 To 88)
Dim myFrm As Form
Dim myCtl As Control
Dim myPointer As Integer

Set myFrm = Me
myPointer = 1

For Each myCtl In myFrm.Controls

If myCtl.ControlType = acCheckBox Then
'you could also play with myCtl.Name and other properties here if
'you like, perhaps using .....if left$(myCtl.name, 3) = "ETH" then ......
myBox(myPointer) = IIf(myCtl.Value, 5, 1)
myPointer = myPointer + 1
myName(myPointer) = myCtl.Name
End If

Next

Set myFrm = Nothing

End Sub

 

[izyrider]

hmmmm
don't see anything major wrong, but one minor adjustment (which wont fix it)

If myCtl.ControlType = acCheckBox Then
'you could also play with myCtl.Name and other properties here if
'you like, perhaps using .....if left$(myCtl.name, 3) = "ETH" then ......
myBox(myPointer) = IIf(myCtl.Value, 5, 1)
'myPointer = myPointer + 1 NOT HERE
myName(myPointer) = myCtl.Name
myPointer = myPointer + 1
End If

...also, i use DAO - know nothing of ADO so maybe this stuff is not possible in ADO.

i'll check.

izy

 

[izyrider]

which a version are you in?

what happens if you try

ctrl-G for the immediate window then
? acCheckBox
return
maybe the constant doesn't exist in your rev.

you could always experiment with replacing
If myCtl.ControlType = acCheckBox Then
with
if left$(myCtl.name, 3) = "ETH" then

i've got something to watch on telly for the next hour or so - i'll get back to you if it doesn't get fixed before then.



izy

 

[MsfStl]

I receive '106' when I go through the immeadiate window. I have no idea what that means.

I'll play around with it and see what I can do. Thank you very much for your assistance. BTW, I have Office2003. Thanks again.

 

[MsfStl]

Ok, new direction.

How about instead of calling the control I work directly with the database? That way I am not trying to change a boolean value from an Access control. I am just changing the data. Or should it matter?

 

[Mile-O]

Obviously since it is an "any or all" (there are 8 options) I had to create individual variables for each option.

Why would you obviously need to create individual tables?

 

[MsfStl]

Forgive my ignorance, I am a Statistician, not a 'Supreme' programmer

Forgive my ignorance, however, if I am analysing a series of questions, I cannot have multiple answers within each question (variable), additionally, I am not going to ask the same question 8 times, therefore I ask the question singularly and the respondent will provide a 'Yes' to each and any option that they are given, if they are true. This way I have 8 Dichotomous variables which I may analyize individually or plurally as classes, etc. Hence, for my puposes I require individual variables. Perhaps, you may enlighten me to the proper way of setting this up. I would be most grateful. It is with my most sincere humbleness that I ask your forgiveness and direction with this issue.

 

[izyrider]

of course you can play with a query looking at a table
myConvertedValue: iif(originalBoolean, 5, 1)
will return 5(originalBoolean=True) 1(originalBoolean=False).

if you want to persue collecting data from zillions of checkboxes on a form, example is attached. it is more or less the same as our attempts yesterday, plus some fluff messages to show what is going on.

izy

 

[MsfStl]

Thank you very much IZYRIDER! You have been most helpful! I have it working properly now.