Syntax Error.

[lithium]

I have a program that essentially documents repair requests and when the user submits new "Fix it tickets" sometimes after they fill out the form and go to press the "Add" button it will say "Syntax Error" and have their large string of words they typed in the "Comments" box in the error window? I added error handling but essentially it just skips the error and continues, is this right or is it erroring because they enter like special characters such as: ();:,.""''?!

[Missinglinq]

Special characters are often problematical in Access, but if we're to help you we really have to have more information than you've posted. When you say 'Add' button are you referring to the native add new record button, or a custom button? If the latter, what code is behind the button?

Linq ;0)>

[RichXB]

If you could identify a common factor that it's erroring on then that would help, but special characters are a good bet. I use this to sub them for hyphens where I use filenames in one of my projects.

In your case you'd want to include apostrophes and so on in the replace string text.

                'Remove invalid characters from the filename                
Replacestring = "/?<>\|:;*"
For R = 1 To Len(Replacestring)
PassedFilename = Replace(PassedFilename, Mid(Replacestring, R, 1), "-")
Next R

[lithium]

Yeah the "Add" button is used to push a new record into the system using the information they submitted in the text boxes.

[Missinglinq]

So what code is behind this add button?

[lithium]

Private Sub cmdNew_Click()
'Check to see if all fields are filled in, avoiding Null entries
If IsNull(txtTitle) Or IsNull(boxAssigned) Or IsNull(boxRequested) Or IsNull(txtComments) Or IsNull(boxPriority) Then
    MsgBox "Please ensure all fields are filled in!", , "Incomplete Form!"
    Me.Undo
    Else
    'No Null were found, insert into table and exit after requery
    CurrentDb.Execute ("INSERT INTO Table1([Title],[Assigned To],[Requested By],[Status], [Priority], [Comments]) VALUES ('" & Me.txtTitle & "', '" & Me.boxAssigned & "', '" & Me.boxRequested & "', '" & Me.boxStatus & "', '" & Me.boxPriority & "', '" & Me.txtComments & "')")
    MsgBox "New repair request ticket added."
    [Forms]![Main1]![lstActive].Requery
    DoCmd.Close
End If

End Sub

[RichXB]

OK, you could try this on a spare copy so you don't break your database. Should knock out most problematic characters. I've assumed txtTitle and txtComments are the only free text items here.

Private Sub cmdNew_Click()
'Check to see if all fields are filled in, avoiding Null entries
If IsNull(txtTitle) Or IsNull(boxAssigned) Or IsNull(boxRequested) Or IsNull(txtComments) Or IsNull(boxPriority) Then
    MsgBox "Please ensure all fields are filled in!", , "Incomplete Form!"
    Me.Undo
    Else

'new bit - insert the characters you want to eliminate
Replacestring = "/?<>\|:;*'"""""
For R = 1 To Len(Replacestring)
txtTitle = Replace(txtTitle, Mid(Replacestring, R, 1), "-")
txtComments = Replace(txtComments, Mid(Replacestring, R, 1), "-")
Next R
'// new bit





    'No Null were found, insert into table and exit after requery
    CurrentDb.Execute ("INSERT INTO Table1([Title],[Assigned To],[Requested By],[Status], [Priority], [Comments]) VALUES ('" & Me.txtTitle & "', '" & Me.boxAssigned & "', '" & Me.boxRequested & "', '" & Me.boxStatus & "', '" & Me.boxPriority & "', '" & Me.txtComments & "')")
    MsgBox "New repair request ticket added."
    [Forms]![Main1]![lstActive].Requery
    DoCmd.Close
End If

End Sub

[Missinglinq]

So, if any of the Controls are empty, you want to make all of them empty? That's what your line

Me.Undo

will accomplish!

And why are you using an Unbound Form for this? In Access there are a few, specialized scenarios that require taking this approach, but this certainly isn't one of them. Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That's because with Bound Forms, Access takes care of the 'heavy lifting,' but with Unbound Forms, the developer has to write code to take care of just about everything.

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Once again, the question "Why?" Why not let Access automatically save the Record by default, when you leave the Record or close the Form? There's nothing that can be done using Unbound Forms that cannot be accomplished with Bound Forms, and usually accomplished with less work!

Linq ;0)>

[ssanfu]

I'm in total agreement with Linq. Much easier using bound forms.


I noticed that you are not checking the status control ("boxStatus") to see if it is null. This would cause an error in the insert string.

I have quit using (for the most part) the IsNull() function because I would have to also check for just spaces in the controls.

I use "Len(Trim(Me.txtTitle))>0" when I want to ensure there is an entry in a field. This eliminates someone typing one or more spaces. It has solves many problems I encountered.

[John_G]

Hi -

The chances are it is not the special characters that are causing the problem, but apostrophe's or single quotes. In the command following your CurrentDB.Execute, you use single-quotes to delimit the strings. If your users enter a single-quote as part of their text (e.g. "The part didn't arrive on time"), you are going to get that syntax error (I know from experience!).

Try using the double quote as a delimiter instead:

CurrentDb.Execute ("INSERT INTO Table1([Title],[Assigned To],[Requested By],[Status], [Priority], [Comments]) VALUES (""" & Me.txtTitle & """, """ & Me.boxAssigned & """, """ & Me.boxRequested & """, """ & Me.boxStatus & """, """ & Me.boxPriority & """, """ & Me.txtComments & """)")

Notice that to get the double-quote character into the strings, you need to type it twice.

This means that you have to tell you users that they cannot use double-quotes in the text they enter. Even better would be to check for double-quotes and generate an error message to the user if they use one.

HTH

John