control variable

[CJ_London]

I usually choose a function unless I'm really sure I just need a sub

So do I. Both subs and functions can return multiple values when passed as byRef parameters (the default), but a function can also return a value - typically a boolean. For example can be used like this


if somefunction(a,b,c)=true then debug.print a, b, c

function somefunction(a as string, b as long, c as date) as boolean

    somefunction=false
    'do something to modify the values of a, b and c

    'if all values modified OK then somefunction=true

end function

I've used something like this to optimise the size of a container (H/W/D) for a given volume to fit on a pallet.

 

[arnelgp]

here is another Textbox calculator demo.
open form1.
post #15 of https://www.access-programmers.co.uk/forums/threads/calculator.325703/#post-1946293

for the AfterUpdate of those two textbox:

Public Function RetAmt()
    Dim tbx As Control
    Set tbx = Screen.ActiveControl
    Select Case tbx.Name
        Case "txtPayment"
            tbx.Parent!Receipt = Null
            tbx.Parent!txtReceipt = Null
            tbx.Parent!Amount = -tbx.Value
        Case "txtReceipt"
            tbx.Parent!Payment = Null
            tbx.Parent!txtPayment = Null
            tbx.Parent!Amount = tbx.Value
    End Select
End Function

 

[Uncle Gizmo]

Good question. If I had to choose, I usually choose a function unless I'm really sure I just need a sub. But I really don't know what the disadvantages would be.

In Microsoft Access, you can write code as either a function or a subroutine. Functions can take parameters, while subroutines cannot.

Purists often argue that using a function without a parameter is sloppy when a subroutine would suffice. As an experienced developer, I lean toward always creating functions—it's easier to add a parameter later than to convert a subroutine into a function. Sure, we all plan to revisit our code and optimize it, swapping parameter-less functions for subs… but let’s be real, that rarely happens.

Here’s an interesting quirk:
You can call a function from a control’s property by prefixing it with an equal sign --- e.g., =MyFunction(), but you can’t do this with a subroutine. Why? I’ve got a theory:
Allowing subroutine calls this way could let an object’s code call itself, potentially triggering unintended recursion.

So MS Access itself forces you to use a function where a subroutine which suffice!

 

[CJ_London]

You can also use functions in a query, but not subs

 

[MajP]

Functions can take parameters, while subroutines cannot.

What???

 

[cheekybuddha]

Functions can take parameters, while subroutines cannot.

???

Did you get ChatGPT to write this?

 

[MajP]

• Sub Procedures perform actions but do not return a value to the calling code.
• Event-handling procedures are Sub procedures that execute in response to an event raised by user action or by an occurrence in a program.
• Function Procedures return a value to the calling code. They can perform other actions before returning.
  Property Procedures return and assign values of properties on objects or modules.
  

 

[AHeyne]

Is there a disadvantage to using a function over a sub?

That should not be the question. The question should be what it should do. See and follow Command Query Separation:

The fundamental idea is that we should divide an object's methods into two sharply separated categories:

• Queries: Return a result and do not change the observable state of the system (are free of side effects).
• Commands: Change the state of a system but do notreturn a value.

So both, functions and subs, are procedures. Both can have parameters (which could be used for quasi return values if declared ByRef), but only functions can have a return value.

You can also use functions in a query, but not subs

That is correct, but then take care to use an error handler in these functions that doesn't reraise the error as a function used in that way is a so called 'entry function' where code execution starts and can't bubble errors up to any higher procedure. The same belongs to functions which are used directly in event properties.

 

[cheekybuddha]

• Queries: Return a result and do not change the observable state of the system (are free of side effects).

Please clarify.

What about:

UPDATE SomeTable
SET
  Field1 = NULL
WHERE id = 123
;

-- Or:
DELETE FROM SomeTable
WHERE DateField < #2025-01-01#
;

?

 

[MajP]

The article is not talking about queries in a database sense. It is a "query" where you ask something and get something back without doing an action. It is talking about types of code structure that can either not change the state of the application or change the state.

A function that does not alter the state but returns a value is a "query" type of command.
But a function can be both doing "command" or "query"

 

[cheekybuddha]

The article is not talking about queries in a database sense. It is a "query" where you ask something and get something back without doing an action. It is talking about types of code structure that can either not change the state of the application or change the state.

Oops! Missed reading the article - thanks for the clarification!