Compile error...Please help

[Paul]

I have an Access form and it has always worked since the day I finished the
design. Today for no apperant reason Microsoft Visual Basic "Compile error"
window popped up when I tried to execute an On_Click event on that form
saying "Wrong number of arguments or invalid property assiugnment". It
highlighted and focused on the "RunSQL" on the line

'Append NEW Move To record to the Tbl_Inventory Detail
DoCmd.RunSQL................................. Please help

Private Sub btnUpdate_Click()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frm As Form
Dim varCriteria1 As Variant 'various Event ID
Dim varCriteria2 As Variant 'InventoryDetailID
Dim varCriteria3 As Variant
Dim varCriteria4 As Variant

Select Case Me.cboEvent
Case "Move" '1
Set frm = Forms![Frm_Update Event]![Frm_Event Subform].Form
Set db = CurrentDb
Set rs = frm.RecordsetClone
On Error Resume Next
If Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
If rs!Status = True Then
varCriteria1 = rs!MoveID
varCriteria2 = rs!InventoryDetailID

DoCmd.SetWarnings False
'Append NEW Move To record to the Tbl_Inventory Detail
DoCmd.RunSQL "INSERT INTO Tbl_Inventory_Detail ( InventoryID,
PotSize, Location, Quantity, UnitPrice, Status, Comment, UserNotes,
StockNumberNotes ) " & _
"SELECT Tbl_Event.InventoryID,
Tbl_Inventory_Detail.PotSize, Tbl_Move.NewLocation, Tbl_Move.Quantity,
Tbl_Inventory_Detail.UnitPrice, ""Active"" AS Expr1, ""Initially Created By
Move Event, "" & ""Date :"" & Format(Tbl_Move.MoveDate,""dd-mmm-yy"") & ", "
& ""Move From Location: "" & Tbl_Inventory_Detail.Location & ", " &
""Quantity: "" & Tbl_Move.Quantity AS Expr2, Tbl_Move.UserNotes,
Tbl_Move.StockNumberNotes " & _
"FROM Tbl_Event INNER JOIN (Tbl_Move INNER JOIN
Tbl_Inventory_Detail ON Tbl_Move.InventoryDetailID =
Tbl_Inventory_Detail.InventoryDetailID) ON Tbl_Event.EventID =
Tbl_Move.EventID " & _
"WHERE (((Tbl_Move.MoveID)= " & varCriteria1 & "));"
'Update Move From Quantity to the Tbl_Inventory Detail
DoCmd.RunSQL "UPDATE (Tbl_Inventory INNER JOIN
Tbl_Inventory_Detail ON Tbl_Inventory.InventoryID =
Tbl_Inventory_Detail.InventoryID) INNER JOIN (Tbl_Event INNER JOIN Tbl_Move
ON Tbl_Event.EventID = Tbl_Move.EventID) ON Tbl_Inventory.InventoryID =
Tbl_Event.InventoryID SET Tbl_Inventory_Detail.Quantity =
[Tbl_Inventory_Detail].[Quantity]-[Tbl_Move].[Quantity] " & _
"WHERE (((Tbl_Inventory_Detail.InventoryDetailID)="
& varCriteria2 & ") AND ((Tbl_Move.MoveID)=" & varCriteria1 & "));"
'Update Date to the Tbl_Move
DoCmd.RunSQL "UPDATE Tbl_Move SET Tbl_Move.UpdatedDate=Now() " &
_
"WHERE ((Tbl_Move.MoveID)=" & _
varCriteria1 & ");"
'Update Status to Inactive to the Tbl_Inventory Detail for zero
Quantity
DoCmd.RunSQL "UPDATE Tbl_Inventory_Detail SET
Tbl_Inventory_Detail.Status = ""Inactive"" " & _
"WHERE (((Tbl_Inventory_Detail.Quantity)=0));"
DoCmd.SetWarnings True
End If

rs.MoveNext
Loop
End If

Case "Sales" '2
Set frm = Forms![Frm_Update Event]![Frm_Event Subform].Form
Set db = CurrentDb
Set rs = frm.RecordsetClone
On Error Resume Next
If Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
If rs!Status = True Then
DoCmd.SetWarnings False
varCriteria1 = rs!SalesID
varCriteria2 = rs!InventoryDetailID
and so on........

 

[Andi Mayer]

I have an Access form and it has always worked since the day I finished the
design. Today for no apperant reason Microsoft Visual Basic "Compile error"
window popped up when I tried to execute an On_Click event on that form
saying "Wrong number of arguments or invalid property assiugnment". It
highlighted and focused on the "RunSQL" on the line

store the SQL in a string variable like stSQL

go to the immidiate window
?stSQL
paste this into the query View (SQL Vew) and look what's the problem