Syntax error on appending (Access 2000)

Topic

New Reply

‘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

Reply | Quote

Replies

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.

strSQL = "INSERT INTO tblStudents ( FirstName, SecondName, IDNumber ) " & _
"VALUES (" & Chr(34) & f!FirstName & Chr(34) & ", " & _
Chr(34) & f!SecondName & Chr(34) & ", " & f!IDNumber & ")"

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 for instructions on posting a stripped down copy of a database.

Reply | Quote

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 for instructions on posting a stripped down copy of a database.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

...
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 ...

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

P.S. it’s Francois (male), not Francoise (female).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

strSQL = "INSERT INTO Students " & _
"( FirstName, SecondName, IDNumber, notes, [work phone] ) " & _
"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![work phone]) Then
strSQL = strSQL & "null"
Else
strSQL = strSQL & Chr(34) & f![work phone] & Chr(34)
End If
strSQL = strSQL & ")"

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

Reply | Quote

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:

strSQL = "INSERT INTO Students " & _
"( FirstName, SecondName, IDNumber, notes, [work phone] ) " & _
"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![work phone]) Then
strSQL = strSQL & "null"
Else
strSQL = strSQL & Chr(34) & f![work phone] & Chr(34)
End If
strSQL = strSQL & ")"

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

Reply | Quote

An alternative to Hans code is :
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)) & ", " & _
....

Replace each value that may be empty with the iif function

Reply | Quote

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

Reply | Quote

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:

...
' *** 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
...

Or you could remove them if you prefer:

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

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

Reply | Quote

Dear Hans,

Happy New Year to you and my best wishes !

I have tested it and of course it is perfect now.It is bulletproof indeed.
How can i open the second form and go to the record just transfreed with my command?
Shall i go to the last record, or may be not?

after the execution of the sql i have the following lines:

CurrentDb.Execute strSQL
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.Close acForm, “frm1”
DoCmd.OpenForm “frmStudents”

Reply | Quote

Chestita nova godina!

You don’t know whether the record just added will be the last one in the other form. You can pass the IDNumber to the other form.

CurrentDb.Execute strSQL
Dim strID As String
strID = f!IDNumber
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.OpenForm FormName:=”frmStudents”, OpenArgs:=strID
DoCmd.Close acForm, “frm1”

In the On Load event of frmStudents, check if an IDNumber is passed in OpenArgs, and if so, move to the corresponding record:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.Recordset.FindFirst “IDNumber = ” & Chr(34) & Me.OpenArgs & Chr(34)
End If
End Sub

Reply | Quote

Thanks again.

Reply | Quote

Thanks again.

Reply | Quote

Dear Hans

will you help me out? The problem is that i have diferent names in the tables.The ID number in the

table enrollment,on which the form frm1 is based, is rollid,
while the id number in the table students is studentid.
i receive the message type mismatch.

regards

CurrentDb.Execute strSQL
Dim strID As String
strID = f!rollid
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.OpenForm FormName:=”frmStudents”, OpenArgs:=strID
DoCmd.Close acForm, “frm1”

In the form frmStudents:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.Recordset.FindFirst “studentid = ” & Chr(34) & Me.OpenArgs & Chr(34)
End If
End Sub

Reply | Quote

This is confusing – in the database you attached earlier in this thread, the field was named IDNumber in both tables.

What is the data type of the rollid field in the enrollment table? (Open the table in design view to check this)
What is the data type of the studentid field in the students table?

Reply | Quote

I am sorry for the confusion.I hade to change the name.Howevere both types in both tables are autonumbers.
If it is not possible i will still use the code i have.So it is not critical but preferable to open the second form on that point

regards

Reply | Quote

I am sorry for the confusion.I hade to change the name.Howevere both types in both tables are autonumbers.
If it is not possible i will still use the code i have.So it is not critical but preferable to open the second form on that point

regards

Reply | Quote

Since an AutoNumber field is assigned automatically by Access, and cannot be modified by the user, the StudentID field in the Students table should not be an AutoNumber field, but a Number (Long Integer) field.

In your previous examples, the fields you used were text fields. The Chr(34)’s in the code surround the text value with quotes. For a number field, you must not use these.

Reply | Quote

Since an AutoNumber field is assigned automatically by Access, and cannot be modified by the user, the StudentID field in the Students table should not be an AutoNumber field, but a Number (Long Integer) field.

In your previous examples, the fields you used were text fields. The Chr(34)’s in the code surround the text value with quotes. For a number field, you must not use these.

Reply | Quote

This is confusing – in the database you attached earlier in this thread, the field was named IDNumber in both tables.

What is the data type of the rollid field in the enrollment table? (Open the table in design view to check this)
What is the data type of the studentid field in the students table?

Reply | Quote

Dear Hans

will you help me out? The problem is that i have diferent names in the tables.The ID number in the

table enrollment,on which the form frm1 is based, is rollid,
while the id number in the table students is studentid.
i receive the message type mismatch.

regards

CurrentDb.Execute strSQL
Dim strID As String
strID = f!rollid
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.OpenForm FormName:=”frmStudents”, OpenArgs:=strID
DoCmd.Close acForm, “frm1”

In the form frmStudents:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.Recordset.FindFirst “studentid = ” & Chr(34) & Me.OpenArgs & Chr(34)
End If
End Sub

Reply | Quote

Chestita nova godina!

You don’t know whether the record just added will be the last one in the other form. You can pass the IDNumber to the other form.

CurrentDb.Execute strSQL
Dim strID As String
strID = f!IDNumber
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.OpenForm FormName:=”frmStudents”, OpenArgs:=strID
DoCmd.Close acForm, “frm1”

In the On Load event of frmStudents, check if an IDNumber is passed in OpenArgs, and if so, move to the corresponding record:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.Recordset.FindFirst “IDNumber = ” & Chr(34) & Me.OpenArgs & Chr(34)
End If
End Sub

Reply | Quote

Dear Hans,

Happy New Year to you and my best wishes !

I have tested it and of course it is perfect now.It is bulletproof indeed.
How can i open the second form and go to the record just transfreed with my command?
Shall i go to the last record, or may be not?

after the execution of the sql i have the following lines:

CurrentDb.Execute strSQL
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.Close acForm, “frm1”
DoCmd.OpenForm “frmStudents”

Reply | Quote

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

Reply | Quote

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

...
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 ...

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

P.S. it’s Francois (male), not Francoise (female).

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote