I am getting a syntax error but I cant seem to spot it. need help with this. Its when I click the save button that fires my SQL UPDATE query.
thansk in advance -
'module level declarations
-
Dim rsNames As ADODB.Recordset
-
Dim cnDb As ADODB.Connection
-
Dim strConnection As String
-
Dim blnAddMode As Boolean
-
-
Sub SaveCurrentRecord()
-
-
Dim cmdCommand As ADODB.Command
-
Set cmdCommand = New ADODB.Command
-
Dim strSQL As String
-
-
If Not rsNames.BOF And Not rsNames.EOF Then
-
-
'create a new connection instance and open it using the connection string
-
Set cnDb = New ADODB.Connection
-
cnDb.Open strConnection
-
-
Dim intCurName As Integer
-
intCurName = 0
-
-
'if adding a new record
-
If blnAddMode = True Then
-
On Error GoTo HandleError
-
'create SQL to insert a new record into the database
-
'containing the values on the form
-
strSQL = "INSERT INTO tblName(" & _
-
"fldLastName, fldFirstName, fldTitle) " & _
-
"VALUES (" & _
-
"'" & Me.txtLastName & "', " & _
-
"'" & Me.txtFirstName & "', " & _
-
"'" & Me.txtTitle & "') "
-
-
Else
-
On Error GoTo HandleError
-
'create SQL to update the existing record in the
-
'database with the values on the form
-
strSQL = "UPDATE tblName SET " & _
-
"fldLastName = '" & Me.txtLastName & "', " & _
-
"fldFirstName = '" & Me.txtFirstName & "', " & _
-
"fldTitle = '" & Me.txtTitle & "', " & _
-
"WHERE fldNameId = " & Me.txtNameId
-
-
'save the id of the current record
-
intCurName = rsNames!fldNameId
-
End If
-
-
'set the command to the current connection
-
Set cmdCommand.ActiveConnection = cnDb
-
'set the insert or update SQL statement to the command text
-
cmdCommand.CommandText = strSQL
-
'execute the delete command against the database
-
cmdCommand.Execute
-
-
'while connected to the database, go ahead and
-
'repopulate the recordset to make sure it contains
-
'the most current values from the database.
-
Set rsNames.ActiveConnection = cnDb
-
rsNames.Requery
-
Set rsNames.ActiveConnection = Nothing
-
-
'move back to the contact that was current before the
-
'requery
-
If intCurName > 0 Then
-
'move back to the contact that was just updated
-
rsNames.Find "[fldNameId] = " & intCurName
-
Else
-
'if just added new record, move to the beginning of
-
'the recordset
-
rsNames.MoveFirst
-
End If
-
-
'reset add mode flag to false
-
blnAddMode = False
-
-
'populate the controls on the form
-
Call PopulateControlsOnForm
-
End If
-
Exit Sub
-
-
HandleError:
-
GeneralErrorHandler Err.Number, Err.Description, BUS_LOGIC, "Update"
-
Exit Sub
-
-
End Sub
-
4 3197
Hi there!
I believe the error is that you have added one too many commas. -
strSQL = "UPDATE tblName SET " & _
-
"fldLastName = '" & Me.txtLastName & "', " & _
-
"fldFirstName = '" & Me.txtFirstName & "', " & _
-
"fldTitle = '" & Me.txtTitle & "' " & _ //<----- here is where i removed the comma
-
"WHERE fldNameId = " & Me.txtNameId
-
The extra comma is expecting you declare another field that needs to be set but instead it finds the WHERE clause.
Give that a try and let me know if it works out for you.
Remove comma before WHERE - "fldTitle = '" & Me.txtTitle & "', " & _
will run better this way - "fldTitle = '" & Me.txtTitle & "' " & _
Here is additional problem I've noticed - no single quotes around the value supposed to be text. -
...........
-
strSQL = "UPDATE tblName SET " & _
-
"fldLastName = '" & Me.txtLastName & "', " & _
-
"fldFirstName = '" & Me.txtFirstName & "', " & _
-
"fldTitle = '" & Me.txtTitle & "', " & _
-
"WHERE fldNameId = '" & Me.txtNameId & "';" '<------------
-
-
-
great, thanks I solved this.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rachel Weeden |
last post by:
I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.
Basically, I want to select records using...
|
by: Lauren Wilson |
last post by:
The error does not have an error number. It's not even a normal
Access error message box.
Hope someone has some insight to this problem.
I have a form that contains a large (5" x 4") text box...
|
by: Tony |
last post by:
Dear All,
When I run an example program on the
http://www.dotnetjunkies.com/quickstart/util/srcview.aspx?path=/quickstart/aspplus/samples/webforms/data/datagrid1.src&file=VB\datagrid1.aspx&font=3
...
|
by: Michael |
last post by:
Hi Everyone,
I'm having a slight problem, I hope I didn't overlook something. I'm getting
the following error when I try to execute the function below:
Incorrect syntax near 'intake_GetListSet'."...
|
by: bughunter |
last post by:
IMHO, statements like this is mistake typically.
May be more better made this construction - I said about empty WHERE -
invalid? A lot of data will saved... :-)
Andy
|
by: Arthur Dent |
last post by:
Hi all,
Im just curious, what is the purpose of the keyword "Overloads" in VB
nowadays?
I understand conceptually what overloads are and what they do, but im a
little puzzled,
because if you...
|
by: itamar82 |
last post by:
I am getting the following error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'WHERE'.
for the sql below:
SELECT TourId
FROM
(SELECT...
|
by: jonathanemil |
last post by:
Hello,
I am a 1st semester Computer Science student in a Python class. Our current assignment calls for us to read a list from a file, create a 2-dimensional list from the file, and check to see...
|
by: kmartinenko |
last post by:
Hello,
I am wondering if there is a sly workaround in ArcGIS 9.2 where I can write a "near" analysis script in Python and create my own tool for the purpose of identifying the distance values...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |