New to VBA / Access....quick easy ? I'm sure...

[rdjohnso]

New to VBA....quick easy ? I'm sure...

I have three variables (at the bottom of the function)
[CurrentRate][CurrentRateStDt][CurrentRateEndDt]

I would like to be able to pull each of these different variables into a different place in my report via the control source.... how do I do this? Thx for any assistance.
---------------------------------------------------
Control Source:

=Format(CurrentRateData(Forms!frm_Person!personID,DateSerial(Year(Date()),Month(Date()),1),DateSerial(Year(Date()),Month(Date())+1,0)),"mm/dd/yy")
---------------------------------------------------
Code:

Public Function CurrentRateData(PersonID As Integer, StartDate As Date, EndDate As Date) As Integer

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsPerson As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
'Dim iStartDate As Date
'Dim iEndDate As Date
Dim curRate As Integer
Dim curRateStDt As Date
Dim curRateEndDt As Date

'**** make path to file dynamic
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Ron Johnson\My Documents\Blue&White.mdb;"

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "SELECT * FROM tbl_PersonRate where PersonID = " & PersonID
cmd.CommandType = adCmdText
Set rsPerson = cmd.Execute

Do While Not rsPerson.EOF
For iCtr = 0 To rsPerson.Fields.Count - 1
'OutPuts Name and Value of each field
Debug.Print rsPerson.Fields(iCtr).Name & ": " & _
rsPerson.Fields(iCtr).Value

Next

'StartDate and EndDate before (Less Than) RateStDt - ignore the record
If StartDate < rsPerson!RateStartDate And EndDate < rsPerson!RateStartDate Then

'StartDate and EndDate after (Greater Than) RateEndDt - ignore the record
ElseIf StartDate > rsPerson!RateEndDate And EndDate > rsPerson!RateEndDate Then
'Do Nothing

Else

If StartDate >= rsPerson!RateStartDate Then
'StartDate after Person's RateStDt... so within that record
iRate = rsPerson!Rate
iRateStDt = rsPerson!RateStartDate
iRateEndDt = rsPerson!RateEndDate
ElseIf EndDate <= rsPerson!RateEndDate Then
'StartDate before Person's RateStDt...
'BUT EndDate before Person's RateStDt... so within that record
iRate = rsPerson!Rate
iRateStDt = rsPerson!RateStartDate
iRateEndDt = rsPerson!RateEndDate
End If

End If

rsPerson.MoveNext
Loop

CurrentRate = iRate
CurrentRateStDt = iRateStDt
CurrentRateEndDt = iRateEndDt

 

[MajP]

You called it a function, but I do not see any values returned. I would make your three variables public to the module. Then I would create three functions:
Example

public function fncCurrentRate() as integer
fncCurrentRate = currentRate
end function

public function fncRateStart() as date
fncRateStart = CurrentRateStDt
end function

Now use these on your form like any other function.

 

[rdjohnso]

values are at the bottom of the function

currentRate = iRate
currentRateStDt = iRateStDt
currentRateEndDt = iRateEndDt

I tried your example and changed my control source to call the unque function names for each you listed but that didnt work

 

[MajP]

A function returns a single value. Here is how you do it

public function fncName ( arguments) as returntype
blah blah

fncName = something
end function

public sub testFncName (argument values)
'now when I call the function by name it returns something
msgBox fncName
end sub

In your problem I can return the current rate if in the end I do:
name of Your Function = something
CurrentRateData = iRate

You said it did not work. Did you get an error? What was the error? Did you declare the variables outside the procedure?

 

[rdjohnso]

ok MajP....

so i have made one of the Sub ROutines like you mentioned.... think that will work but.... not sure what should be in my controlSource for that cell in my report....

Here is the current Control Source for my Access report.
--------------------------------------------------------
=CurrentRateData(Forms!frm_Person!personID,DateSerial(Year(Date()),Month(Date()),1),DateSerial(Year(Date()),Month(Date())+1,0))

--------------------------------------------------------
Here is that subRoutine.... (bottom part of the main function to show you the 3 variables that have the values for the sub routines...
--------------------------------------------------------
...
vCurrentRate = iRate
vCurrentRateStDt = iRateStDt
vCurrentRateEndDt = iRateEndDt

'Cleanup
rsPerson.Close
conn.Close
Set conn = Nothing

End Function

Public Sub CurrentRate(vCurrentRate)
'Now when call the function by name it returns something
MsgBox vCurrentRate
End Sub
--------------------------------------------------------
Thanks for your help

 

[MajP]

Actually that was just an example of making a function with a return value, vice a subroutine. Here is how I would do it. Your original function was fine. Just change it from "function" to "sub" and remove the integer return type. The only thing you have to do is pull out the local variables and make them module level. At the top of your module declare your variables as public.

Option Compare Database
Option Explicit

public CurrentRate as integer
public CurrentRateStDt as date
public CurrentRateEndDt as date

Now I am not sure how your form works. If this is not a continous form then on the forms_current event fire your subroutine:

call CurrentRateData (Forms!frm_Person!personID,DateSerial(Year(Date()),Month(Date()),1),DateSerial(Year(Date()),Month(Date())+1,0))

This will set all of your public variables. To return them you need a function.

public function fncCurrentRate() as integer
fncCurrentRate = CurrentRate
end function

so in your rate control, the control source is simply
=fncCurrentRate()

fncCurrentRate returns the public variable CurrentRate which was set in your sub routine current rate data.

The other controls have something like this.
=fncRateStart()
=fncRateEnd()

Hope this helps

 

[MajP]

There is a second way you could do this. Leave your function as it is, but change the return type to variant. Now add an extra argument to your function

Public Function CurrentRateData(PersonID As Integer, StartDate As Date, EndDate As Date, strReturn as string ) As Variant

Now pass either "Rate", "Start", or "End" for the parameter "strReturn"

add this at the end of your code

Select Case strReturn
Case "Rate"
CurrentRateData = CurrentRate
Case "Start"
CurrentRateData = CurrentRateStDt
Case "End"
CurrentRateData = CurrentRateEndDt
Case Else
MsgBox "Must define return"
end select

Now you can use one function to return three possible variables.