Results 1 to 11 of 11

Using a database query in VBA code

  1. #1
    Rin is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    9

    Question Using a database query in VBA code

    I have a query in the DB that runs fine when I use it by itself. When I put it on a code block, it throws an error that asks for a parameter. You can see in the code block below that there's a comment line with the query followed by a line with the SQL instead. Both throw the same error. What's up with this?

    Private Sub cmbRegion_Change()
    Dim dbsRegionTotals As DAO.Database
    Dim rstRegionTotals As DAO.Recordset

    Set dbsRegionTotals = CurrentDb
    'Set rstRegionTotals = dbsRegionTotals.OpenRecordset("qryRegionEmailCount ")


    Set rstRegionTotals = dbsRegionTotals.OpenRecordset("SELECT Count(tblAgents.Email) AS CountOfEmail " & _
    "FROM tblAgents LEFT JOIN tblRegionRank ON (tblAgents.Brokerage = tblRegionRank.Brokerage) " & _
    "AND (tblAgents.LastName = tblRegionRank.LastName) AND (tblAgents.FirstName = tblRegionRank.FirstName) " & _
    "WHERE (((tblRegionRank.Region)=[Forms]![frmRegionData]![cmbRegion]));")
    txtRegionEmailAddresses = rstRegionTotals!CountOfEmail

    rstRegionTotals.Close 'Closes the recordset, but leaves it in memory to be re-opened later.
    Set rstRegionTotals = Nothing 'Releases the recordset from memory
    End Sub

    Thanks,

    Rin

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    put debug.print when you have issues and look at the sql (or whatever you're concatenating) in the immediate window. In this case I think you will find the issue to be the lack of a space at the last word in each line, or the lack of one at the beginning of the next line. Thus you'll have
    AS CountOfEmailFROM tblAgents
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  3. #3
    Rin is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    9
    Thanks for that. Actually I have it in the actual code block without the concatenation. I concatenated it here because otherwise it didn't fit well. In other words, concatenation isn't the problem. Look at the line above the SQL that I commented out. That's actually a better way of doing this. using the query from the DB, and it doesn't work either.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    I missed the statement that they both create the same message. That seems coincidental. In the vba version, the way you've included the form reference as part of the whole sql statement means that the ACE expression service will use exactly those words. You need to concatenate such references as in
    WHERE (((tblRegionRank.Region)=" & [Forms]![frmRegionData]![cmbRegion] & "));")

    If the reference data type is text or date you must wrap it in text or date delimiters (' or #)
    In other words, concatenation isn't the problem
    What you're doing is line continuation, not concatenation, which is what's going on with the example I corrected. Concatenation almost always involves & between the syntax and a variable, object name, function call, etc. and the rest of the syntax. FWIW, I never use line continuation. I have found it to be wonky.

    Now for the query itself. Depending on the type of query, you usually will get that prompt if the form reference cannot be resolved or if a field/domain name is misspelled.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    Reason for the interrupted posts is because of what's going on around here, plus needing the time to compose more information. First, let me clarify that I realize the line continuation character is _ and that you are in a manner, concatenating interrupted lines of code. The intent of my meaning is that concatenation in the usual sense (stringing together code or sql syntax and variable or object names) is at least one of your issues - that your control reference just becomes a literal inclusion in the sql construct.

    For what it's worth, here's a couple of view points (I tend to leave out the superfluous parentheses that Access likes):

    Code:
    Dim sql As String
    sql = "SELECT Count(tblAgents.Email) AS CountOfEmail FROM tblAgents LEFT JOIN "
    sql = sql & "tblRegionRank ON tblAgents.Brokerage = tblRegionRank.Brokerage AND "
    sql = sql & "tblAgents.LastName = tblRegionRank.LastName AND tblAgents.FirstName = "
    sql = sql & "tblRegionRank.FirstName WHERE tblRegionRank.Region = "
    sql = sql & [Forms]![frmRegionData]![cmbRegion] & ";")
    
    Set rstRegionTotals = dbsRegionTotals.OpenRecordset(sql)
    
    Me.txtRegionEmailAddresses = rstRegionTotals!CountOfEmail
    This is how I piece together long sql statements. Note that the end of each line contains a separating space. Some elect to put it at the front of the following lines but the point is to be consistent. Note also that the reference to the form control does not start with quotes but that line does end with a quoted portion. Let's say that the form control contains the value "6" and is a number, not text or a date. Your sql is
    WHERE (((tblRegionRank.Region)=[Forms]![frmRegionData]![cmbRegion]))

    whereas it should be
    WHERE (((tblRegionRank.Region)=6))

    The properly concatenated sql would be WHERE (((tblRegionRank.Region)=6)). Thus you are "joining" literal portions of statements to portions which are to be interpreted as data. If you include the references within quotes, they become literal strings, not references that hold data values.

    As for the rest, if code fails in such a way that causes the recordset to not open, you will generate an error when you try to close it. You should use error trapping in all but the simplest procedures. Here, the least I would do would be

    On Error Resume Next
    rstRegionTotals.Close
    Set rstRegionTotals = Nothing

    Anything that gets created by SET should be released as well

    Set dbsRegionTotals = Nothing
    Last edited by Micron; 11-30-2018 at 06:43 PM. Reason: spelin and gramur
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,538
    @Micron unpaired paren at the end of your SQL concatenation.

    Names make very poor unique identifiers. Why aren't you linking on an AgentID?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  7. #7
    Rin is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    9
    Thanks for the help guys, but you're missing the real problem altogether.

    Check the line that I commented out. That's the line in question.
    Set rstRegionTotals = dbsRegionTotals.OpenRecordset("qryRegionEmailCount ")

    The line with the SQL in it is simply to show you what's in the database query named in the commented out line - that is "qryRegionEmailCount".

    When I run that in the native Access, it runs fine. When I put it in the VBA code block it throws an error asking for a parameter. I don't know what parameter it's looking for. When I copy and paste the SQL from the query into the code block, without line continuation, I get the same error, which makes sense since it's exactly the same query.

    Here's how it looks at this very moment in the VBA module:

    Set dbsRegionTotals = CurrentDb
    'Set rstRegionTotals = dbsRegionTotals.OpenRecordset("qryRegionEmailCount ")
    Set rstRegionTotals = dbsRegionTotals.OpenRecordset("SELECT Count(tblAgents.Email) AS CountOfEmail FROM tblAgents LEFT JOIN tblRegionRank ON (tblAgents.Brokerage = tblRegionRank.Brokerage) AND (tblAgents.LastName = tblRegionRank.LastName) AND (tblAgents.FirstName = tblRegionRank.FirstName) WHERE (((tblRegionRank.Region)=[Forms]![frmRegionData]![cmbRegion]));")
    txtRegionEmailAddresses = rstRegionTotals!CountOfEmail

    @June7, I don't know what it means to be linking on an AgentID.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,538
    AgentID is a suggested name of a unique identifier field you would create and use as primary key. This could be an autonumber type. Then this value would be saved in dependent tables as a foreign key. The tables would link on these fields. Not only are names poor unique identifiers, text values are less efficient in indexing.

    Did you try suggestion for concatenating the control reference outside the quote marks?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  9. #9
    Rin is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    9
    OK, @June7, you're right. The control reference has to be outside of the quote marks. So I added a string variable and assigned it the value of the control and replaced the control reference in the SQL with the string variable. That worked.

    So the fact that the database query, "qryRegionEmailCount", had the reference to the control kept it form being usable in the line:
    Set rstRegionTotals = dbsRegionTotals.OpenRecordset("qryRegionEmailCount ")


    Thanks SOOOOOO much for being patient with me.

    ~Rin

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    Sorry, I think you are the one missing the point, but hey, I've been in this spot before and was the one missing the point. Don't think so this time.

    The point I think you're missing is that you cannot simply paste sql from a query into vba when that query sql contains something like a form reference. The Access side of things will figure out what [Forms]![frmRegionData]![cmbRegion] is as long as the form is open, the names are correct, etc. The vba side cannot (that's the ACE I referred to). AFAIK, it was formerly referred to as JET and includes the Expression Service, which figures out what data you're trying to get from the object (forms/Access) side of things.

    You must have

    "first part of sql" & [Forms]![frmRegionData]![cmbRegion] & "next part of sql"

    not

    "
    first part of sql [Forms]![frmRegionData]![cmbRegion] next part of sql"

    which is what you have in your form code. Access (vba side) will figure out that you're trying to get records based on some criteria, but it doesn't know what that is because you've wrapped it up inside of your sql. You can also get the same message in other situations, but so far, what I've been saying seems to be the most likely cause at this point. Note that my explanation is for when the data type for the control is a number. If it's not, then you need delimiters as I've mentioned.

    Regardless of what you 'link' on, the primary problem needs to be sorted out. Again, sorry if I'm the one missing the point but I do think you need to properly concatenate the sql parts and the form control reference. You also have the option to simply call the query that works, no?

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    Quote Originally Posted by Rin View Post
    OK, @June7, you're right.
    ~Rin
    Wow. Ain't that a kick in the teeth. Said that in post 4 and even provided sample code on that very premise.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-19-2017, 08:00 AM
  2. Replies: 12
    Last Post: 08-23-2016, 08:22 AM
  3. Replies: 8
    Last Post: 01-09-2016, 06:07 AM
  4. Replies: 1
    Last Post: 05-04-2013, 11:19 AM
  5. Replies: 3
    Last Post: 04-05-2006, 03:17 PM

Tags for this Thread

Posting Permissions

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