Default Value from Module

[tasawer]

Hi I need help to clean up my module usage and to use it to bring up a deafult value for a field.

My Module that works fine when used as code in a form

Public Function mgmtRateCheck(StartDate, ConfirmRate)

Dim Rate1 As Double
Dim DMonth As String

    DMonth = month(StartDate) & "/" & Day(StartDate) & "/" & Year(StartDate)
    
    Rate1 = 0

    Rate1 = DLookup("mgmtRate", "tblmgmtRate", _
    "RateDateStart <= #" & DMonth & "#" & " And RateDateEnd >= #" & DMonth & "#")

    ConfirmRate = Rate1

End Function

this is how I use it in a form and it works fine

Call mgmtRateCheck(Date, ConfirmmgmtRate)
Me.mgmtFee = ConfirmmgmtRate

How do I utilise it as a Default Value function for a field on a form.

Thanks

[ADezii]

Hi I need help to clean up my module usage and to use it to bring up a deafult value for a field.

My Module that works fine when used as code in a form

Public Function mgmtRateCheck(StartDate, ConfirmRate)

Dim Rate1 As Double
Dim DMonth As String

    DMonth = month(StartDate) & "/" & Day(StartDate) & "/" & Year(StartDate)
    
    Rate1 = 0

    Rate1 = DLookup("mgmtRate", "tblmgmtRate", _
    "RateDateStart <= #" & DMonth & "#" & " And RateDateEnd >= #" & DMonth & "#")

    ConfirmRate = Rate1

End Function

this is how I use it in a form and it works fine

Call mgmtRateCheck(Date, ConfirmmgmtRate)
Me.mgmtFee = ConfirmmgmtRate

How do I utilise it as a Default Value function for a field on a form.

Thanks

Unless I am way off base on this one, you are:

1. Passing the Current Date to the Function.
2. Rebuilding the Current Date within the Function.
3. Retrieving the Return Value from the Function via one of its Arguments.
4. If this is all correct, then the Function can be simplified to:
   

   Public Function mgmtRateCheck()
   Dim Rate1 As Double
     
   Rate1 = 0
     
   Rate1 = DLookup("mgmtRate", "tblmgmtRate", _
           "RateDateStart <= #" & Date & "#" & " And RateDateEnd >= #" & Date & "#")
     
   mgmtRateCheck = Rate1
   End Function

5. To Set the Default Value of a Form Field (Text Box) equal to the return Value of this Function, set its Default Value (Properties ==> Data Tab ==> Default Value) equal to:
   

   =mgmtRateCheck()

[tasawer]

Hi Adezzi, we are partly there.
This is one solves the default value for me. Thx

In the 'tblmgmgrate', I have managment rate fixed between two dates.
I need to pick the rate according the invoice date

When an invoice is created, it gets current date as invoice date and mgmtrate can default to your solution above.

However, when entering a backlog of invoices, InvoiceDate will be entered manually hence the need to re-select mgmtrate.

I hope this makes sense.

Regards