Select 2 Columns in Access form Excel VBA

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
616
Office Version
  1. 2019
Platform
  1. Windows
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

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


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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 23, 2020
Messages
616
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Nov 23, 2020
Messages
616
Office Version
  1. 2019
Platform
  1. Windows
Thank you for your help hopefully should be fine now
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
616
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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"
VBA Code:
Set rs = rs.Execute("SELECT ALL * FROM IDAndData; ")

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 23, 2020
Messages
616
Office Version
  1. 2019
Platform
  1. Windows
Sorry I`ve managed to sort this out now works.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Perhaps you could post the final code for the benefit of others?
 

Forum statistics

Threads
1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top