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]

Here it is...

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

 

[RoryA]

I thought you were trying to populate a combo box?

 

[Darren Smith]

Sorry the right code is below

MT

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

 

[RoryA]

You can replace this:

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

with this:

.Column = rs.getrows