I'll get to the point
this is an app for my databases teacher to manage all tests. so the tables i have (that are relevant to the question) are tblLEERLING (all students are in here with their nr, names, and class) tblTOETS (all tests are in here with their nr, date, title,...) and i have tblLEERLINGTOETS wich is a linkage table between students and their tests, since each student has more then 1 test and each test is made by more then 1 student, so you can see that tabled as an instance of each test for each student.
now i have a form to make a new test (with a combobox cboklassen where the class is selected, and all other textfields and such for the test info)
so what i do then is i insert the test, take the dmax from the tabel tblTOETS to get the last inserted test and then comes the tricky stuff
i made this recordet
Code: Select all
Private Sub cmdtoetsbevestigen_Click()
Dim inttoetsid As Integer
If mintbevestigen = 1 Then
DoCmd.RunSQL "INSERT INTO tblTOETS (toets,datum,categorieid,klasid) VALUES (forms!frmtoetsen!txttitel,forms!frmtoetsen!txtdatum,forms!frmtoetsen!cbocategorie,forms!frmtoetsen!cboklassen)"
inttoetsid = DMax("Toetsid", "tblTOETS")
Dim cnn As New ADODB.Connection
Dim rstado As New ADODB.Recordset
Set cnn = CurrentProject.Connection
Dim strklasid As String
strklasid = CStr(cboklassen)
rstado.Open "SELECT * FROM tblLEERLING WHERE klasid= " & strklasid & " ", cnn, adOpenForwardOnly, adLockPessimistic
With rstado
Do Until .EOF
DoCmd.RunSQL "INSERT INTO tblLEERLINGTOETS (leerlingid,toetsid) VALUES (leerlingid, " & inttoetsid & ")"
.MoveNext
Loop
End With
rstado.Close
Else
End If
End Sub
so the problem is the query for my recordset
"SELECT * FROM tblLEERLING WHERE klasid= " & strklasid & " "
when i set klasid='6IB' then it is being executed correctly
when i just used klasid=forms!frmtoetsen!cboklassen is didn't work either
when i use the debugger it does say strklasid='6IB' so it goes beyond my reasoning as to why it doesn't execute this correctly
hope someone can help
in return, eternal gratitude