Form coding question (1 Viewer)

[Peter Paul]

Greetings,
I am trying to do something which I think should be fairly straight-forward, but alas I am not getting it. I am creating a form for criminal incident reporting. One block of information, allows for multiple selection of up to three choices, out of a possible 8 options. When the user moves off of the last checkbox, I want to have the program check to make sure that no more than three have been selected.

This is one of the variations I have tried.

Private Sub OffenseInformation_subform_Exit(Cancel As Integer)
TotalCriminal = (Forms!Form!subform.[Field1] + [Field2] + [Field3] + [Field4] + [Field5] + [Field6] + [Field7] + [Field8])

If TotalCriminal <= -4 Then MsgBox "You are only allowed to choose three Criminal Activity Types, Please correct this", vbDefaultButton1

Field1.setfocus

End Sub

Any suggestions would be greatly appreciated.

Thanks yet again,
Peter Paul

 

[Pat Hartman]

Combining values in a single column will only lead to further problems. What are you going to do when you want to analyze the criminal incidents? And what if more than three apply? Once you have the possibility of more than one instance of something, you have a one-to-many relationship. You need to create a separate table to hold the criminal incident information. Add a subform to the mainform to enter the data.

The concept is similar to having an order with multiple items ordered. See the northwind.mdb (also solutions.mdb and frmsmp97.mdb) for examples of how these forms and tables are defined.

 

[Peter Paul]

Pat,
I agree that there is the possibility that more than three will apply. But, I am trying to comply with the FBI's requirements for NIBRS compliancy, and this is what they require.

 

[Chris RR]

This is another vote for Pat Hartman's recommendation. Even if you limit the user to three incidents (and there are ways...), you are going to save yourself many problems. Put the incidents in a separate table.

 

[Peter Paul]

Pat and Chris,
Ok, I will set up a subtable for this data. Still wondering what good way to limit the answers to three, however.

Thanks for your answers,
Peter

 

[skiphooper]

Hi,

Try This:
' *************************
Dim chkno As Integer
Dim Answer As Variant

chkno = 0

If Check01.Value = True Then
chkno = chkno + 1
End If
If Check02.Value = True Then
chkno = chkno + 1
End If
If Check03.Value = True Then
chkno = chkno + 1
End If
' ECT ECT ECT.......
If chkno <= 0 Then
Answer = MsgBox("There are NO Score Types Selected" & Chr(13) & Chr(10) _
& "You MUST Select at least 1 Score Type" _
& Chr(13) & Chr(10) _
& "And a Maxium of 3 to Continue", _
vbOKOnly & vbCritical, "No Criteria Selected")
If Answer = vbOK Then
chkno = 0
Exit Sub
End If
End If


If chkno > 3 Then
Answer = MsgBox("Too many selections checked", vbOKOnly & vbCritical, _
"Too Many Criteria Checked")
If Answer = vbOK Then
chkno = 0
Exit Sub
End If
End If
' **************************************
Just a Thought
Have the user click a command button when he wants the process started. if he never goes near the 8th check box, you process will never start.

Skip


[This message has been edited by skiphooper (edited 03-23-2001).]

[This message has been edited by skiphooper (edited 03-23-2001).]