Calculation Problem

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

If I delete the contents with this code:
Private Sub cmdDeleteDailyRow1_Click()
tbStartDate1.value = ""
tbEndDate1.value = ""
tbTotalDays1.value = ""
cbDailyCharge1.value = ""
tbDailyChargeRate1.value = ""
tbDailyChargeAmount1.value = ""

SubCalculate
End Sub

It deletes all the other daily charges 2,3,4,5,6 on Private Sub
subSetInvoiceDetailsValues() is it something to do with "End If"

Private Sub subSetInvoiceDetailsValues()

CurrentProject.Connection.Execute "INSERT INTO
tblAdditionCharge(InvoiceID,ChargeID,HorseID,DayNo,ChargeNumber,AdditionCharge,
" _
& " AdditionChargeAmount)SELECT " & lngInvoiceID & "," & "ChargeID"
_
& "," & cbHorseName.value & ",DayNo,ChargeNumber,AdditionCharge, " _
& " AdditionChargeAmount FROM tmpAdditionCharge"

If IsNull(tbTotalDays1.value) = True Or tbTotalDays1.value = "" Then
Exit Sub
ElseIf IsNull(cbDailyCharge1.value) = True Or cbDailyCharge1.value = ""
Then
Exit Sub
Else
CurrentProject.Connection.Execute "INSERT INTO
tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
& " DailyChargeRate,DailyChargeAmount)Values(" & lngInvoiceID & ",'"
& Format(tbStartDate1.value, "dd/mm/yyyy") _
& "','" & Format(tbEndDate1.value, "dd/mm/yyyy") & "'," &
tbTotalDays1.value & ",'" & cbDailyCharge1.value _
& "'," & tbDailyChargeRate1.value & "," & tbDailyChargeAmount1.value
& ")"
End If

If IsNull(tbTotalDays2.value) = True Or tbTotalDays2.value = "" Then
Exit Sub
ElseIf IsNull(cbDailyCharge2.value) = True Or cbDailyCharge2.value = ""
Then
Exit Sub
Else
CurrentProject.Connection.Execute "INSERT INTO
tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
& " DailyChargeRate,DailyChargeAmount)Values(" & lngInvoiceID & ",'"
& Format(tbStartDate2.value, "dd/mm/yyyy") _
& "','" & Format(tbEndDate2.value, "dd/mm/yyyy") & "'," &
tbTotalDays2.value & ",'" & cbDailyCharge2.value _
& "'," & tbDailyChargeRate2.value & "," & tbDailyChargeAmount2.value
& ")"
End If

If IsNull(tbTotalDays3.value) = True Or tbTotalDays3.value = "" Then
Exit Sub
ElseIf IsNull(cbDailyCharge3.value) = True Or cbDailyCharge3.value = ""
Then
Exit Sub
Else
CurrentProject.Connection.Execute "INSERT INTO
tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
& " DailyChargeRate,DailyChargeAmount)Values(" & lngInvoiceID & ",'"
& Format(tbStartDate3.value, "dd/mm/yyyy") _
& "','" & Format(tbEndDate3.value, "dd/mm/yyyy") & "'," &
tbTotalDays3.value & ",'" & cbDailyCharge3.value _
& "'," & tbDailyChargeRate3.value & "," & tbDailyChargeAmount3.value
& ")"
End If

If IsNull(tbTotalDays4.value) = True Or tbTotalDays4.value = "" Then
Exit Sub
ElseIf IsNull(cbDailyCharge4.value) = True Or cbDailyCharge4.value = ""
Then
Exit Sub
Else
CurrentProject.Connection.Execute "INSERT INTO
tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
& " DailyChargeRate,DailyChargeAmount)Values(" & lngInvoiceID & ",'"
& Format(tbStartDate4.value, "dd/mm/yyyy") _
& "','" & Format(tbEndDate4.value, "dd/mm/yyyy") & "'," &
tbTotalDays4.value & ",'" & cbDailyCharge4.value _
& "'," & tbDailyChargeRate4.value & "," & tbDailyChargeAmount4.value
& ")"
End If
If IsNull(tbTotalDays5.value) = True Or tbTotalDays5.value = "" Then
Exit Sub
ElseIf IsNull(cbDailyCharge5.value) = True Or cbDailyCharge5.value = ""
Then
Exit Sub
Else
CurrentProject.Connection.Execute "INSERT INTO
tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
& " DailyChargeRate,DailyChargeAmount)Values(" & lngInvoiceID & ",'"
& Format(tbStartDate5.value, "dd/mm/yyyy") _
& "','" & Format(tbEndDate5.value, "dd/mm/yyyy") & "'," &
tbTotalDays5.value & ",'" & cbDailyCharge5.value _
& "'," & tbDailyChargeRate5.value & "," & tbDailyChargeAmount5.value
& ")"
End If
If IsNull(tbTotalDays6.value) = True Or tbTotalDays6.value = "" Then
Exit Sub
ElseIf IsNull(cbDailyCharge6.value) = True Or cbDailyCharge6.value = ""
Then
Exit Sub
Else
CurrentProject.Connection.Execute "INSERT INTO
tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
& " DailyChargeRate,DailyChargeAmount)Values(" & lngInvoiceID & ",'"
& Format(tbStartDate6.value, "dd/mm/yyyy") _
& "','" & Format(tbEndDate6.value, "dd/mm/yyyy") & "'," &
tbTotalDays6.value & ",'" & cbDailyCharge6.value _
& "'," & tbDailyChargeRate6.value & "," & tbDailyChargeAmount6.value
& ")"
End If
End Sub
 
Little bit of fault finding I think.

I'd try to find out exactly what's happening at each step to
ensure that it's doing what you want.

Use the debugger and step through your code examining each
of the values and ensuring that the results of the IsNull()
Or Zero length string check is doing what you expect.

It is my habit to do a zero length string test before
IsNull. Dunno why I developed that habit though, lost in
the mists of time :-))

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In Bob Vance typed:
 
Back
Top