Variable scope using VB in MS Access

Question

I have not used VB much but as far as I can tell scope works the same as in C#. The problem is, I'm using VB in MS Access so I am unsure if the rules are a bit different (though I assume they are not). The following code shows values being assigned to variables which have only been declared within the function parameters. Specifically looking at PurchaseOrderID, I am unsure as to how it is retaining its assigned value for use in the function proceeding it.

Function Create(SupplierID As Long, EmployeeID As Long, OrderID As Long, PurchaseOrderID As Long) As Boolean
    Dim rsw As New RecordsetWrapper
    If rsw.OpenRecordset("Purchase Orders") Then
        With rsw.Recordset
            .AddNew
            ![Supplier ID] = SupplierID
            If EmployeeID > 0 Then
                ![Created By] = EmployeeID
                ![Creation Date] = Now()
                ![Submitted By] = EmployeeID
                ![Submitted Date] = Now()
                ![Status ID] = Submitted_PurchaseOrder
            End If

            If OrderID > 0 Then
                ![Notes] = InsertString(PurchaseGeneratedBasedOnOrder, CStr(OrderID))
            End If
            If rsw.Update Then
                .Bookmark = .LastModified
                PurchaseOrderID = ![Purchase Order ID]
                Create = True
            End If
        End With
    End If
End Function


Function CreateLineItem(PurchaseOrderID As Long, ProductID As Long, UnitCost As Long, Quantity As Long) As Boolean
    Dim rsw As New RecordsetWrapper
    If rsw.OpenRecordset("Purchase Order Details") Then
        With rsw.Recordset
            .AddNew
            ![Purchase Order ID] = PurchaseOrderID
            ![Product ID] = ProductID
            ![Quantity] = Quantity
            ![Unit Cost] = UnitCost
            CreateLineItem = rsw.Update
        End With
    End If
End Function

Can someone give me some insight on this?

Community · Accepted Answer · 2018-05-08 21:48:18Z

2

In VBA, how you call a procedure can be important.

"Even if a called procedure has declared its parameters as ByRef, you can force those to be ByVal by enclosing each argument within parentheses."

-- http://www.cpearson.com/excel/byrefbyval.aspx

This is true of VBA in all Office applications. For example, let us say you have two procedures:

Sub SubByVal(ByVal Total As Integer)
    Total = 50
End Sub

Sub SubByRef(ByRef Total As Integer)
    Total = 50
End Sub

And you run a few tests:

Dim Total As Integer
Total = 100

These three versions work as expected and Total is equal to 100

Call SubByVal(Total)
SubByVal (Total)
SubByVal Total

These two work as expected and Total is equal to 50

Call SubByRef(Total)
SubByRef Total

However, in this version, in spite of calling ByRef, Total is equal to 100, because it is forced to ByVal by the parentheses.

SubByRef (Total)

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Nov 13, 2012 at 0:37

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Glenn R · Accepted Answer · 2012-11-15 22:05:26Z

Perhaps this is best handled by types. Something like this:

Type PurchaseSet
   PurchaseOrderID As Long
   OrderCreated as boolean
End Type


Function Create(SupplierID As Long, EmployeeID As Long, OrderID As Long ) As PurchaseSet
    Dim rsw As New RecordsetWrapper
    Dim ps as PurchaseSet 
   ps.OrderCreated = false

    If rsw.OpenRecordset("Purchase Orders") Then
        With rsw.Recordset
            .AddNew
            ![Supplier ID] = SupplierID
            If EmployeeID > 0 Then
                ![Created By] = EmployeeID
                ![Creation Date] = Now()
                ![Submitted By] = EmployeeID
                ![Submitted Date] = Now()
                ![Status ID] = Submitted_PurchaseOrder
            End If

            If OrderID > 0 Then
                ![Notes] = InsertString(PurchaseGeneratedBasedOnOrder, CStr(OrderID))
            End If
            If rsw.Update Then
                .Bookmark = .LastModified
                ps.PurchaseOrderID = ![Purchase Order ID]
                ps.OrderCreated = True

            End If
        End With
    End If

    Create = ps
End Function

HansUp · Accepted Answer · 2012-11-12 23:24:21Z

1

You can pass arguments to a VBA procedure by value or by reference. If you don't specify either ByVal or ByRef in the procedure's declaration, it defaults to ByRef. So the following two declarations are equivalent ...

Function DoSomething(PurchaseOrderID As Long) AS Boolean
Function DoSomething(ByRef PurchaseOrderID As Long) AS Boolean

The key here is that ByRef allows changes to the argument within the procedure to be transmitted back to the caller.

edited Nov 12, 2012 at 23:24

answered Nov 12, 2012 at 23:15

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

7 Comments

Nate Over a year ago

I thought Long was immutable i.e only passed by value?

prprcupofcoffee Over a year ago

In VBA/VB6, procedure arguments of any type are passed by reference unless qualified as ByVal.

HansUp Over a year ago

@Nate Check out the Access help topic for the ByRef keyword and Function Statement.

Nate Over a year ago

@David It would explain how this code seems to run without error. Do you know if this is this also true for newer versions of VB?

Nate Over a year ago

@HansUp Thanks, you're answer basically answers my query fully.

|

Collectives™ on Stack Overflow

Variable scope using VB in MS Access

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related