Hiding controls based on number of questions

[Martin]

Hi,

I'm using Access 2003. I have a grades Dbase to keep track of student
grades. I have quizzes, written tests and performance exams to keep track
of. My problem is the written exam. Over 10 different lessons, the written
exams range from 10 questions to 70 question. I would like to write code
that hides the unused fields if greater than the number of questions ask. To
be flexable, I don't want to hard code for just the 10 lessons. I do have a
table that holds the number of questions.

Is there a way to cycle through the different question fields and hide then?
Here is how I am doing it that works and what I am trying to reduce.

iwritten = myset!WXMax 'WXMax = number of questions per exam.
If iwritten >= 1 Then
Me.Q1A = mysetwa![Q1]
Me.Q1A.Visible = True
Else
Me.Q1A.Visible = False
End If
If iwritten >= 2 Then
Me.Q2A = mysetwa![Q2]
Me.Q2A.Visible = True
Else
Me.Q2A.Visible = False
End If
etc. up to question 70

would just like to us a For loop to do the job but having trouble
referencing the controls for question 1, 'Q1', to 70, 'Q70".

iwritten = myset!WXMax
For x = 1 To iwritten
If iwritten >= x Then
me.Controls!.Q & x & A = mysetwa![Q & x & ] 'does not like
endif
Next x

 

[Steve Sanford]

It looks like you have a table with at least 70 fields (Q1, Q2, ...). If so,
then your table structure is, well, wrong. You should have one field for the
questions, with 70 rows.

As an example, if you had a table with 3 fields, "ID" - autonumber,
"Question" - text and "QSelected" - boolean. You could have 500 questions
(rows) and select 15 by setting "QSelected" to TRUE. By opening a recordset,
you could loop thru the recordset, setting the 15 unbound controls on the
form visible and the control source of the text box to the field "Question".

Anyway, back to your question. I didn't test these, but they should run.

OPTION 1:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

For i = 1 To 70
If i >= iwritten Then
'hide
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Else
'show
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
End If
Next




OPTION 2:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

'hide
For i = 1 To 70
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Next

'show
For i = 1 To iwritten
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
Next


Again.... this is air code!

HTH

 

[Martin]

Steve,

Thank you for your response. I tried your code and I get an error stating
the item does not exist in the collection. My table has 1 record per student
for up to 70 questions each. The fields in the table are 'Q1', 'Q2', etc.
Besides my table structure problems, I played around with what you suggested
and got no where.

When I debug.print Me("Q" & i & "A") I get Null because the field has not
been populated yet. When I debug.print mysetwa!Fields("Q" & i) I get item
not found in this collection. Field 'Q1' is a text field 1 character wide.

It took me a while to write 700 lines of code to take care of the 70
possible querstions but it does work. I just thought there should be an
easier way to code it.

It looks like you have a table with at least 70 fields (Q1, Q2, ...). If so,
then your table structure is, well, wrong. You should have one field for the
questions, with 70 rows.

As an example, if you had a table with 3 fields, "ID" - autonumber,
"Question" - text and "QSelected" - boolean. You could have 500 questions
(rows) and select 15 by setting "QSelected" to TRUE. By opening a recordset,
you could loop thru the recordset, setting the 15 unbound controls on the
form visible and the control source of the text box to the field "Question".

Anyway, back to your question. I didn't test these, but they should run.

OPTION 1:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

For i = 1 To 70
If i >= iwritten Then
'hide
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Else
'show
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
End If
Next




OPTION 2:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

'hide
For i = 1 To 70
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Next

'show
For i = 1 To iwritten
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
Next


Again.... this is air code!

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,

I'm using Access 2003. I have a grades Dbase to keep track of student
grades. I have quizzes, written tests and performance exams to keep track
of. My problem is the written exam. Over 10 different lessons, the written
exams range from 10 questions to 70 question. I would like to write code
that hides the unused fields if greater than the number of questions ask. To
be flexable, I don't want to hard code for just the 10 lessons. I do have a
table that holds the number of questions.

Is there a way to cycle through the different question fields and hide then?
Here is how I am doing it that works and what I am trying to reduce.

iwritten = myset!WXMax 'WXMax = number of questions per exam.
If iwritten >= 1 Then
Me.Q1A = mysetwa![Q1]
Me.Q1A.Visible = True
Else
Me.Q1A.Visible = False
End If
If iwritten >= 2 Then
Me.Q2A = mysetwa![Q2]
Me.Q2A.Visible = True
Else
Me.Q2A.Visible = False
End If
etc. up to question 70

would just like to us a For loop to do the job but having trouble
referencing the controls for question 1, 'Q1', to 70, 'Q70".

iwritten = myset!WXMax
For x = 1 To iwritten
If iwritten >= x Then
me.Controls!.Q & x & A = mysetwa![Q & x & ] 'does not like
endif
Next x

 

[Steve Sanford]

Sorry, my bad... I did say air code. I was doing cut and paste.... and.....
(see below for corrected code)

On "The Access Web" there is an example of cycling through controls with
common names (http://www.mvps.org/access/forms/frm0003.htm).

So this works : Me("Q" & i & "A")

'------------------------------------
Here is the corrected code:

OPTION 1:
--------------
Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

For i = 1 To 70
If i >= iwritten Then
'hide
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Else
'show
Me("Q" & i & "A") = mysetwa!Fields("Q" & i )
Me("Q" & i & "A").Visible = True
End If
Next


OPTION 2:
------------
Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

'hide
For i = 1 To 70
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Next

'show
For i = 1 To iwritten
Me("Q" & i & "A") = mysetwa!Fields("Q" & i)
Me("Q" & i & "A").Visible = True
Next
'------------------------------------



I'm still trying to understand ...... you have 1 record per student with 70
questions? And 10 lessons? So 10 records per student with 70 questions in
each record???

So does that mean for say, Lesson 1, that each student can/will have
different questions?

If you want to be able to have more than 10 lessons, you really need to
normalize your table structure.

Anyway, try the corrected code and let me know....

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,

Thank you for your response. I tried your code and I get an error stating
the item does not exist in the collection. My table has 1 record per student
for up to 70 questions each. The fields in the table are 'Q1', 'Q2', etc.
Besides my table structure problems, I played around with what you suggested
and got no where.

When I debug.print Me("Q" & i & "A") I get Null because the field has not
been populated yet. When I debug.print mysetwa!Fields("Q" & i) I get item
not found in this collection. Field 'Q1' is a text field 1 character wide.

It took me a while to write 700 lines of code to take care of the 70
possible querstions but it does work. I just thought there should be an
easier way to code it.

It looks like you have a table with at least 70 fields (Q1, Q2, ...). If so,
then your table structure is, well, wrong. You should have one field for the
questions, with 70 rows.

As an example, if you had a table with 3 fields, "ID" - autonumber,
"Question" - text and "QSelected" - boolean. You could have 500 questions
(rows) and select 15 by setting "QSelected" to TRUE. By opening a recordset,
you could loop thru the recordset, setting the 15 unbound controls on the
form visible and the control source of the text box to the field "Question".

Anyway, back to your question. I didn't test these, but they should run.

OPTION 1:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

For i = 1 To 70
If i >= iwritten Then
'hide
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Else
'show
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
End If
Next




OPTION 2:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

'hide
For i = 1 To 70
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Next

'show
For i = 1 To iwritten
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
Next


Again.... this is air code!

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,

I'm using Access 2003. I have a grades Dbase to keep track of student
grades. I have quizzes, written tests and performance exams to keep track
of. My problem is the written exam. Over 10 different lessons, the written
exams range from 10 questions to 70 question. I would like to write code
that hides the unused fields if greater than the number of questions ask. To
be flexable, I don't want to hard code for just the 10 lessons. I do have a
table that holds the number of questions.

Is there a way to cycle through the different question fields and hide then?
Here is how I am doing it that works and what I am trying to reduce.

iwritten = myset!WXMax 'WXMax = number of questions per exam.
If iwritten >= 1 Then
Me.Q1A = mysetwa![Q1]
Me.Q1A.Visible = True
Else
Me.Q1A.Visible = False
End If
If iwritten >= 2 Then
Me.Q2A = mysetwa![Q2]
Me.Q2A.Visible = True
Else
Me.Q2A.Visible = False
End If
etc. up to question 70

would just like to us a For loop to do the job but having trouble
referencing the controls for question 1, 'Q1', to 70, 'Q70".

iwritten = myset!WXMax
For x = 1 To iwritten
If iwritten >= x Then
me.Controls!.Q & x & A = mysetwa![Q & x & ] 'does not like
endif
Next x

 

[Martin]

Steve,

Thanks a lot. Great help. Had to due I slight mod. Still got same error
as before. Found this line works great.

Me("Q" & i & "A") = mysetwa("Q" & i)

Yes I know my normalization is bad. I read and read and still have trouble
with that part. My grades dbase is in 4 different sections of the school I
work at and now they want to make it so everyone uses the same one.

I had to replace the old dbase because it was so confusing. over 70
different tables which were all linked to one main table instead of what I
learned in college. Redundant data all over the place and #$*% just did not
work. Basically started from scratch with 40 tables but only 4 in the
relationship. Anyway, thank you again for your time and help.

Thank you. Thank you. Thank you.

Sorry, my bad... I did say air code. I was doing cut and paste.... and.....
(see below for corrected code)

On "The Access Web" there is an example of cycling through controls with
common names (http://www.mvps.org/access/forms/frm0003.htm).

So this works : Me("Q" & i & "A")

'------------------------------------
Here is the corrected code:

OPTION 1:
--------------
Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

For i = 1 To 70
If i >= iwritten Then
'hide
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Else
'show
Me("Q" & i & "A") = mysetwa!Fields("Q" & i )
Me("Q" & i & "A").Visible = True
End If
Next


OPTION 2:
------------
Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

'hide
For i = 1 To 70
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Next

'show
For i = 1 To iwritten
Me("Q" & i & "A") = mysetwa!Fields("Q" & i)
Me("Q" & i & "A").Visible = True
Next
'------------------------------------



I'm still trying to understand ...... you have 1 record per student with 70
questions? And 10 lessons? So 10 records per student with 70 questions in
each record???

So does that mean for say, Lesson 1, that each student can/will have
different questions?

If you want to be able to have more than 10 lessons, you really need to
normalize your table structure.

Anyway, try the corrected code and let me know....

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,

Thank you for your response. I tried your code and I get an error stating
the item does not exist in the collection. My table has 1 record per student
for up to 70 questions each. The fields in the table are 'Q1', 'Q2', etc.
Besides my table structure problems, I played around with what you suggested
and got no where.

When I debug.print Me("Q" & i & "A") I get Null because the field has not
been populated yet. When I debug.print mysetwa!Fields("Q" & i) I get item
not found in this collection. Field 'Q1' is a text field 1 character wide.

It took me a while to write 700 lines of code to take care of the 70
possible querstions but it does work. I just thought there should be an
easier way to code it.

It looks like you have a table with at least 70 fields (Q1, Q2, ...). If so,
then your table structure is, well, wrong. You should have one field for the
questions, with 70 rows.

As an example, if you had a table with 3 fields, "ID" - autonumber,
"Question" - text and "QSelected" - boolean. You could have 500 questions
(rows) and select 15 by setting "QSelected" to TRUE. By opening a recordset,
you could loop thru the recordset, setting the 15 unbound controls on the
form visible and the control source of the text box to the field "Question".

Anyway, back to your question. I didn't test these, but they should run.

OPTION 1:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

For i = 1 To 70
If i >= iwritten Then
'hide
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Else
'show
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
End If
Next




OPTION 2:

Dim i As Integer

'WXMax = number of questions per exam
iwritten = myset!WXMax

'hide
For i = 1 To 70
Me("Q" & i & "A") = ""
Me("Q" & i & "A").Visible = False
Next

'show
For i = 1 To iwritten
Me("Q" & i & "A") = mysetwa!Fields("Q" & i & "A")
Me("Q" & i & "A").Visible = True
Next


Again.... this is air code!

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Hi,

I'm using Access 2003. I have a grades Dbase to keep track of student
grades. I have quizzes, written tests and performance exams to keep track
of. My problem is the written exam. Over 10 different lessons, the written
exams range from 10 questions to 70 question. I would like to write code
that hides the unused fields if greater than the number of questions ask. To
be flexable, I don't want to hard code for just the 10 lessons. I do have a
table that holds the number of questions.

Is there a way to cycle through the different question fields and hide then?
Here is how I am doing it that works and what I am trying to reduce.

iwritten = myset!WXMax 'WXMax = number of questions per exam.
If iwritten >= 1 Then
Me.Q1A = mysetwa![Q1]
Me.Q1A.Visible = True
Else
Me.Q1A.Visible = False
End If
If iwritten >= 2 Then
Me.Q2A = mysetwa![Q2]
Me.Q2A.Visible = True
Else
Me.Q2A.Visible = False
End If
etc. up to question 70

would just like to us a For loop to do the job but having trouble
referencing the controls for question 1, 'Q1', to 70, 'Q70".

iwritten = myset!WXMax
For x = 1 To iwritten
If iwritten >= x Then
me.Controls!.Q & x & A = mysetwa![Q & x & ] 'does not like
endif
Next x