Error 3075

[schalkse]

Ok, tried realy allot, anybody can help find what is wrong with this code?
[Klantnummer] is a listbox on my form with all the customer numbers in it. in my table it is saved as text.
Thanks

Private Sub Klantnummer_Change()
Dim db As DAO.Database
Dim rs As DAO.Recordset
    
Dim strSQL As String
Dim strNaam As String
Dim strContact As String
Dim strAdres As String
Dim strPost As String
Dim strPlaats As String
Dim strLand As String
Dim strTel As String
Dim strFax As String
Dim strMail As String
Dim strCris As String
Dim strContactCris As String
Dim bActief As Boolean
Dim strGsm As String

strSQL = "SELECT tblKlanten.Klantnummer, tblKlanten.Klantnaam, tblKlanten.Contactpersoon, tblKlanten.Adres, tblKlanten.Postcode, tblKlanten.Plaatsnaam, tblKlanten.Land, tblKlanten.Tel, tblKlanten.Fax, tblKlanten.email, tblKlanten.Crissisnummer, tblKlanten.Contactpersoon_crissis, tblKlanten.Actief, tblKlanten.GSM" & _
"FROM tblKlanten " & _
"WHERE (tblKlanten.Klantnummer = '" & [Klantnummer] & "');"
Set db = CurrentDb

    Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until rs.EOF

        strNaam = rs!Klantnaam
        strContact = rs!Contactpersoon
        strAdres = rs!Adres
        strPost = rs!Postcode
        strPlaats = rs!Plaatsnaam
        strLand = rs!Land
        strTel = rs!Tel
        strFax = rs!Fax
        strMail = rs!email
        strCris = rs!Crissisnummer
        strContactCris = rs!Contactpersoon_crissis
        bActief = rs!Actief
        strGsm = rs!GSM
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    MsgBox strContact & strNaam & strAdres & strPost & strPlaats & strLand & strTel & strFax & strMail & strCris & strContactCris & bActief & strGsm
End Sub

 

[strep21]

Schalkse, (ruiters ???)
try to replace
"WHERE (tblKlanten.Klantnummer = '" & [Klantnummer] & "');"
by
"WHERE (tblKlanten.Klantnummer = " & [Klantnummer]);

 

[schalkse]

Yep, the schalke ruiters

Nope, now get compile error syntax error

 

[schalkse]

Nobody any clue as to why? Am realy stuck if this don't work.

thanks

 

[Jon K]

You need a space before the word FROM:-

strSQL = "SELECT tblKlanten.Klantnummer, tblKlanten.Klantnaam, tblKlanten.Contactpersoon, tblKlanten.Adres, tblKlanten.Postcode, tblKlanten.Plaatsnaam, tblKlanten.Land, tblKlanten.Tel, tblKlanten.Fax, tblKlanten.email, tblKlanten.Crissisnummer, tblKlanten.Contactpersoon_crissis, tblKlanten.Actief, tblKlanten.GSM" & _
" FROM tblKlanten " & _
"WHERE (tblKlanten.Klantnummer = '" & [Klantnummer] & "');"
.

 

[schalkse]

Ok, i found out after 1 day of trying that the sequel runs fine until i add tblKlanten.Tel. When the customer has no phonenumber, it's empty
when the recordset hits the rs!Tel it returns the error 94.

Anybody a clue how i can get rid of that?

Please

thanks

 

[Jon K]

Dim strTel As Variant

A string can't store a Null value.
.