How can I create a shared function in ms access VBA?

Question

I have a form that runs some sql on the form load and three sort buttons on the form that all repeat the sql in their own private function. The only difference is the sort order. When I need to update the where clause, I have to update it 4 times. I would like to created a parameterized function like

function getFormRecs(arg){
    sql...
    . 
    .
    .
    order by 'arg'
{

where arg is the argument passed in from the form control or event.

Then I would like the form load and the 3 sort buttons on the form to call the function getFormRecs(), while passing in the arg, like

getFormRecs('formload')
getformRecs('sorta')
getformRecs('sortb')
getformRecs('sortc')

I would define the correct sort field in the top of the function based on the arg passed in.

The end result is simply to reuse a function or Sub-routine that contains nearly identical long winded sql.

Daniel · Accepted Answer · 2013-10-03 20:15:55Z

2

This seems to be about what you're asking for:

Function getFormRecs(ByVal arg As String)
    getFormRecs = "sql ... " & _
                  "." & _
                  "." & _
                  "." & _
                  "order by " & arg
End Function
Private Sub Form_Load()
    Dim sql As String
    sql = getFormRecs("formload")
    '... Logic that uses sql
End Sub

answered Oct 3, 2013 at 20:15

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

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

Comments

HansUp · Accepted Answer · 2013-10-03 20:16:58Z

2

I'm unsure why you need a function. You could start with a base query which does not include an ORDER BY.

SELECT id, fld2, fld3
FROM YourTable;

Then re-use that same query elsewhere by including it as the data source in a new SELECT statement where you add the specific ORDER BY you require.

SELECT q.id, q.fld2, q.fld3
FROM qryBase AS q
ORDER BY q.fld3;

answered Oct 3, 2013 at 20:16

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Collectives™ on Stack Overflow

How can I create a shared function in ms access VBA?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related