Select 2 Columns in Access form Excel VBA

[Darren Smith]

This needs to select 2 Columns in Access. Column Names are Vehicle and HeightAndWidthGantry then filter by Vehicle Type.
To return the HeightAndWidthGantry Value.

This bit should select 2 Columns out of 10 in Access database Sheet

Source = "SELECT *FROM [GantryHeight&Width] " & _
   " WHERE [Vehicle]='" & Model_Type.Text & "'"

Private Sub Model_Type_Change()
    
TurnOff

With ThisWorkbook.Worksheets("Quote Detail")
        .ListObjects("Quote_Detail").AutoFilter.ShowAllData
        
        Me.WBase.Text = "Wheel Base"
        Me.Vehicle_Cab_Type.Text = "Cab Type"
        Me.Drivetrain.Text = "Drivetrain"
        Me.Rear_Wheels.Text = "Rear Wheels"
        
        
        If Me.Model_Type.Value <> "Model Type" Then _
            .ListObjects("Quote_Detail").Range.AutoFilter Field:=1, Criteria1:=Me.Model_Type.Value

    End With
    
        UpdateLists
        
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("GantryID")
ws.Range("A2:C5").ClearContents


DBFullName = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Access Files\DrNo Data Base.accdb"

Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect


Set Recordset = New ADODB.Recordset
With Recordset

Source = "SELECT *FROM [GantryHeight&Width] " & _
   " WHERE [Vehicle]='" & Model_Type.Text & "'"

.Open Source:=Source, ActiveConnection:=Connection

MsgBox "The Query:" & vbNewLine & vbNewLine & Source

For Col = 0 To Recordset.Fields.Count - 1
ws.Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

ws.Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
ws.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

    
TurnOn

End Sub

 

[Darren Smith]

Is this possible?

 

[RoryA]

Your question doesn't really make sense. You say you want to select two columns but return one? If you select two columns, you get two columns. I suspect all you really need to change is to replace the asterisk in your select statement with the field name that you want.

 

[Darren Smith]

Sorry should off said find both columns
My question is can you take detail from Access into a Combobox without coping to a spreadsheet first then on to a Combobox

 

[RoryA]

Yes you can. Once you have the recordset, just use GetRows to get its data into an array and assign that to the Column property of the combobox.

 

[Darren Smith]

Thank you for your help hopefully should be fine now

 

[Darren Smith]

Sorry not there yet I have tried to sort this but no go.
Trying to take some details of a filtered access sheet to an excel VBA Combobox.
This code says "Object doesn`t support this property or method"

Set rs = rs.Execute("SELECT ALL * FROM IDAndData; ")

Private Sub Gantry_Height_Width_DropButtonClick()
TurnOff

    Dim qry As String
    Dim i As Variant

   
    qry = "SELECT * FROM [IDAndData] " & _
        " WHERE [ModelType]='" & Model_Type.Text & "'"

   
    Dim rs As Object: Set rs = OpenConAndGetRS(qry)
    If Not (rs.BOF Or rs.EOF) Then
   
    With Me.Gantry_Height_Width
    .Clear
    Set rs = rs.Execute("SELECT ALL * FROM IDAndData; ")
    Do Until rs.Fields.EOF
    For Each i In rs.Fields
    Next i
    rs.MoveNext
    Loop
    End With

   End If
    rs.Close: Set rs = Nothing



TurnOn

End Sub

 

[RoryA]

Why have you changed all your working code into that (which makes no sense)? All you needed to do was amend the SQL string and assign the recordset data to the combo box.

 

[Darren Smith]

Sorry I`ve managed to sort this out now works.

 

[RoryA]

Perhaps you could post the final code for the benefit of others?