Using VB in Continuous forms 2

[LimitedTech]

I want to use the following code in a text box on a continuous Form.
If
[TxtBillComm4] ="NORMAL" Then
[CurTotalPay] = [CurRate]+[CurFee]
ElseIf [TxtBillComm4] = "SPECIAL" Then
[CurTotalPay] = [CurRate]
ElseIf [TxtBillComm4] = "NO BASE" Then
[CurTotalPay] = [CurRate]
ElseIf [TxtBillComm4] = "OUTSIDE" Then
[CurTotalPay] = [CurRate]
Else
[CurTotalPay] = 0
End If

The code will work but it puts the results of line 1 in all the [CurTotalPay] in the subsequent records. I've tried putting it in different events but cant seem to get this to work. What am I doing wrong?

 

[MajP]

unfortunately you can not do this with an unbound control through an event. You have to use a function. You could use that function in a calculated text box or a calculated field in a query. Either way you need to pass in the three values from your table: curRate, curFee, and whatever field txtBillComm4.


So build a UDF

Public fucntion GetCurTotal(SomeField as variant, curRate as variant, curFee as variant) as integer)
 select case someField
  case "NORMAL" 
     GetCurTotal = [CurRate]+[CurFee]
   case "SPECIAL", "No Base", "Outside" 
     GetCurTotal = [CurRate]
   end select
end Function

Then you can call this function from a query and pass in the values

Select ...... GetCurTotal([someField],[curRate],[curFee]) as CurTotal

Now you can use CurTotal as a control source for a control.

If you want to use the UDF from a calculated control it is the same thing
=GetCurTotal([someField],[curRate],[curFee])

 

[LimitedTech]

thanks MajP I will give this a try and report back!

 

[LimitedTech]

Except a couple typos worked great! Thanks again.

 

[dhookom]

LimitedTech,
I would be appropriate to award a star to MajP by clicking the "Great post?" link. This also indicates the thread has been successfully concluded.

Duane
Hook'D on Access
MS Access MVP

 

[LimitedTech]

This working great. The cases are now numbers instead of text. I need to add another case to this. (Case 5) is conditional on other values. I have a different UDF for this.

Public Function BASERATE(RATETYPE As String, RATE As Currency, WEIGHT As Single, MILES As Long, ADDCHRGAMT1 As Currency, ADDCHRGAMT2 As Currency, ADDCHRGAMT3 As Currency, ADDCHRGAMT4 As Currency) As Currency
Select Case RATETYPE
  Case Is = "Flat"
    BASERATE = RATE + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case Is = "Cwt"
    BASERATE = (WEIGHT * 0.01) * RATE + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case Is = "P/Mile"
    BASERATE = MILES * RATE + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case Else
    BASERATE = 0
End Select
End Function

I'm just not sure how to get this code into the function.
I tried nesting but could not get it to operate correctly. (IE first set the value for BASERATE and case 5 = BASERATE)
Or I thought I could just declare all the variables needed and use an and statement in the case.
IE(case 5 and "Flat", Case 5 and "P/mile", etc) How is the best way to approach this?

 

[Andrzejek]

Consider sthese little changes - for easier reading of your code: (you can do the same for the rest of your variables)

Public Function BASERATE([blue]ByRef strRateType As String, ByRef curRate As Currency[/blue], _
WEIGHT As Single, MILES As Long, ADDCHRGAMT1 As Currency, _
ADDCHRGAMT2 As Currency, ADDCHRGAMT3 As Currency, _
ADDCHRGAMT4 As Currency) As Currency

Select Case [blue]strRateType[/blue]
  Case "Flat"
    BASERATE = [blue]curRate[/blue] + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case "Cwt"
    BASERATE = (WEIGHT * 0.01) * curRate+ ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case "P/Mile"
    BASERATE = MILES * curRate + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case Else
    BASERATE = 0
End Select

End Function

To test it just use your Function and pass some parameters:
(Some VALID parameters would be nice)
[tt]
MsbBox "Your Base Rate is: " & BASERATE("Flat", 10.00, 1, 122, 10, 10, 10, 10)[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[LimitedTech]

Thanks for the reply and pointers. I think I was not very clear about what I am trying to do. I need to maintain the original UDF:

Public fucntion GetCurTotal(SomeField as variant, curRate as variant, curFee as variant) as integer)
 select case someField
  case "NORMAL" 
     GetCurTotal = [CurRate]+[CurFee]
   case "SPECIAL", "No Base", "Outside" 
     GetCurTotal = [CurRate]
   end select
end Function

Except I have changed the cases to
1 instead of "Normal"
2 instead of "SPECIAL"
3 instead of "No Base"
4 instead of "Outside"

I need to add a 5 to this code. The case will GetCurtotal = BASERATE from the following code below. This is also a separate UDF called ModBaseRate.

Public Function BASERATE(ByRef strRateType As String, ByRef curRate As Currency, _
WEIGHT As Single, MILES As Long, ADDCHRGAMT1 As Currency, _
ADDCHRGAMT2 As Currency, ADDCHRGAMT3 As Currency, _
ADDCHRGAMT4 As Currency) As Currency

Select Case strRateType
  Case "Flat"
    BASERATE = curRate + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case "Cwt"
    BASERATE = (WEIGHT * 0.01) * curRate+ ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case "P/Mile"
    BASERATE = MILES * curRate + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case Else
    BASERATE = 0
End Select

End Function

However my attempts to make this happen have been unsuccessful. I am not sure how to incorporate this code with the other to make it operate properly.

 

[Andrzejek]

I would focus on one problem at the time, and after solving that problem, move to another one.

Your [tt]GetCurTotal[/tt] will not work because the word ‘function’ is misspelled: fucntion

So if that is the first step, I would concentrate on this part first.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[LimitedTech]

The typos have been corrected as mentioned previously in this thread. GetCurTotal works properly right now.

 

[Andrzejek]

Then why are you showing the code that is not the latest version?
We have no way of knowing if you made any progress or not if you keep showing us old, non-working code and not the one you want to improve.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[LimitedTech]

Thanks for the reply and pointers. What I need to do is, maintain the original UDF:

Public function GetCurTotal(SinCustBillType as single, curRate as Currency, curFee as Currency) as Currency
 select case SinCustBillType
  case 1 
     GetCurTotal = [CurRate]+[CurFee]
   case 2, 3, 4
     GetCurTotal = [CurRate]
   end select
end Function

and add a 5 to this code. The case will GetCurtotal = BASERATE from the following code below. This is also a separate UDF called ModBaseRate.

Public Function BASERATE(ByRef strRateType As String, ByRef curRate As Currency, _
WEIGHT As Single, MILES As Long, ADDCHRGAMT1 As Currency, _
ADDCHRGAMT2 As Currency, ADDCHRGAMT3 As Currency, _
ADDCHRGAMT4 As Currency) As Currency

Select Case strRateType
  Case "Flat"
    BASERATE = curRate + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case "Cwt"
    BASERATE = (WEIGHT * 0.01) * curRate+ ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case "P/Mile"
    BASERATE = MILES * curRate + ADDCHRGAMT1 + ADDCHRGAMT2 + ADDCHRGAMT3 + ADDCHRGAMT4
  Case Else
    BASERATE = 0
End Select

End Function

However my attempts to make this happen have been unsuccessful. I am not sure how to incorporate this code with the other to make it operate properly.

 

[Andrzejek]

How about:

Public Function GetCurTotal(SinCustBillType as single, _
    curRate as Currency, curFee as Currency) as Currency

    Select Case SinCustBillType
      Case 1 
         GetCurTotal = [CurRate]+[CurFee]
      Case 2, 3, 4
         GetCurTotal = [CurRate][blue]
      Case 5
         GetCurtotal = BASERATE(x, y, z, ...) [/blue]
   End Select

End Function

BTW – I still don’t understand why you do not copy-and-paste your code from your VBA editor…?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[LimitedTech]

That was really easy! Thanks again for the help!