check boxes for multple questions

  • Thread starter Thread starter Ezekiël
  • Start date Start date
E

Ezekiël

Hi,

I was wondering how i could use chechboxes in a form for multiple questions.

I have 2 tables, the second is for the storage of multiple questions. If one
chooses 1 or more checkboxes it should be stored in the second table. Also
there can only be 5 entries per person.

Can someone help me on this?
 
Ezekiel,

It is difficult to help you based on the information provided. Please
provide the structures and relationships of your tables, as well as a generic
description of the database's purpose.

Sprinks
 
Hi Sprinks,

The table structure looks like this (sql server tables):

table1:
Id int identity(1,1) primary key,
formnr int not null,
question1 int not null,
question2 int not null,
questionX........etc.

table2:
Id int identity(1,1) primary key,
formnr int null,
question3 int null

relationship is a 1-to-many on formnr.

The thing is that question3 has 5 options to choose from and one shouldn't
be allowed to insert more than 5.
Next problem is that i don't want to use a datasheetform to let one fill in
the anwsers, but rather a checkbox with the 5 options displayed. That way it
looks the same on the paperversion of the form.

I hope i provided more info this time.

Greetings,

Zeke
 
Hi, Ezekiel.

I'm still confused as to the purpose of this database. If it is to store
the responses of multiple people to a set of questions, then my first
impression is that is not normalized. A many-to-many relationship seems to
exist between Respondants and Responses, that would normally be implemented
through 2 1-to-many relationships.

A checkbox can either be bound to a boolean or a numeric field such that it
stores
-1 when it's checked and 0 when unchecked, or it can be unbound. Although
I'm still not clear on what you're trying to do, my guess is that you want to
insert records in table 2 for every box on the form that is checked, and that
you want to prohibit the user from checking more than 5 boxes.

If this is the case, I think the way to implement it is to either use a
series of unbound checkboxes and a "Store Answers" command button. When
pressed, loop through the form controls, adding up the number of responses.
If it totals more than five, display a message and return the user to the
form, otherwise, repeat the loop and insert a record based on whether it's
checked or not. There are more sophisticated algorithms to implement this,
but since the number of passes is small, it won't make a hill of beans
difference.

You can use the Tag property of each checkbox control to store the
appropriate value to be inserted.

' Command button OnClick event procedure
Dim ctl As Control
Dim intCheckCount As Integer

' Initialize counter
intCheckCount = 0

' Count up check boxes
For Each ctl in Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
intCheckCount = intCheckCount + 1
End If
End If
Next ctl

If intCheckCount > 5 Then
MsgBox ("Please check only five boxes")
Me!ControlNameToPlaceCursor.SetFocus
Exit Sub
End If

' Insert records
For Each ctl in Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
' SQL Insert statement here including reference to ctl.Tag
End If
End If
Next ctl

Hope that helps, but if I'm way off base on what you're trying to do, please
post a generic description of the database's purpose, and what the form looks
like.

Sprinks
 
Hi Sprinks,

Sorry about confusing you, but you got the right picture which i want to do.

For the inserting part, is it really neccesary to hardcode a jet sql
statement or do you also know a method to use sql server statements. If i'm
not making any sense, please say so. I was thinking ado, but my skills are
lacking to accomplish that.

Anyway thanks for the effort.
 
Hi, Jason.

I don't know what an SQL Server statement is, as I'm not an IT professional,
just an engineer with a lot of Access experience. But I CAN help you with
the syntax of the Insert statement, if you'll post the structure of the
target table, with the name and type of each field.
 
Back
Top