Results 1 to 15 of 15
  1. #1
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    31

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

    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.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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)


    Code:
    Const strBaseSQL = "SELECT Fields from MyTable "   ''' Your base query SQL
    Dim strWhere    as String
    Dim strOrderBy  as String
    Then where you need to

    Code:
    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.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    31
    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" )

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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


    Code:
    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
    .....
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    There's a lot of ways to go about this...

    Quote Originally Posted by ExcessionOCP View Post
    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)

  6. #6
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    31
    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!

    Code:
    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.

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can pass a form and a control into a function as well as your criteria. Have a google on the methodologies.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    31
    Ah! That sounds interesting! Thanks Minty, I'll have a search around and see what I find. Thanks for the hint.

  9. #9
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    31
    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?

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by ExcessionOCP View Post
    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!

    Code:
    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:

    Code:
    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:

    Code:
    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

  11. #11
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    31
    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?

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    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.

  13. #13
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    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

  14. #14
    ExcessionOCP is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    31
    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

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-13-2017, 03:28 PM
  2. Write Multiple file names to a text box
    By PoolHallJunkie in forum Programming
    Replies: 4
    Last Post: 09-16-2014, 01:16 PM
  3. Replies: 7
    Last Post: 02-26-2013, 02:26 PM
  4. Replies: 2
    Last Post: 10-25-2012, 05:36 AM
  5. Help making a form write to multiple tables
    By shiphtfour in forum Forms
    Replies: 2
    Last Post: 01-15-2011, 04:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums