Page 1 of 2 12 LastLast
Results 1 to 15 of 16

VBA SQL statement help

  1. #1
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26

    VBA SQL statement help

    I have a form along with a subform. The form is used as a query form to select parameters from a few drop down boxes and the subform is used to display the results of the query. I need to have the subform editable by users.



    The data is coming from a SQL database. Both forms are working as intended. HOWEVER once I realized I needed to place primary keys on the SQL table to make the subform editable the query form (main form) no longer is fetching the correct data. In fact it only retrieves part of the first record in the table (some fields are blank even thought they have data in the table)

    I'm thinking it has something to do with the VBA code. Like I said, this all worked great until I added primary keys to the SQL table. The primary keys were added to the existing fields in the table so no new fields were introduced.


    Private Sub Cmd_RunQuery_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteriaHull As String
    Dim strCriteriaWS As String
    Dim strCriteriaLeadDept As String
    Dim strCriteriaPhase As String
    Dim CalcSSBegin As Date
    Dim CalcSSEnf As Date
    Dim strSQL As String
    Dim qryDef As QueryDef





    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryform")


    For Each varItem In Me!cmboHull.ItemsSelected
    strCriteriaHull = strCriteriaHull & ",'" & Me!cmboHull.ItemData(varItem) & "'"
    Next varItem


    For Each varItem In Me!cmboWS.ItemsSelected
    strCriteriaWS = strCriteriaWS & ",'" & Me!cmboWS.ItemData(varItem) & "'"
    Next varItem


    For Each varItem In Me!cmboLeadDept.ItemsSelected
    strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!cmboLeadDept.ItemData(varItem) & "'"
    Next varItem

    For Each varItem In Me!cmboPhase.ItemsSelected
    strCriteriaPhase = strCriteriaPhase & ",'" & Me!cmboPhase.ItemData(varItem) & "'"
    Next varItem



    If Len(strCriteriaHull) > 0 Then
    strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
    strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
    End If


    If Len(strCriteriaWS) > 0 Then
    strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
    strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ") AND "
    End If


    If Len(strCriteriaLeadDept) > 0 Then
    strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
    strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") AND "
    End If


    If Len(strCriteriaPhase) > 0 Then
    strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)
    strSQL = strSQL & "dbo_tblPrintCenter.Phase IN (" & strCriteriaPhase & ") AND "
    End If


    'Check for a blank value
    If IsNull(Me![txtCalcSSBegin]) Then
    MsgBox "You must supply a SS Date"
    Cancel = True
    Exit Sub
    End If

    If IsNull(Me![txtCalcSSEnd]) Then

    MsgBox "You must supply a SS Date"
    Cancel = True
    Exit Sub
    End If



    'strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
    'strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
    'strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
    'strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)



    strSQL = " SELECT * FROM dbo_tblPrintCenter WHERE "
    If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
    If Len(strCriteriaWS) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ") AND "
    If Len(strCriteriaLeadDept) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") And "
    If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.SSPhase IN (" & strCriteriaPhase & ") AND "
    strSQL = strSQL & "dbo_tblPrintCenter.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"


    DoCmd.SetWarnings False



    Set qryDef = db.QueryDefs("qryform")
    qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
    Me!qryformsubform.Form.RecordSource = "qryform"
    'Set the object variables to Nothing to ensurethat these are cleared from the memory
    Set db = Nothing
    Set qdf = Nothing


    End Sub

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,242
    don't think adding your primary key would make a difference to VBA. Did you refresh your linked table?

    Only other comments are

    1. your date fields - if you use the US format of mm/dd/yyyy then should not be a problem, otherwise you need to format your dates to the US format
    2. don't see why you are building your strsql twice e.g.

    If Len(strCriteriaHull) > 0 Then
    strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
    strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
    End If

    and

    If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
    3. why do you assign the sql to a query and then the query to the form recordsource - why not just assign the sql to the form recordsource? Or assign the query recordset to the form recordset

  3. #3
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26
    Quote Originally Posted by Ajax View Post
    don't think adding your primary key would make a difference to VBA. Did you refresh your linked table?

    Only other comments are

    1. your date fields - if you use the US format of mm/dd/yyyy then should not be a problem, otherwise you need to format your dates to the US format
    2. don't see why you are building your strsql twice e.g.



    3. why do you assign the sql to a query and then the query to the form recordsource - why not just assign the sql to the form recordsource? Or assign the query recordset to the form recordset
    Yes I relinked the table multiple times

    1. I'm using US Format
    2. I'm not sure. I had to get help with the SQL statement and this is what worked. I'm a VBA noob so I know no better
    3.
    I'm a VBA noob so I know no better I hate to change anything at this point until I can get it to work considering it was working fine until I made the primary keys.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,021
    Here's the standard trouble shooting suggestion - prove the sql is correct. After
    qryDef.SQL = strSQL & " " & strWhere & " " & strOrder put
    Debug.Print strSQL & " " & strWhere & " " & strOrder

    Copy output from immediate window and paste into sql view of a new query and test it. If it is an action query, switch to datasheet view if you just want to see any effect. It would show you what rows will be affected, not what the effect will be. You can also examine it in design view. Either way, the point is to examine the query to see if the constructed sql is correct.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,242
    Yes I relinked the table multiple times
    and if you open that table you see data as it should be? - the whole table not just the first few rows. Sort on each column one by one to check for data integrity issues

  6. #6
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26
    Quote Originally Posted by Micron View Post
    Here's the standard trouble shooting suggestion - prove the sql is correct. After
    qryDef.SQL = strSQL & " " & strWhere & " " & strOrder put
    Debug.Print strSQL & " " & strWhere & " " & strOrder

    Copy output from immediate window and paste into sql view of a new query and test it. If it is an action query, switch to datasheet view if you just want to see any effect. It would show you what rows will be affected, not what the effect will be. You can also examine it in design view. Either way, the point is to examine the query to see if the constructed sql is correct.
    Yes it works fine however the subform is not picking up the results. For instance below I told it to pick hull 2619 but the first record in the table is 5350 and that is what it displays. In fact it seems like its frozen as it no longer is trying to use the SQL query anymore.

    SELECT * FROM dbo_tblPrintCenter WHERE dbo_tblPrintCenter.hull IN ('2619') AND dbo_tblPrintCenter.CalcSS BETWEEN #1/1/2016# AND #7/31/2019#

  7. #7
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26
    Quote Originally Posted by Ajax View Post
    and if you open that table you see data as it should be? - the whole table not just the first few rows. Sort on each column one by one to check for data integrity issues

    Yes table is as it should be.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,021
    If that query test returns the correct record and you are going to modify the record source of a subform you will have to requery the subform after setting its recordsource. Presumably the linked child/master fields are correctly set between them.

    Forgot to request that you post lengthy code between code tags (# on menubar) and indent accordingly. You can also go back and select your code and click that button to edit your post. What you have is too hard to read. I agree with Ajax; there is some unnecessary repetition there.

  9. #9
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26
    Hey guys I decided my date range should be optional instead of mandatory. I'm now getting an error (Syntax error in Date query expression...). I commented out the part that checks for a blank value and I believe the error is coming from the SELECT statement.


    Code:
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteriaHull As String
    Dim strCriteriaWS As String
    Dim strCriteriaLeadDept As String
    Dim strCriteriaPhase As String
    Dim CalcSSBegin As Date
    Dim CalcSSEnf As Date
    Dim strSQL As String
    Dim qryDef As QueryDef
    
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryform")
    
    For Each varItem In Me!lstHull.ItemsSelected
       strCriteriaHull = strCriteriaHull & ",'" & Me!lstHull.ItemData(varItem) & "'"
    Next varItem
    
    For Each varItem In Me!lstWS.ItemsSelected
       strCriteriaWS = strCriteriaWS & ",'" & Me!lstWS.ItemData(varItem) & "'"
    Next varItem
    
    For Each varItem In Me!lstLeadDept.ItemsSelected
       strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!lstLeadDept.ItemData(varItem) & "'"
    Next varItem
    
    For Each varItem In Me!lstSSPhase.ItemsSelected
       strCriteriaPhase = strCriteriaPhase & ",'" & Me!lstSSPhase.ItemData(varItem) & "'"
       Next varItem
    
    
    If Len(strCriteriaHull) > 0 Then
      strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
      strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
    End If
    
    If Len(strCriteriaWS) > 0 Then
      strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
      strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ") AND "
    End If
    
    If Len(strCriteriaLeadDept) > 0 Then
      strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
      strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") AND "
    End If
    
    If Len(strCriteriaPhase) > 0 Then
      strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)
      strSQL = strSQL & "dbo_tblPrintCenter.Phase IN (" & strCriteriaPhase & ") AND "
    End If
    
     'Check for a blank value
    '    If IsNull(Me![txtCalcSSBegin]) Then
    '        MsgBox "You must supply a SS Date"
    '        Cancel = True
    '    Exit Sub
    '    End If
    '
    '
    '    If IsNull(Me![txtCalcSSEnd]) Then
    '       MsgBox "You must supply a SS Date"
    '       Cancel = True
    '    Exit Sub
    '  End If
    
    
    
    strSQL = " SELECT * FROM dbo_tblPrintCenter WHERE "
        If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
        If Len(strCriteriaWS) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ") AND "
        If Len(strCriteriaLeadDept) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") And "
        If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.SSPhase IN (" & strCriteriaPhase & ") AND "
        strSQL = strSQL & "dbo_tblPrintCenter.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
    
    DoCmd.SetWarnings False
    
    Set qryDef = db.QueryDefs("qryform")
       
    qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
    
    Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL
    
    
    'Set the object variables to Nothing to ensurethat these are cleared from the memory
     
    Set db = Nothing
     Set qdf = Nothing
    
    
    Forms![Print Request Search Form]![dbo_tblPrintCenter subform].Form.Requery
    End Sub

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,021
    I decided my date range should be optional instead of mandatory
    Not sure I understand. If the dates are now optional, why are you not using an If statement for dates like you are for the other options?
    Code:
        If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.SSPhase IN (" & strCriteriaPhase & ") AND "
        strSQL = strSQL & "dbo_tblPrintCenter.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
    And you get the error even if you provide dates, or just when you don't? Like I said earlier - output the sql and test. If you are in sql view when it fails, it often highlights the offending portion. However, that assumes that the issue isn't obvious once you have a chance to look at the sql in its entirety. This is basic troubleshooting that you should be adopting. If you already did that, then it would be best to say so, otherwise I'm wasting your time and mine suggesting it.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  11. #11
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26
    I'm only getting the error when I don't supply a date. I tried doing the IF statement but having the getting the same error when I don't supply a date.

    Code:
    'strSQL = strSQL & "dbo_tblPrintCenter.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
        If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
    Here is what the immediate window is showing when I don't supply a date

    Code:
     SELECT * FROM dbo_tblPrintCenter WHERE dbo_tblPrintCenter.hull IN ('2619','4019') AND dbo_tblPrintCenter.LeadDept IN ('07') And dbo_tblPrintCenter.SSPhase IN ('001') AND dbo_tblPrintCenter.CalcSS BETWEEN #1/1/2016# AND ##


    Here is what it shows when I supply dates (I get no errors)
    Code:
     SELECT * FROM dbo_tblPrintCenter WHERE dbo_tblPrintCenter.hull IN ('2619','4019') AND dbo_tblPrintCenter.LeadDept IN ('07') And dbo_tblPrintCenter.SSPhase IN ('001') AND dbo_tblPrintCenter.CalcSS BETWEEN #1/1/2016# AND #1/1/2019#



  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,021
    You didn't ask any questions in your last post so I'm not sure if it means the problem has become obvious, because it is to me. Your second date field is not returning a date:
    BETWEEN #1/1/2016# AND ##
    You'd have the same failure if the first had no date or neither had a date if you don't validate the date fields and code accordingly.

    Try
    Code:
    If Len(Me.txtCalcSSBegin)>0 And Len(Me.txtCalcSSEnd) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
    to ensure there are 2 dates. You'd have to tweak the IF and the resulting sql append if you're going to allow just one date.
    If there are 2 dates, you have a different issue that is causing the 2nd date to not be picked up. Not that it really matters but Len is not how I usually validate a form control for data. I felt it best to follow your style.

  13. #13
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26
    I don't quite yet have a style so everything you see is pieced together. that's probably why it looks like crap.

    I just don't know how to account for a user only entering only 1 date or none. Either way I get this:

    Code:
    SELECT * FROM dbo_tblPrintCenter WHERE dbo_tblPrintCenter.hull IN ('2618') AND dbo_tblPrintCenter.SSPhase IN ('001') AND
    I entered 1 date (
    CalcSSBegin) and as you can see its not even accounting for it in the WHERE clause.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,021
    Code:
    I just don't know how to account for a user only entering only 1 date or none.
    If that's what you want to allow, I'm sure I can help with that. This is a search form? Then consider http://allenbrowne.com/ser-62.html as a possible ready made solution, or we can work with yours. If yours, then the user has the option of providing
    - only a start date, or
    - only an end date, or
    - both dates, or
    - no dates?
    It already seems apparent that other fields are optional and may or may not contain several values, hence the loop through what I guess is a listbox.
    Consider posting a zipped copy of your db if sticking with it.
    If yes, what is the comparison for single dates, =, >= or > ? Each would be different (e.g. >= start date but end date would be <= )

  15. #15
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    26
    Yes both dates are optional and in fact all fields are considered optional. However the user will know to select at least the hull number(s) as they know their is a lot of data.

    I see some logic issues with my previous statement. Logically it doesn't make sense for the user to enter just 1 date. So they will either need to enter both dates or none. That is what I need to do.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. if statement
    By joym in forum Access
    Replies: 3
    Last Post: 05-03-2017, 03:44 PM
  2. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  3. iif Statement Help
    By smc678 in forum Forms
    Replies: 8
    Last Post: 12-11-2012, 11:02 AM
  4. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  5. Replies: 7
    Last Post: 08-17-2011, 01:49 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
  •  
Tech Forums: Microsoft Office Forums