Entering data into table using a form in Access 2010

[maahipa2009]

Hi I searched a lot for this problem (Run-time error 3134: Syntax error in INSERT INTO statement) , please help me if you know the solution:

I have a table created with some fields such as AQ Code, Process Category, Process Type (Combo box), Notes, Start Date, End Date, Updated By, Update. I have also created a blank form with same fields to make entries into this table.

The VBA code I am using is as follows:
Private Sub cmdAdd_Click()
'add data to table
CurrentDb.Execute "INSERT INTO tblProcesses(AQ Code, Process Category, Process Type, Notes, Startdate, Enddate, Updated By, Update) VALUES("' & Me.txtCode & "','" & Me.txtCategory & "','" & _
Me.cboType" & "','" & Me.txtNotes & "','" & Format(txtStartdate.values, "mm/dd/yyyy") & " #,#" & Format(txtEndDate.Value, "mm/dd/yyyy") & "#,#" & Me.txtUpdate & " #)"

'clear form
cmdClear_Click


'refresh data in list on form
frmProcessesSub.Form.Requery
End Sub


Private Sub cmdClear_Click()
Me.txtCode = ""
Me.txtCategory = ""
Me.cboType = ""
Me.txtNotes = ""
Me.txtStartdate = ""
Me.txtEndDate = ""
Me.txtUpdatedBy = ""
Me.txtUpdate = ""

'focus in AQ Code text box
Me.txtCode.SetFocus
End Sub


Private Sub cmdClose_Click()
DoCmd.Close
End Sub

[John_G]

Hi -

You have some problems with reversed and extra quotation marks, and extra blanks, marked in bold-red:

CurrentDb.Execute "INSERT INTO tblProcesses(AQ Code, Process Category, Process Type, Notes, Startdate, Enddate, Updated By, Update) VALUES("' & Me.txtCode & "','" & Me.txtCategory & "','" & _
Me.cboType" & "','" & Me.txtNotes & "','" & Format(txtStartdate.values, "mm/dd/yyyy") & " #,#" & Format(txtEndDate.Value, "mm/dd/yyyy") & "#,#" & Me.txtUpdate & " #)"


The fixed version looks like this (I hope!):

"INSERT INTO tblProcesses(AQ Code, Process Category, Process Type, Notes, Startdate, Enddate, Updated By, Update) VALUES('" & Me.txtCode &
"','" & Me.txtCategory & "','" & Me.cboType & "','" & Me.txtNotes & "',#" & Format(txtStartdate.values, "mm/dd/yyyy") & "#,#" & Format(txtEndDate.Value, "mm/dd/yyyy") & "#,#" & Me.txtUpdate & "#)"

You might also want to format Me.txtUpdate as you did the other dates.

John

[maahipa2009]

Hi John,

First of all thank you very much for replying me back. I really appreciate your response and knowledge.

I tried with that but I am getting Compile error: Method or data member not found with Me.UpdatedBy hi lighted. Please any solutions?

Private Sub cmdAdd_Click()
'add data to table
CurrentDb.Execute "INSERT INTO tblProcesses(AQ Code, Process Category, Process Type, Notes, Startdate, Enddate, Updated By, Update) VALUES('" & Me.txtCode & "','" & Me.txtCategory & "','" & Me.cboType & "','" & Me.txtNotes & "','" & Me.txtUpdatedBy & "',#" & Format(txtStartdate.values, "mm/dd/yyyy") & "#,#" & Format(txtEndDate.Value, "mm/dd/yyyy") & "#,#" & Format(txtUpdate.Value, "mm/dd/yyyy") & "#)"

'clear form
cmdClear_Click
'refresh data inlist on form
frmProcessesSub.Form.Requery
End Sub

[rpeare]

Create your SQL statement as a string first then use

debug.print SQLString

to see what the code is actually generating, I typically can not tell what I've done wrong when generating SQL statements without looking at the actual output.

Secondly, are you encountering any this error when specific fields are null as opposed to having a value? I haven't tested but I seem to recall Access will bomb out if you try to append a null date using ## instead of null in the SQL string. The other benefit of building your sql string independently (before you try to actually run it) is that you can code it to ignore fields where there are no values to append, so for instance let's say your record only had 3 fields, AQ_Code, Process_Category and StartDate your code would be something like

sSQL = "INSERT INTO tblProcesses ("
sSQL = sSQL & IIf(IsNull(txtCode), "", "AQ_Code,")
sSQL = sSQL & IIf(IsNull(txtCategory), "", "Process_Category,")
sSQL = sSQL & IIf(IsNull(txtStartDate), "", "StartDate,")
sSQL = Left(sSQL, Len(sSQL) - 1)
sSQL = sSQL & ") VALUES ("
sSQL = sSQL & IIf(IsNull(txtCode), "", "'" & txtCode & "',")
sSQL = sSQL & IIf(IsNull(txtCategory), "", "'" & txtCategory & "',")
sSQL = sSQL & IIf(IsNull(txtStartDate), "", "#" & txtStartDate & "#,")
sSQL = Left(sSQL, Len(sSQL) - 1)
sSQL = sSQL & ")"

db.execute (ssql)

You can also test your txtStartdate with the isDate function to make sure it's actually a date before you try to append it though if you have an input mask on that field it should prevent you from entering a non date even if you're actually storing the value as a text field.

[John_G]

maahipa2009 -

The error you are getting - method or data member not found - suggests that Me.txtUpdatedBy is not the name of a control on your form. Check that you have the control name right.

John

[maahipa2009]

Thanks!

But I am getting Run-time error '424': Object Required

[rpeare]

To whom are you replying, johng or me?

[John_G]

Where is that run-time error happening? What line of code, and in what module?

John

[maahipa2009]

Oops I am sorry!

For rpeare,

I am getting Run-time error '424': Object Required for db.execute (ssql)

[maahipa2009]

Hi John,

Again I am getting Compile error: Method or data member not found with txtNotes.
I cross checked it, it is spelled correctly.

Code:
Private Sub cmdAdd_Click()
'add data to table
CurrentDb.Execute "INSERT INTO tblProcesses(AQ Code, Process Category, Process Type, Notes, Startdate, Enddate, Updated By, Update) VALUES('" & Me.txtCode & "','" & Me.txtCategory & "','" & Me.cboType & "','" & Me.txtNotes & "',#" & Format(txtStartdate.values, "mm/dd/yyyy") & "#,#" & Format(txtEndDate.Value, "mm/dd/yyyy") & "#,#" & Format(txtUpdate.Value, "mm/dd/yyyy") & "#,'" & Me.txtUpdatedBy & "')"

'clear form
cmdClear_Click
'refresh data in list on form
frmProcessesSub.Form.Requey
End Sub


Private Sub cmdClear_Click()
Me.txtCode = ""
Me.txtCategory = ""
Me.cboType = ""
Me.txtNotes = ""
Me.txtStartdate = ""
Me.txtEndDate = ""
Me.txtUpdatedBy = ""
Me.txtUpdate = ""

'focus in AQ Code text box
Me.txtCode.SetFocus
End Sub


Private Sub cmdClose_Click()
DoCmd.Close
End Sub

[rpeare]

With my code you have to define the database first

dim db
dim sSQL as string

set db = current db
<insert the rest of my code above>

[maahipa2009]

Hi rpeare,

Is this code is ok now?

Private Sub cmdAdd_Click()

Debug.Print SQLString
Dim db
Dim sSQL As String
sSQL = "INSERT INTO Processes("
sSQL = sSQL & IIf(IsNull(txtAQCode), "", "AQ Code,")
sSQL = sSQL & IIf(IsNull(txtCategory), "", "Category,")
sSQL = sSQL & IIf(IsNull(txtType), "", "Type,")
sSQL = sSQL & IIf(IsNull(txtNotes), "", "Notes,")
sSQL = sSQL & IIf(IsNull(txtStartdate), "", "Start Date,")
sSQL = sSQL & IIf(IsNull(txtEnddate), "", "End Date,")
sSQL = sSQL & IIf(IsNull(txtUpdatedby), "", "Updated By,")
sSQL = sSQL & IIf(IsNull(txtUpdate), "", "Update,")
sSQL = Left(sSQL, Len(sSQL) - 1)
sSQL = sSQL & ") VALUES ("
sSQL = sSQL & IIf(IsNull(txtAQCode), "", "'" & txtAQCode & "',")
sSQL = sSQL & IIf(IsNull(txtCategory), "", "'" & txtCategory & "',")
sSQL = sSQL & IIf(IsNull(txtType), "", "'" & txtType & "',")
sSQL = sSQL & IIf(IsNull(txtNotes), "", "'" & txtNotes & "',")
sSQL = sSQL & IIf(IsNull(txtStartdate), "", "#" & txtStartdate & "#,")
sSQL = sSQL & IIf(IsNull(txtEnddate), "", "#" & txtEnddate & "#,")
sSQL = sSQL & IIf(IsNull(txtUpatedby), "", "'" & txtUpdatedby & "',")
sSQL = sSQL & IIf(IsNull(txtUpdate), "", "#" & txtUpdate & "#,")
sSQL = Left(sSQL, Len(sSQL) - 1)
sSQL = sSQL & ")"

db.Execute (sSQL)

Set db = CurrentDb
End Sub

[rpeare]

no, if you're going to declare a database you have to set it before you try to run any operations in that database so the

set db = currentdb should be in declared BEFORE you try to use db.execute (ssql) or you're going to get a method not supported error.

Also if you put debug.print ssql before you've even tried to build your string you're going to get a blank string

Remember always when you're programming where you are in your sequence, if you are trying to use things that haven't been determined by your code you're always going to have trouble

Other than that it looks ok assuming you actually have fields named as you do.

One other thing. Just as a matter of housekeeping you should close any object that you open to save memory.

So after you declare your variables you'd have

set db = currentdb

and the very last line of your code you'd have

set db = nothing

I'm terrible about putting it in sample code but it's a good practice to close all object you open.