Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax error on appending (Access 2000)

    'On appending a new row in the table Students i get a syntax error.Is it due to
    'the lack of some parts in the code ?

    Public Function NewStudent()
    Dim strSQL As String
    Dim f As Form
    Set f = Forms!frmEnrollment
    strSQL = "INSERT INTO TblStudents ( FirstName, SecondName, IDNumber ) " & _
    "VALUES (f!FirstName,f!SecondName f!IDNumber)"


    CurrentDb.Execute strSQL

    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Syntax error on appending (Access 2000)

    SQL doesn't know about forms or variables you set in VBA. You must include the values as literals in the string by concatenation. String values must be enclosed in quotes.
    <code>
    strSQL = "INSERT INTO tblStudents ( FirstName, SecondName, IDNumber ) " & _
    "VALUES (" & Chr(34) & f!FirstName & Chr(34) & ", " & _
    Chr(34) & f!SecondName & Chr(34) & ", " & f!IDNumber & ")"</code>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    Thank you . I will work on that and it is clear to me now

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    I am sorry i cannot do it. Even though i copied your text. I feel ashamed,i really cannot do it.
    I understand that you are not obliged to teach me, just to show a solution
    to the forum, but i really am at the end of my powers and i cannot finish
    what you have advised me.Please show me where i am wrong,i am sending an attachment


    Thank you

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Syntax error on appending (Access 2000)

    You can't attach a database directly to a reply. You will have to create a zip file of less than 100 KB and attach that. See <post#=401925>post 401925</post#> for instructions on posting a stripped down copy of a database.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    Here is the stripped copy of the database i failed to send

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    The form frmEnrollment is not present in the attached db.
    Without knowing what is on the form, I corrected the sql statement to have the same amount of fields and values in the sql statements.
    If you want more, post the db with the form frmEnrollment.
    Francois

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    Thank you very much for your reply.Your code works and i am very happy about that,and the new field in the table is populated,
    however only in case that all the fields in the form are filled.So for example, if the
    SecondName is missing, then the row is blank, and i have no new record.Is it possible to make
    the code more flexible and to paste only those records that exist ?

    Regards

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Syntax error on appending (Access 2000)

    You can insert code to test if SecondName has been filled in:

    <code>...
    Set f = Forms!frmEnrollment
    ' *** inserted lines
    If IsNull(f!Secondname) Then
    MsgBox "Please enter a second name!", vbExclamation
    f!Secondname.SetFocus
    Exit Function
    End If
    ' *** end of inserted lines
    strSQL = "INSERT INTO ...</code>

    If necessary, you can test for other missing data too - just copy the inserted lines and change the name of the field.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> P.S. it's Francois (male), not Francoise (female).

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    Bulletproof.I have now a bulletproof application.Thank you to all of you, Hans and Francois.
    I wish you a merry Christmas and happy New Year.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    I have a small additional question.The point is that the telephone numbers might not be
    known and should not be considered as obligatory.In that case,it would be better
    to execute the code, and ask for the number.I have tried the folloiwng:
    If IsNull(f!phone) Then
    On Error Resume Next
    End If

    but it didnt work, the row remains empty.
    Is it possible at all ?If not i must remove the phone numbers

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Syntax error on appending (Access 2000)

    I'm sorry, I don't understand what you want. If the telephone number is not required, you don't need to test for it, so you don't need code fot f!phone at all.

    Added - oh wait, you get an error message if you try to set an empty string. You will have to handle null values separately, like this:

    <code> strSQL = "INSERT INTO Students " & _
    "( FirstName, SecondName, IDNumber, notes, <!t>[work phone]<!/t> ) " & _
    "VALUES (" & Chr(34) & f!FirstName & Chr(34) & ", " & _
    Chr(34) & f!SecondName & Chr(34) & ", " & _
    Chr(34) & f!IDNumber & Chr(34) & ", "
    If IsNull(f!Notes) Then
    strSQL = strSQL & "null"
    Else
    strSQL = strSQL & Chr(34) & f!Notes & Chr(34)
    End If
    strSQL = strSQL & ", "
    If IsNull(f!<!t>[work phone]<!/t>) Then
    strSQL = strSQL & "null"
    Else
    strSQL = strSQL & Chr(34) & f!<!t>[work phone]<!/t> & Chr(34)
    End If
    strSQL = strSQL & ")"</code>

    Please read the code step by step and try to follow how the SQL string is assembled.

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    An alternative to Hans code is :
    <code>strSQL = "INSERT INTO Students ( FirstName, SecondName, IDNumber,notes,[work phone]) " & _
    "VALUES (" & Chr(34) & f!FirstName & Chr(34) & ", " & _
    IIf(IsNull(f!SecondName), "Null", Chr(34) & f!SecondName & Chr(34)) & ", " & _
    ....
    </code>
    Replace each value that may be empty with the iif function
    Francois

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error on appending (Access 2000)

    Dear Francois,

    Happy New Year and my best wishes to you.

    I use sucessfully your suggestion to avoid the Null values in my code.It works excellent but it gives me an error if the user
    inadverently sets the FirstName into commas.For example if the first name is Joe then it is OK but if it is "Joe"
    then i get a message that i have a syntax error.
    May i somehow tell the user to delete the commas ? These commas are a nuisance to me and it will be a good opportunity to make the user avoid them.

    regards

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Syntax error on appending (Access 2000)

    Just like the test for a missing name, you can also test for the presence of quotes (the "inverted commas" are called quotes):

    You can insert code to test if SecondName has been filled in:

    <code>...
    ' *** inserted lines
    If InStr(f!FirstName, Chr(34)) > 0 Then
    MsgBox "Don't use quotes!", vbExclamation
    f!FirstName.SetFocus
    Exit Function
    End If
    ' *** end of inserted lines
    ...</code>

    Or you could remove them if you prefer:

    <code>...
    ' *** inserted lines
    f!FirstName = Replace(f!FirstName, Chr(34), "")
    ' *** end of inserted lines
    ...</code>

    In both cases, you must insert this code before creating the SQL string.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •