• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

MS Access VBA Code Help!!

had a Question that was answered 1/30/2011 http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26782103.html30/01/11 05:11 PM, ID: 34747454
 and every things works fine but gives a error when dealing with subtract time the VB help shows the date/time -date/time but wont do the math is there anything i can do to fix this?below is the solution i was giving again all code_formula works unless i subtract time/date then i get a runtime error 2434

----------------------------------------------------------------------------------------------------------------------------
Bitsqueezer:
Hi,

instead of always creating a new code and redeploy the database as MDE make your code more flexible so you don't need to ever change it.

First, create a new table and fill it with the needed formulas, example:
ID_Code	CODE_Short	CODE_Formula
1       H/R         24 * ({EndTime} - {StartTime}) * {Price}
2       M/P         ({EndMiles} - {StartMiles}) * {Price}
3       DT-H/R      24 * ({EndTime} - {StartTime}) * {Price} + 13
4       NT-H/R      24 * ({EndTime} - {StartTime}) * {Price} + 18

Open in new window


The words in the curly brackets are the variables which will be changed in the code later.

Now use something like this for your code:
Private Sub CALLTYPEID_AfterUpdate()
   Dim Price As Currency
   Dim strFormula As String
    
    [CallTypeExt] = DLookup("CallTypeExt", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
    
    If Nz(Me![END MILES], 0) <> 0 Then
        Price = DLookup("Rate", "tblCallTypePrices", "[CallTypeID] = " & Me!CALLTYPEID & " And #" & _
                        TimeValue(Me![START TIME]) & "# Between [StartTime] And [EndTime]")
        
        strFormula = DLookup("CODE_Formula", "tblCodes", "CODE_Short='" & Nz([CallTypeExt]) & "'")
        If strFormula <> "" Then
            strFormula = Replace(strFormula, "{StartTime}", CStr(Me![START TIME]))
            strFormula = Replace(strFormula, "{EndTime}", CStr(Me![END TIME]))
            strFormula = Replace(strFormula, "{StartMiles}", CStr(Me![START MILES]))
            strFormula = Replace(strFormula, "{EndMiles}", CStr(Me![END MILES]))
            strFormula = Replace(strFormula, "{Price}", CStr(Price))
            Me![DRIVERS PAY] = Eval(strFormula)
        Else
            Me![DRIVERS PAY] = Price
        End If
    End If
    
    DoCmd.RunCommand acCmdRefresh
End Sub

Open in new window



(I removed the "Cancel = True" as it make no sense in an AfterUpdate event.)

The code simply loads the right formula from the table "tblCodes" and if the short term from "CallTypeExt" could be found in this table it replaces all possible "variables" (the texts in the curly brackets) with the current values from the form and in the end it will be computed with Eval.
I did not test it, maybe you need to adjust it a little bit but it should show you the idea.

If you now need any additional formula simply add it to the table which you can do without any change in the code of the database and it will work immediately.

Cheers,

Christian


PLEASE HELP THIS IDEA WORKS GREAT JUST A FEW FLAWS THAT NEEDS TO BE WORKED OUT
0
Blueice13085
Asked:
Blueice13085
  • 5
  • 4
  • 2
2 Solutions
 
BadotzCommented:
When you get to
Me![DRIVERS PAY] = Eval(strFormula)

Open in new window

what is the value of strFormula?
0
 
Blueice13085Author Commented:
strFormula = "24 * (4/2/2011 10:01:44 AM - 4/2/2011 9:07:44 AM) * 17"

this is what it shows in the VBA window when i highlight
Me![DRIVERS PAY] = Eval(strFormula)

Open in new window

0
 
Blueice13085Author Commented:
and the M/P works just fine wits the CODE_Formula


({EndMiles} - {StartMiles}) * {Price}
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Blueice13085Author Commented:
Aslo this code work(s) great, just cant update the code in .MDE

[CallTypeExt] = DLookup("CallTypeExt", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
[$ INV AMOUNT$] = DLookup("CallTypePrice", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
[PAY TYPE] = DLookup("CallPayType", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
   Dim Price As Currency
    
    If Nz(Me![End Miles], 0) = 0 Then
        Cancel = True
   
    End If
    
    If Cancel <> True Then
        Price = DLookup("Rate", "tblCallTypePrices", "[CallTypeID] = " & Me!CALLTYPEID & " And #" & _
            TimeValue(Me![Start Time]) & "# Between [StartTime] And [EndTime]")
        Select Case Me!CallTypeExt
            Case "H/R"
                Me![DRIVERS PAY] = 24 * (Me![End Time] - Me![Start Time]) * Price
            Case "M/P"
                Me![DRIVERS PAY] = (Me![End Miles] - Me![Start Miles]) * Price
           Case "DT-H/R"
                Me![DRIVERS PAY] = 24 * (Me![End Time] - Me![Start Time]) * Price + 13
            Case "NT-H/R"
                Me![DRIVERS PAY] = 24 * (Me![End Time] - Me![Start Time]) * Price + 18
             Case "Cancel"
                Me![DRIVERS PAY] = 0
            Case Else
                Me![DRIVERS PAY] = Price
            
        End Select
    End If
    
 DoCmd.RunCommand acCmdRefresh

Open in new window

0
 
BadotzCommented:
That value for strFormula does not eval, but this will
strFormula = "24 * (#4/2/2011 10:01:44 AM# - #4/2/2011 9:07:44 AM#) * 17"

Open in new window

Note the dates are wrapped in octothorpes (#).
0
 
peter57rCommented:
strFormula = "24 * (4/2/2011 10:01:44 AM - 4/2/2011 9:07:44 AM) * 17"

This formula does not conform to Access requirements.
It might work in some situations but in general it is wrong.

You must include datatime values inside # ..# delimiters.

So you need to modify your code so that you create an expression which is..

strFormula = "24 * (#4/2/2011 10:01:44 AM# - #4/2/2011 9:07:44 AM#) * 17"
You will then have a correctly expressed formula which will work.
0
 
BadotzCommented:
Nice work, peter57r!
0
 
peter57rCommented:
Just a bit behind the pace again(:-)
0
 
Blueice13085Author Commented:
alright so how can i get the formula to have the #..... formula reads
24 * ({EndTime} - {StartTime}) * {Price}
so would it be

24 * (#{EndTime}# - #{StartTime}#) * {Price}


0
 
Blueice13085Author Commented:
I selected the both of you sense the time of post was porb within 30 secs or so, and both had the correct answer i thank you all for the help and hope all understands the points i gave to each other:)
0
 
BadotzCommented:
No worries - glad to help.

And yes, I believe 24 * (#{EndTime}# - #{StartTime}#) * {Price} should work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now