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
 

Darren Smith

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

VBA Code:
Sub Toolpod_Hasp_Staple(sToolpod_Hasp_Staple As String)

Dim ws      As Worksheet
Dim Addme       As Range
Dim iRow        As Long
Dim qry         As String

Set ws = ThisWorkbook.Sheets("Job Card Master")

With ws
    iRow = Selection.Row
    Set Addme = ws.Range("A" & iRow)
    qry = "SELECT * FROM [ToolpodType] " & _
    " WHERE [ToolpodType]='" & sToolpod_Hasp_Staple & "'" & _
    " ORDER BY [ID] ASC"
    iRow = iRow
    Dim rs As Object: Set rs = OpenConAndGetRS(qry)
    If Not (rs.BOF Or rs.EOF) Then
        Do While Not rs.EOF
            .Cells(iRow, 1) = rs.Fields("ItemNo").Value
            .Cells(iRow, 2) = rs.Fields("DrawingNo").Value
            .Cells(iRow, 3) = rs.Fields("Description").Value
            .Cells(iRow, 4) = rs.Fields("TGSPartNo").Value
            .Cells(iRow, 5) = rs.Fields("Material/Part").Value
            .Cells(iRow, 7) = rs.Fields("Size").Value
            .Cells(iRow, 8) = rs.Fields("Qty").Value
            .Cells(iRow, 11) = rs.Fields("AllocHours").Value
            .Cells(iRow, 13) = rs.Fields("Order").Value
            .Cells(iRow, 14) = rs.Fields("Supplier").Value
            iRow = iRow + 1
            rs.MoveNext
        Loop
    End If
    rs.Close: Set rs = Nothing
End With

End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
I thought you were trying to populate a combo box?
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
616
Office Version
  1. 2019
Platform
  1. Windows
Sorry the right code is below

MT

VBA Code:
Private Sub Gantry_Height_Width_DropButtonClick()

TurnOff
   
        Dim qry As String
        Dim i As Variant
        Dim prevPos As Long
       
       
 
             
        qry = "SELECT DISTINCT IDAndData.HeightWidthGantry FROM IDAndData" & _
              "  WHERE (IDAndData.HeightWidthGantry Is Not Null) " & _
              "  AND IDAndData.ModelType='" & Model_Type.Text & "'" & _
              "  AND IDAndData.ModelType='" & Model_Type.Text & "'"
   
        Application.EnableEvents = False
        Dim rs As Object: Set rs = OpenConAndGetRS(qry)
        If Not (rs.BOF Or rs.EOF) Then
            With Me.Gantry_Height_Width
            prevPos = .ListIndex
                .Clear
                Do Until rs.EOF
                    .AddItem (rs.Fields("HeightWidthGantry").Value)
                    rs.MoveNext
                Loop
                .ListIndex = prevPos
            End With
       End If
        rs.Close: Set rs = Nothing
        Application.EnableEvents = True
   
    TurnOn

End Sub
 
Solution

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
You can replace this:

Code:
.Clear
                Do Until rs.EOF
                    .AddItem (rs.Fields("HeightWidthGantry").Value)
                    rs.MoveNext
                Loop

with this:

Code:
.Column = rs.getrows
 

Forum statistics

Threads
1,147,518
Messages
5,741,640
Members
423,675
Latest member
Dea21

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