Passing Values in Standard .bas Modules

[tomhughes]

Can someone please direct me to some good information on passing values to and from Standard .bas Modules.

I am using Access 2000. My program calls a standard module where the string values are declared. I have used the following code in the past to pass a string value back to the procedure that is calling the standard Module.

Public Function Get_Record_FrmMain(ByVal strTabNo, strTopicData As String) As String
  .
  .
  .
strTabNo = SomeValue
strTopicData = SomeOtherValue

Get_Record_FrmMain = strTabNo

End Function

I need to pass two values back, but can only pass one back using this type of code. I am not very familiar with VBA code, and any suggestions would be appreciated.

 

[Golom]

Try this

Public Function Get_Record_FrmMain( _
    [red]ByRef[/red] strTabNo [red]As SomeType[/red], _
    [red]ByRef[/red] strTopicData As String) As String
  .
strTabNo = SomeValue
strTopicData = SomeOtherValue

Get_Record_FrmMain = strTabNo

End Function

 

[Remou]

This:

http://allenbrowne.com/ser-16.html

may be of interest.

 

[tomhughes]

With the code as shown below, I am getting a Compile error "ByRef type mismatch"

Private Sub cmdSetCrossLink_Click()
str1 = Get_Record_FrmMain(strTabNo)
str2 = Get_Record_FrmMain(strTopicData)
End Sub


Public Function Get_Record_FrmMain(ByRef strTabNo As String, ByRef strTopicData As String) As String
Get_Record_FrmMain = strTabNo
End Function

 

[Golom]

Your argument list for the function has two required arguments ... strTabNo and strTopicData. Your calls however show only one argument being passed.

 

[jadams0173]

You should be able to get both values with one call

Private Sub cmdSetCrossLink_Click()

Call Get_Record_frmMain(str1,str2)
end sub

Passing the variables by ref allows your funciton to change the values of the variables, thus allowing you to return more than one.

So when the function finishes str1 and str2 will have a value that was set inside the function. Hope that doesn't confuse you to much.

I tried to have patience but it took to long! -DW

 

[tomhughes]

I am not sending any data to the function being called. The function that is being called creates the string values and sends them back.

This code is backwards since it is passing data to the function being called.

Private Sub cmdSetCrossLink_Click()

Call Get_Record_frmMain(str1,str2)
end sub

 

[Golom]

I am not sending any data to the function being called.

In fact ... you are. You are sending variables whose content you don't care about before the call and whose initial values are not being used in the function but you are sending them.

Given that the return values are what matters, you will do something like

Dim s1 As String, s2 As String

Call Get_Record_frmMain(s1,s2)

Debug.Print s1, s2

You don't do any assignment in the calling routine. All that is handled in Get_Record_frmMain.

 

[jadams0173]

Very well said Golom on passing variables that don't have a meaningful value.

You don't have to pass anything with meaning to the function when passing the variables by ref. Here is an example from something I'm working on now:

Dim QtyReleased     As Integer
Dim ItemID          As String
Dim ItemDesc        As String
Dim CustItemNo      As String

        'initalize variables
        QtyReleased = 0
        ItemID = vbNullString
        StartDate = 0



        'This function call will pass the MO number and RETURN the ItemID and the QTY Released
            If Find_Item(txtMO.Text, ItemID, QtyReleased, StartDate, ItemDesc) = False Then Exit Sub
            
                      
            txtPN = ItemID
            txtQty = QtyReleased
            txtDescription = ItemDesc
            
            
            'convert the start date from serial number to a calendar date
            txtBuildDate = StartDate 'Format(tstamp_to_date(StartDate), "mm/dd/yyyy")

The only thing I pass is txtMO.text (but you dont' have to pass anything). In the function Find_Item I assign values to ItemID, QtyReleased, StartDate, ItemDesc and since I'm passing them by ref when the function is complete I know have values in these variables that were returned by the function.

Here is some of the function:

Public Function Find_Item(MO As String, ByRef ITEM As String, ByRef QTY As Integer, ByRef ActStartDate As Date, ByRef Description As String) As Boolean

Dim rsFindItem            As ADODB.Recordset
Dim sql                   As String
.
.
.

Set rsFindItem = New ADODB.Recordset

    With rsFindItem
        .Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText
                
[red]
                Find_Item = True
                ITEM = .Fields("ITEM")
                QTY = .Fields("order_QTY")
                ActStartDate = .Fields("START_DATE")
                Description = .Fields("item_desc")
  [/red]   
        
    End With



End Function

Does this help? In the red section I set the values I need in the function. In the first code snipet I use the values that were set in the function, yet I only passed the function one variable that had a value.

My example may not be coded the "best" way but it works for me and should work for you. HTH

I tried to have patience but it took to long! -DW