Results 1 to 4 of 4
  1. #1
    Miked1978 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    38

    VBA SQL statement help

    I have a form with 2 combo boxes (Ship and Unit). When the user selects their criteria for each combo box I have a search button they press which will query a SQL table with their selections and the results will display in a subform. I'm having issues with the SQL code using BOTH combo box selections. Current code gives no errors but will only recognize what the user selected in the "Unit" combobox and not what they selected in the "ship"

    Any help would be greatly appreciated!!!



    Code:
    Private Sub cmdSearch_Click()
    Dim Varitem As Variant
    Dim strShip As String
    Dim StrUnit As String
    Dim strSQL As String
    
    ' Gets what Ship been selected
    For Each Varitem In Me!Cbo_Ship.ItemsSelected
       strShip = strShip & ",'" & Me!Cbo_Ship.ItemData(Varitem) & "'"
    Next
    
    
    'get selections from LeadDept multiselect listbox
    For Each Varitem In Me!cbo_Unit.ItemsSelected
       StrUnit = StrUnit & ",'" & Me!cbo_Unit.ItemData(Varitem) & "'"
    Next
    
    
    strSQL = "SELECT * FROM dbo_tbUnitPreAssembly_Master WHERE "
    strSQL = "Select * From dbo_tbUnitPreAssembly_Master where [Ship] = '" & Me.Cbo_Ship & "' AND "
    strSQL = "Select * From dbo_tbUnitPreAssembly_Master where [UnitType] = '" & Me.cbo_Unit & "'"
            
    DoCmd.SetWarnings False
    
    Me.tbUnitPreAssembly_Master_subform.Form.RecordSource = strSQL
    End Sub

  2. #2
    Miked1978 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    38
    NVM i got it working by changing my
    strSQL to this:

    strSQL = "SELECT * FROM dbo_tbUnitPreAssembly_Master WHERE "
    strSQL = strSQL & "dbo_tbUnitPreAssembly_Master.Ship = '" & Cbo_Ship & "' AND "
    strSQL = strSQL & "dbo_tbUnitPreAssembly_Master.UnitType = '" & cbo_Unit & "' "

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Miked1978 has pointed out one issue, but there are several more.

    Why do you have
    Code:
    DoCmd.SetWarnings False
    This line should be removed.

    Why are you looping through the combo boxes using the "ItemsSelected" property???
    Are the fields "Ship" and "Unit" multi-value fields (MVF)??

    It looks like there is a preceding comma in the variables strShip and strUnit.

    Maybe you would post your dB??

  4. #4
    Miked1978 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    38
    Quote Originally Posted by ssanfu View Post
    Miked1978 has pointed out one issue, but there are several more.

    Why do you have
    Code:
    DoCmd.SetWarnings False
    This line should be removed.

    Why are you looping through the combo boxes using the "ItemsSelected" property???
    Are the fields "Ship" and "Unit" multi-value fields (MVF)??

    It looks like there is a preceding comma in the variables strShip and strUnit.

    Maybe you would post your dB??
    I took it out. I had copied the code from another module and just left it in there.

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

Similar Threads

  1. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  2. 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
  3. Iif statement help
    By Firefighter22 in forum Queries
    Replies: 7
    Last Post: 09-30-2011, 10:59 AM
  4. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  5. if statement
    By sirlosi in forum Queries
    Replies: 4
    Last Post: 03-07-2011, 11:26 AM

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