I'm not sure I understand correctly, but maybe this will help you.
I couldn't come up with a single SQL query, I'm getting better at SQL, but still struggling. I couldn't figure out how to include the PersonID value from the form, so I used VBA code.
Also, there are several issues I think you should remedy: you should have "Option Explicit" as the 2nd line in every Module.
You used "Name" as a variable. "Name" is a reserved word in Access and shouldn't be used for an object name.
Since I an using O365, I had to add a reference to be able to use "dbFailOnError" in code.
I think you are making a lot of work for yourself by using unbound forms, but.......
1) For the button on form "f_qry_PersonRoleCodeStatus" (still haven't figured out your naming scheme), this is the code for the button "Befehl39_Click()" I came up with:
Code:
Private Sub Befehl39_Click()
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim RC As Long 'recordset record count
Dim lngPerson As Long
Dim lngRolle As Long
Dim lngCode As Long
If Not IsNumeric(Me.cboName) Then
'missing name
MsgBox "Please select a Name, then try again."
Exit Sub
End If
If Not IsNumeric(Me.cboRolle) And Not IsNumeric(Me.cboScope) Then
'both null so do nothing
MsgBox "Please select Rolle and/or Scope, then try again."
Exit Sub
End If
Set d = CurrentDb
lngPerson = Me.cboName
If IsNumeric(Me.cboRolle) And Not IsNumeric(Me.cboScope) Then
'Me.cboRolle Not Null - Me.cboScope Is Null
sSQL = "SELECT tbl_Criterion.CriterionID"
sSQL = sSQL & " FROM tbl_Criterion"
sSQL = sSQL & " WHERE (tbl_Criterion.RoleID =" & Me.cboRolle & ") AND (IsNull(tbl_Criterion.CodeID));"
ElseIf Not IsNumeric(Me.cboRolle) And IsNumeric(Me.cboScope) Then
'Me.cboRolle Is Null - Me.cboScope Is Not Null
sSQL = "SELECT tbl_Criterion.CriterionID"
sSQL = sSQL & " FROM tbl_Criterion"
sSQL = sSQL & " WHERE (tbl_Criterion.CodeID = " & Me.cboScope & ") AND (IsNull([tbl_Criterion].[RoleID]));"
ElseIf IsNumeric(Me.cboRolle) And IsNumeric(Me.cboScope) Then
'Me.cboRolle Is Not Null - Me.cboScope Is Not Null
sSQL = "SELECT tbl_Criterion.CriterionID"
sSQL = sSQL & " FROM tbl_Criterion"
sSQL = sSQL & " WHERE tbl_Criterion.RoleID = " & Me.cboRolle & " AND tbl_Criterion.CodeID = " & Me.cboScope & ";"
End If
If Len(sSQL) > 0 Then
' Debug.Print sSQL
Set r = d.OpenRecordset(sSQL)
If Not r.BOF And Not r.EOF Then
r.MoveLast
r.MoveFirst
RC = r.RecordCount
Do While Not r.EOF
sSQL = "INSERT INTO tbl_Question (PersonID, CriterionID)"
sSQL = sSQL & " VALUES (" & lngPerson & "," & r!CriterionID & ");"
' Debug.Print sSQL
d.Execute sSQL, dbFailOnError
' Debug.Print r!CriterionID
r.MoveNext
Loop
End If
End If
'clean up
On Error Resume Next
r.Close
Set r = Nothing
Set d = Nothing
End Sub
Seems to insert records correctly for me........
2) Still thinking about it.......
EDIT: I would not use two checkboxes for Pass/Fail. (tblQuestions)
What if both checkboxes get checked? I would use a combo box with "Pass", "Fail" as options. Or you could use a Value List of "0;Fail,1,Pass" if you wanted to save values (like tables) instead of text.