Access data to excel combo box

[Darren Smith]

This code I have written in Access database I need to filter this table with a Combo box then add the result data to another Comb box in VBA excel
How is this possible?

Sub ExportData()

On Error GoTo ErrorHandler

Dim dbTable As String

Dim xlWorksheetPath As String

xlWorksheetPath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker"

xlWorksheetPath = xlWorksheetPath & "Automated Cardworker.xlsm"

dbTable = "GantryHeight&Width"

DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True

ErrorHandlerExit:

Exit Sub

ErrorHandler:

MsgBox "No Data Found”"

Resume ErrorHandlerExit

End Sub

 

[Darren Smith]

The other way I tried was this but for some reason, it can`t seem to open access correctly

Private Function OpenConAndGetRS(qry As String) As Object
    Dim strcon As String
    Dim conn As Object, rs As Object
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    rs.CursorLocation = adUseClient
    strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Access Files\DrNo Data Base.accdb;"
    conn.Open strcon
    rs.Open qry, conn, 3, 3
    Set OpenConAndGetRS = rs
End Function

Private Sub Gantry_Height_DropButtonClick()

    Dim qry As String

   qry = "SELECT * FROM [GantryHeight&Width] " & _
    " WHERE [Vehicle]='" & Model_Type.Text & "'"
    Dim rs As Object: Set rs = OpenConAndGetRS(qry)
    If Not (rs.BOF Or rs.EOF) Then
    Do While Not rs.EOF
  
    Me.Gantry_Height.Column = rs.Fields("Height&Width").Value
    rs.MoveNext
    Loop
  
   rs.Close: Set rs = Nothing
    End If
  
End Sub

 

[RoryA]

Duplicate thread, so closed.