Solved

Line 1: Incorrect syntax near ')' Access VBA

Posted on 2014-10-09
10
261 Views
Last Modified: 2014-10-09
Hi all.

I get the following error: Line 1: Incorrect syntax near ')'

Below is my code. I've narrowed the error down to line:
calculatedfreight = Round(DSum("Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID) * .024, 2)

Private Sub DeleteRecord_Click()

Dim rs1 As ADODB.Recordset
Dim DeletedItemID As Long

DeletedItemID = Me!ItemID

DoCmd.DoMenuitem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuitem acFormBar, acEditMenu, 6, , acMenuVer70

DoCmd.RunSQL "DELETE FROM dbo.WorkOrderItems WHERE ParentItemID = & DeletedItemID

Dim calculatedfreight as Currency

calculatedfreight = Round(DSum("Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID) * .024, 2)

DoCmd.RunSQL "UPDATE dbo.WorkOrders SET Freight = " & calculatedfreight & " WHERE OrderID = " & WorkOrderID

End Sub

Open in new window


What am I doing wrong? Thank you in advance.
0
Comment
Question by:Sim1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Assisted Solution

by:Anthony Berenguel
Anthony Berenguel earned 50 total points
ID: 40371299
See line 11.
Private Sub DeleteRecord_Click()

	Dim rs1 As ADODB.Recordset
	Dim DeletedItemID As Long

	DeletedItemID = Me!ItemID

	DoCmd.DoMenuitem acFormBar, acEditMenu, 8, , acMenuVer70
	DoCmd.DoMenuitem acFormBar, acEditMenu, 6, , acMenuVer70

	DoCmd.RunSQL "DELETE FROM dbo.WorkOrderItems WHERE ParentItemID = " & DeletedItemID ' you were missing the closing quotation symbol before the ampersand. 

	Dim calculatedfreight as Currency

	calculatedfreight = Round(DSum("Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID) * .024, 2)

	DoCmd.RunSQL "UPDATE dbo.WorkOrders SET Freight = " & calculatedfreight & " WHERE OrderID = " & WorkOrderID

	End Sub

Open in new window

0
 

Author Comment

by:Sim1980
ID: 40371458
Added the missing closing quotation symbol and I still get the same error.

When I remove the lines:

calculatedfreight = Round(DSum("Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID) * .024, 2)

	DoCmd.RunSQL "UPDATE dbo.WorkOrders SET Freight = " & calculatedfreight & " WHERE OrderID = " & WorkOrderID

Open in new window


I don't get the error.
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40371474
What if you remove just line 3? Will line 1 execute successfully?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 total points
ID: 40371733
Try with:

calculatedfreight = Round(DSum("Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID & "") * .024, 2)

/gustav
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40371803
What are you trying to do?

In code, there are recordset, delete, and update statements.

This line looks fine:
calculatedfreight = Round(DSum("Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID) * .024, 2)

But try to check it by setting a break point at the line.
Then in immediate window check:
?DSum("Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID)
0
 

Author Comment

by:Sim1980
ID: 40371835
Anthony: I removed line 3 and still get the same error

Gustav: I added your code and still get the same error

hnasr: I set a break point at the line, in the immediate window I type ? and the line of code.
I got the following error message: Run-time error '170': Line 1: Incorrect syntax near ')'.
0
 

Author Comment

by:Sim1980
ID: 40371840
It should be noted, if I place that line of code before the DELETE statement, I do not get an error and it correctly  updates the calculatedfreight.
0
 
LVL 30

Accepted Solution

by:
hnasr earned 400 total points
ID: 40371874
Run this in immediate window, at break point:
? "Price","WorkOrderItems","ItemType = 1 and WorkOrderID = " & WorkOrderID
0
 

Author Comment

by:Sim1980
ID: 40371893
That was it hnasr !

When I ran you last code in the immediate window, it showed WorkOrderID as null.

I made sure the WorkOrderID was on the form and used Me.WorkOrderID instead and it solved the problem.

Thanks!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40371902
Welcome!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question