Adding combined columns with an if statement otherwise add single column

[shabbaranks]

Hi ....



Im trying to get my VBA to be a bit clever. What I would like to do is using my reference "sProjectRef" is have a calculation before the data gets added to a table. It currently adds Me.Project.Column(2) and combines it to Me.SubProjectCode.

What I would like to do is IF Me.SubProjectCode column 4 contains "old code" then just use Me.SubprojectCode(2) as sProject and if Me.SubProjectCode is empty then combine "Me.Project.Column(2) & "-" & Me.SubProjectCode" as per the original entry.

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
sDescript = Nz(Me.Description, " ")
Me.Description = sDescript
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date
Dim sCostCode As String
Dim sProjectRef As String
Dim sHourType As String
If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If
sHourType = Me.Hour.Value
sCostCode = Me.CostCode
sActivity = Me.Activity.Column(0)
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = Nz(Me.Description, " ")
Me.Description = sDescript
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker
sProjectRef = Me.Project.Column(2) & "-" & Me.SubProjectCode
If sHourType > 0 Then
sHourType = "CD"
Else
sHourType = "CC"
End If
    
Mysql = "INSERT INTO TimesheetTableTemp (sCostCode, Activity, Project, Hours, HourType, Description, suser, ProjectRef, [task date]) Values "
Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sHourType & "', '" & sDescript & "', '" & sLogUser & "', '" & sProjectRef & "', '" + Format(sDate) + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub

Any ideas please?

[pbaldy]

What have you got so far? What part of this test are you stuck on? It sounds like just another If/Then/Else test like you've already used.

[shabbaranks]

Am I close...

If Me.Project.Column(4) = "Old Code" Then
sProjectRef = Me.Project.Column(2)
Else
sProjectRef = Me.Project.Column(3) & "-" & Me.SubProjectCode
End If

Merry Christmas

[pbaldy]

Does it work? If not, what's wrong?

[shabbaranks]

It only adds the Me.SubprojectCode in regardless of whats selected, so no it doesnt work.

[pbaldy]

So set a breakpoint and check the value coming from the combo. Perhaps you've got the wrong column.

http://www.baldyweb.com/Debugging.htm

[shabbaranks]

I set a breakpoint but I dont think I am doing it correctly although I did read through the instructions. The process I did was:

Set the breakpoint - its got a dot and is highlighted brown. It then stops at the point I spcecified then pressed F8 to step through but I dont get anything displayed.

Just not getting it

[pbaldy]

You can either hover the cursor over Me.Project.Column(4) or type

?Me.Project.Column(4)

In the Immediate window.

[shabbaranks]

Got this one to work in the end. The problem was I didnt have column 4 in my query so it was returning a null value. Added and now it works great - thanks!!

[pbaldy]

Glad you got it sorted out.