MS access SQL problem

Xonet · Post by **Xonet** » 08 Jun 2008, 08:28

Hi guys, im hoping there's someone here with some SQL and VBA experience cuz im having a little problem, as always with unsolved problems, it doesn't seem logic.

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

nvm the innsert int tblLEERLINGTOETS as it isnt correct, the problem is i get an error "syntax error (missing operator) in query expression 'klasid=6IB'"
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

Post by **ayu** » 08 Jun 2008, 08:43

The part my brain is crashing at is this...

Code: Select all

klasid= " & strklasid & " "

First of all make sure that there is no space between klasid= and the '' , since for some reason some applications/servers gets a meltdown when that happens.

as in make it compact, when you did "klasid='6IB'" there were no spaces, but there is a space after every single char in klasid= " & strklasid & "

anyway just a thought... I don't do VBA, and it was a while since I worked with Access =)

Anyway give it a try, I have experienced similar issues with PHP and MySQL

Xonet · Post by **Xonet** » 08 Jun 2008, 10:41

Thanks for the advice, but it didn't work, visual basic isn't that hard on syntax errors and doesn't really care about spaces that much.

tried it anyway but still got the same error

just figured out i could allways do something like

Code: Select all

if dlookup ("klasid","tblLEERLING","leerlingid=!leerlingid")='6IB'
then
docmd.runsql "insert into...."
else
.movenext
end if

but that kinda takes away the entire thought of the query in the first place so my teacher probobly wont be ok with it

if i can't find a sollution at least i have that backup.