Checkboxes selected on subform based on main form selection

[Guest]

I am using Access 2003. I have a main form (frmAnalysis) based on a table
called tblAnalysis. FrmAnalysis has a bound control called EquipmentName.

The main form also has a button that opens a subform (subHazard) based on a
table called tblHazard. TblAnalysis and tblHazard are related through the
AnalysisID field.

The subHazard form consists of 32 bound check box controls named A, B, C, up
to FF. When this subHazard form opens, I want certain checkboxes selected
based on the EquipmentName selected on the frmAnalysis main form.

To know which checkboxes should be selected for each EquipmentName, in an
unrelated table called tblEqupment I have fields named EquipName,
DefaultHazard1, DefaultHazard2, etc. up to DefaultHazard6. In the
DefaultHazard1 field I might enter D, in DefaultHazard2 field I might enter
F, and so on.

Basically, what I am trying to do is if EquipmentName = “Pump1†on
frmAnalysis then look up the default Hazards for Pump1 in tblEquipment and
then open the subHazard form to a new record with the corresponding
checkboxes already selected. I want to allow the user to uncheck the boxes
again if necessary.

Some equipment may have no default hazards; others may have up to 6.

Any suggestions would be greatly appreciated.

 

[Guest]

Here is a concept that may work for you.
Use the subforms current event. If it is a new record, open the unrelated
table filtered on the equipment name, which, if I am to understand, would
return 1 record. Then you have the six default fields. If I read your post
correctly, each of these fields contain the name of the check box control you
want checked. If that is correct, you can look at the field names and check
the boxes as appropriate.
This is strictly air code and has no guarantee, but I think it may be a
starting place:

Private Sub Form_Current()
Dim rst As Recorset

If Me.NewRecord then
Set rst = Currentdb.OpenRecordset("SELECT * FROM tblEquipment WHERE
EquipName = '" & Me.Parent.txtEquipmentName & "'
If Not IsNull(rst!DefaultHazard1) Then
Me.Controls(rst!DefaultHazard1) = True
End If
.......
If Not IsNull(rst!DefaultHazard6) Then
Me.Controls(rst!DefaultHazard6) = True
End If
rst.Close
Set rst = Nothing
End If

Let me know how this works out for you.

 

[Guest]

Thank you so much for your help. I didn't use your code exactly the way you
said but it was the answer I needed. Rather than putting the code in the On
Current of the subform, I included it in the On Click code of the main form
button that opens the subform. I'm not sure if I am using my terminology
correctly--my "subform" may not be what Access really considers a "subform".
And after reading your answer, I have a feeling that I should be opening my
"subform" in a different manner than what I currently am. But I believe the
way I am doing it along with the code you supplied, I will accomplish what I
want the forms to do. I am including the code I entered in the On Click
event of the main form's button for your information. If you should notice
some grave coding errors on my part, I'd appreciate your comments. I am a
novice at this VBA coding and basically piece things together based on what I
see in other databases, along with code from wonderful people like you who
volunteer your help. Here's my code:

Private Sub btnOpenHazardsForm_Click()
On Error GoTo Err_btnOpenHazardsForm_Click

Dim stDocName As String
Dim rst As Recordset

stDocName = "subfrmHazards"
lgAnalysisID = Me.AnalysisID.Value

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblEquipment WHERE
EquipmentName = '" & Me.EquipmentName & "'")

DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![subfrmHazards].AnalysisID = lgAnalysisID
DoCmd.GoToControl "[HazardID]"

If Not IsNull(rst!DefaultHazard1) Then
Forms![subfrmHazards].Controls(rst!DefaultHazard1) = True
End If

If Not IsNull(rst!DefaultHazard2) Then
Forms![subfrmHazards].Controls(rst!DefaultHazard2) = True
End If

If Not IsNull(rst!DefaultHazard3) Then
Forms![subfrmHazards].Controls(rst!DefaultHazard3) = True
End If

If Not IsNull(rst!DefaultHazard4) Then
Forms![subfrmHazards].Controls(rst!DefaultHazard4) = True
End If

If Not IsNull(rst!DefaultHazard5) Then
Forms![subfrmHazards].Controls(rst!DefaultHazard5) = True
End If

If Not IsNull(rst!DefaultHazard6) Then
Forms![subfrmHazards].Controls(rst!DefaultHazard6) = True
End If

rst.Close
Set rst = Nothing

Exit_btnOpenHazardsForm_Click:
Exit Sub

Err_btnOpenHazardsForm_Click:
MsgBox Err.Description
Resume Exit_btnOpenHazardsForm_Click

End Sub
------------