How to write a query once and use it across multiple procedures

[ExcessionOCP]

Hi All,

I have been developing a little application for my own use, mainly to teach myself things but also to help out with a few things at work. I have got to the point to know enough to be dangerous but I am sure I am missing something relatively simple.

I have a form with multiple controls (combo boxes, list boxes, text fields etc) many of which have VBA associated with them to do things after they are updated or selected.

To do this, I have written a bunch of SQL queries and am storing them as strings within the relevant procedure using them to populate combo boxes, list boxes etc.



I have noticed that I have ended up with a LOT of duplicated SQL queries that do the same thing, but are called when different controls are interacted with. These SQL queries are in the form of strings that are declared and assigned withing the relevant procedure. This has become a bit of a pain to manage because if I need to update the query then I have to update each string in each procedure, something that takes time and is prone to error. Also, this feels really ugly and inefficient to me.


I have tried to assign the SQL statements to the string variables within a public procedure in a module, which works for static queries, but most of these SQL commands take values from other controls on my form, which don't exist when the string is declared, so these don't work.

I have tried writing a function that accepts the relevant control value as an input, executes the query and returns a recordset, but I am unable to use the returned recordset as the source for a combo box, something that I need to do regularly.


What am I missing here? Can anyone teach me a better way to be able to write a query once, then have the result of that query (which will contain multiple control values as parameters) and then use the results of that query to dynamically populate a listbox, a combo box or a text box?

Thanks in advance.

[Minty]

It sounds like you have a base query that is then modified with different where clauses?

If that the case You could do something like (replacing the various bits you need obviously - and this is air code untested)

Const strBaseSQL = "SELECT Fields from MyTable "   ''' Your base query SQL
Dim strWhere    as String
Dim strOrderBy  as String

Then where you need to

strWhere = " Field2 = " & Me.form control
strOrderBy= " Field3 Desc "
Debug.print strBaseSQL  & StrWhere & strOrderBy
Me.ComboBox1.Rowsource = strBaseSQL  & StrWhere & strOrderBy

Take note of the spaces between the elements to make the whole thing a sensible SQL query string.

[ExcessionOCP]

Thanks Minty.

That's kind-of it, but I use the same query in multiple subroutines, and I haven't found a way to write it once, store it somewhere, and then use it in other subroutines (modified using your suggestion as necessary). Is this even possible? Or should I stop caring and just live with the fact that the string is duplicated.

I guess it's only during development that I need to change things, so it's potentially a time-limited challenge, but I can't escape the feeling that I'm not doing things "properly" (for various values of "properly" )

[Minty]

If you declare the constant in a general module at the top before any functions or subs it will be available everywhere.
You could also do the same with the string variables, just ensure you always set them to nothing or "" at the end of each use otherwise you could get unexpected results.

So something like

Option Compare Database
Option Explicit

Const strBaseSQL = "SELECT Fields from MyTable "   ''' Your base query SQL
Dim strWhere    as String
Dim strOrderBy  as String

___________________________________________

Function fncMyFirstFunction(iLong as Integer) as Boolean
.....

[kd2017]

There's a lot of ways to go about this...

I have noticed that I have ended up with a LOT of duplicated SQL queries that do the same thing, but are called when different controls are interacted with. These SQL queries are in the form of strings that are declared and assigned withing the relevant procedure. This has become a bit of a pain to manage because if I need to update the query then I have to update each string in each procedure, something that takes time and is prone to error. Also, this feels really ugly and inefficient to me.

Can you show us an example for review? (A form's entire code module, not just snippits)

[ExcessionOCP]

Thanks both Minty and KD2017. Minty, I have had some success with setting the variables in the general module, but in cases where the SQL query contains a variable that has yet to be set, the queries don't work due to the variable being empty when the string is subsequently used later in my code.

Kd2017, here is a snippet of one of my queries - please excuse the basic and poorly-written nature of these - I know that these queries are subject to possible injection attacks but this isn't a concern at the moment and I plan to move to parametric queries once my understanding improves!

Private Sub cmbWorkPackageName_AfterUpdate()


ProjectID = Me.cmbProjectName
WPID = Me.cmbWorkPackageName
    'Populate the Cost Category combo based on ProjectID and WorkPackage values pulled from the relevant combos


    
sBudgetlistWP = "SELECT [Projects.ProjectID],[Projects.ProjectName], [CostType.CostTypeID],[CostType.CostTypeDescription], [Budgets.Budget], [Budgets.WorkPackageID] " & _
                " FROM CostType " & _
                " INNER JOIN (Projects INNER JOIN Budgets ON Projects.ProjectID = Budgets.ProjectID) ON CostType.CostTypeID = Budgets.CostTypeID " & _
                " WHERE [Budgets.WorkPackageID] = " & WPID & _
                " AND [Projects.ProjectID] = " & ProjectID
                    
Me.lstBudgets.RowSource = sBudgetlistWP
    
Me.lstBudgets.Requery
End Sub

My ideal solution would be along the lines of writing a function that can have the two variables passed to it, execute the query, then return the data in the query (as a recordset?) which is then used to populate the values of the listbox. I'm not sure if I'm barking up completely the wrong tree here though.

Cheers both.

[Minty]

You can pass a form and a control into a function as well as your criteria. Have a google on the methodologies.

[ExcessionOCP]

Ah! That sounds interesting! Thanks Minty, I'll have a search around and see what I find. Thanks for the hint.

[ExcessionOCP]

Hi Minty,

When you say "pass a control into a function" do you mean that I can pass a control that I want to be updated by the function, into that function?

[kd2017]

Thanks both Minty and KD2017. Minty, I have had some success with setting the variables in the general module, but in cases where the SQL query contains a variable that has yet to be set, the queries don't work due to the variable being empty when the string is subsequently used later in my code.

Kd2017, here is a snippet of one of my queries - please excuse the basic and poorly-written nature of these - I know that these queries are subject to possible injection attacks but this isn't a concern at the moment and I plan to move to parametric queries once my understanding improves!

Private Sub cmbWorkPackageName_AfterUpdate()


ProjectID = Me.cmbProjectName
WPID = Me.cmbWorkPackageName
    'Populate the Cost Category combo based on ProjectID and WorkPackage values pulled from the relevant combos


    
sBudgetlistWP = "SELECT [Projects.ProjectID],[Projects.ProjectName], [CostType.CostTypeID],[CostType.CostTypeDescription], [Budgets.Budget], [Budgets.WorkPackageID] " & _
                " FROM CostType " & _
                " INNER JOIN (Projects INNER JOIN Budgets ON Projects.ProjectID = Budgets.ProjectID) ON CostType.CostTypeID = Budgets.CostTypeID " & _
                " WHERE [Budgets.WorkPackageID] = " & WPID & _
                " AND [Projects.ProjectID] = " & ProjectID
                    
Me.lstBudgets.RowSource = sBudgetlistWP
    
Me.lstBudgets.Requery
End Sub

My ideal solution would be along the lines of writing a function that can have the two variables passed to it, execute the query, then return the data in the query (as a recordset?) which is then used to populate the values of the listbox. I'm not sure if I'm barking up completely the wrong tree here though.

Cheers both.

You could do something like this:

Public Sub example1(cbo As ComboBox, ProjectID As Long, WPID As Long)
    Dim qry As String
    
    qry = "SELECT [Projects.ProjectID],[Projects.ProjectName], [CostType.CostTypeID],[CostType.CostTypeDescription], [Budgets.Budget], [Budgets.WorkPackageID] "
    qry = qry & vbCrLf & " FROM CostType "
    qry = qry & vbCrLf & " INNER JOIN (Projects INNER JOIN Budgets ON Projects.ProjectID = Budgets.ProjectID) ON CostType.CostTypeID = Budgets.CostTypeID "
    qry = qry & vbCrLf & " WHERE [Budgets.WorkPackageID] = " & WPID
    qry = qry & vbCrLf & " AND [Projects.ProjectID] = " & ProjectID
    
    
    'Debug.Print qry 'for testing purposes
    
    cbo.RowSource = qry
    
    cbo.Requery 'I could be wrong but I thought when you change a row source it's automatically requeried??
End Sub


Private Sub cmbWorkPackageName_AfterUpdate()

    example1 Me.lstBudgets, Me.cmbProjectName, Me.cmbWorkPackageName

End Sub

Or you could do it this way which would probably be the way I prefer between these two:

ublic Function example2(ProjectID As Long, WPID As Long) As String
    Dim qry As String
    
    qry = "SELECT [Projects.ProjectID],[Projects.ProjectName], [CostType.CostTypeID],[CostType.CostTypeDescription], [Budgets.Budget], [Budgets.WorkPackageID] "
    qry = qry & vbCrLf & " FROM CostType "
    qry = qry & vbCrLf & " INNER JOIN (Projects INNER JOIN Budgets ON Projects.ProjectID = Budgets.ProjectID) ON CostType.CostTypeID = Budgets.CostTypeID "
    qry = qry & vbCrLf & " WHERE [Budgets.WorkPackageID] = " & WPID
    qry = qry & vbCrLf & " AND [Projects.ProjectID] = " & ProjectID
    
    
    example2 = qry
End Function


Private Sub cmbWorkPackageName_AfterUpdate()

    Me.lstBudgets.RowSource = example2(Me.cmbprojectname, Me.cmbWorkPackageName)
    Me.lstBudgets.Requery

End Sub

[ExcessionOCP]

Awesome! Thanks! I'll give them both a go and let you know. Out of interest (and for the purposes of education) - why do you prefer the latter?

[kd2017]

It's a little more explicit/clear as to what's going on.

I'm not a 'functional programmer' but I have been trying to incorporate the functional programming style more into my projects.

The sub in example one does two things.
1. it builds a row source value
2. it updates the row source value of a control

The function in example two does one thing:
1. it builds a row source value

Example two is simpler, it will be more reusable, and easier to debug. When you come back to make changes next month and you review the _AfterUpdate() code it's more clear what's going on... to me anyway.

I try to make all my subs/functions be really good at doing just one thing.

(edit)
btw, I don't know if you've already explored this option or if it has been discussed, but you can build this query in query designer and save it and just have your control's row source refer to that query. Said queries can pull variable criteria from forms at run time.

(edit x2)
fyi I've been trying to rely less on global variables as I've learned more about functional style.

[kd2017]

Here is an example of the third option I referred to in the edit of my last post. This is probably the most "MS Accessy" way to do it, but then the query is dependent on the specific form.
Example.accdb

[ExcessionOCP]

Thanks for the explanation! I have tried your second (preferred) approach and it works brilliantly! Thanks so much for your help, this feels like such a better way of doing it

[Micron]

A sub or function can take just about anything as input; the major difference being that a sub cannot return a value to a variable that calls it. So this will not work if mySub is a sub:
myVariable = mySub(input1, input2) where inputs (parameters) are objects such as controls, references to controls, variable values and the like.

This call would be OK
myVariable = myFunction(input1, input2). Functions and subs don't have to take parameters.

As mentioned, many ways to solve such issues. I'd pass the control value rather than the object itself if all I needed from it was it's value but that's just my preference. For your situation, a procedure that also takes optional parameters might be useful. For example, if the main SELECT part is the same for the most part, then you can pass that to a function as a variable and concatenate the parameters with values, as in

myFunction(param1 As something, Optional param2 As something, param3 As something, ...) As String [Note, all parameters after Optional must be optional].

With that, you could concatenate a sql statement with or without the options and return it to a string variable (note As String at the end) in your calling code.