VBA Coding Error If/Else/ElseIF? (1 Viewer)

[ria4life]

Im having some issues with this code below...I suspect the issue may be with me else / else if / if commands....any help would be appreciated:

Each group of commands individual works as required....However, once the First and Second commands are clear...the third insert to doesn't execute.
===================



Private Sub cmdadd_Click()

'**This Part Works Fine

If Me.V1 = "" Then
MsgBox ("You Must First Enter a Truck Number")
End If

'**This Part Works Fine

If Me.cmdAdd.Caption = "Add" Then
Dim i As Long
With Me.List54
If .ListCount > 0 Then
For i = 0 To .ListCount - 1
If .Column(1, i) Like Me.V1 Then
MsgBox ("An Entry Already Exist for This Truck #")
End If
Next i
End If
End With

'**Problem Starts Here...This below is not executed after the two checks above are done.

If Me.cmdAdd.Caption = "Add" Then

SQL = "INSERT INTO tblMVMsvc([Date],[Truck#],PaidTime,TtlMVMSvcd,[TtlMEMSvcd],[TtlBoothsSvcd],CoMingling,[TtlStops],[TtlSvcTime],[TtlTrvlTime],[OT],[Other],[MVMhour],[User_ID])" _
& "VALUES ('" & FldDate & "','" & Me.V1 & "','" & Me.V2 & "','" & Me.V3 & "','" & Me.V4 & "','" & Me.V5 & "','" & Me.V11 & "','" & Me.V6 & "','" & Me.V7 & "','" & Me.V8 & "','" & Me.V9 & "','" & Me.V12 & "','" & Me.V13 & "','" & TempVars("EmployeeName").Value & "')"

CurrentDb.Execute SQL
Me.V1 = ""
Me.V2 = ""
Me.V3 = ""
Me.V4 = ""
Me.V5 = ""
Me.V6 = ""
Me.V7 = ""
Me.V8 = ""
Me.V9 = ""
Me.V10 = ""
Me.V11 = ""
Me.V12 = ""
Me.V13 = ""
Me.txtAuto = ""
Me.txtAuto.Tag = ""

Else

'****Update****

DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_Update_Data", acViewNormal, acUpdate
DoCmd.SetWarnings True
Me.cmdAdd.Caption = "Add"
Me.V1 = ""
Me.V2 = ""
Me.V3 = ""
Me.V4 = ""
Me.V5 = ""
Me.V6 = ""
Me.V7 = ""
Me.V8 = ""
Me.V9 = ""
Me.V10 = ""
Me.V11 = ""
Me.V12 = ""
Me.V13 = ""
Me.txtAuto = ""
Me.txtAuto.Tag = ""
MsgBox ("Update Successful")
Me.List54.Requery



End If
End Sub

 

[isladogs]

Hi

First of all you need a ; at the end of your SQL string
Try that & if it still doesn't work, please repost using the code tags button (#) so its easier to read & then advise you

Colin

 

[Tfa]

first of all aufter the insert or update do a me.Undo instead of all the me.v10 = ""
secondly i would suggest insert and update through DAO or ADO what ever you prefer
that way you have full control over the data that you import.
third the problem is in your insert statement or the update ?
use some break points to see which part does it skip and also the values that it gets

 

[CJ_London]

put a line in your code before the .execute

debug.print sql

this will appear in the immediate window which you can copy and paste into the query sql window and try to run to find the errors.

One I can see is there is no space before VALUES

other comments - Date is a reserved word, using as a field name can cause problems. You are also using # in a field name which is used by sql to treat text as a date. Using square brackets often solves the problem, but not always

also change

CurrentDb.Execute SQL


to

CurrentDb.Execute SQL, dbfailonerror

to get sql errors reported