Checkbox for multi select vba help

[NWGal]

I have a main form "contact", and a subform "roles". The subform has checkboxes for each possible role. I have the following code set in the oncurrent of the main form, which works perfectly for adding the contact id with each selected role to a junction table. The problem I am having is that when I open the subform, (it's on a 2nd tab) the check boxes are not clickable until I either record or move to the next one and then come back. How do I fix this?

FYI, I have a button on another form that opens this main form to view or edit current records but not add new, and another button that allows me to open this same form to make a new record. When I open to view or edit there is no problem because I'm acting on data/records that already exist. The issue only effects when I enter a new contact and want to select his/her roles.

Private Sub Form_Current()
Dim strSQL As String
'clear the temp table, if it exists, to avoid error
strSQL = "DELETE * FROM tblTempCR"
CurrentDb.Execute strSQL, dbFailOnError
'check to see if this is a new record
'if not, check existing data
If Me.NewRecord Then
'disable the subform
Me.sbfrmRole.Enabled = False
'create SQL for the make table query
strSQL = "INSERT INTO tblTempCR (RoleID, RoleName, RoleSelected) SELECT "
strSQL = strSQL & "RoleID, RoleName, False FROM tblRole"
Else
'enable the subform
Me.sbfrmRole.Enabled = True
'recreate the query to find all roles that exist for this person
strSQL = "SELECT ConID, RoleID FROM tblConRole"
strSQL = strSQL & " WHERE ConID=" & Me.ConID
CurrentDb.QueryDefs("qryConRole").SQL = strSQL
'create SQL for the make table query
strSQL = "INSERT INTO tblTempCR (RoleID, RoleName, RoleSelected) SELECT"
strSQL = strSQL & " tblRole.RoleID, tblRole.RoleName, qryConRole.RoleID"
strSQL = strSQL & " FROM tblRole LEFT JOIN qryConRole ON "
strSQL = strSQL & " tblRole.RoleID = qryConRole.RoleID"
End If
'execute the query to fill the temp table
CurrentDb.Execute strSQL, dbFailOnError
'requery the subform to display the correct data
Me.sbfrmRole.Requery
End Sub

[HansV]

The code disables the subform when the main form is on a new record. You could enable it as soon as the user starts entering data in a new record:

Code: Select all

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.sbfrmRole.Enabled = True
End Sub

[NWGal]

Would I put this on the subform beforeinsert event, or the mainform?

[HansV]

It is intended for the main form.

[NWGal]

It is intended for the main form.

Awesome! That did the trick. Thanks