Access Qry as a Function

[Darren Smith]

This code I need to extract the access to access database code into a function please can someone help.

Sub Outline_Marker_Lights(sOutline_Marker_Lights 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 [Lights] " & _
    " WHERE [LightType]='" & sOutline_Marker_Lights & "'" & _
    " 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, 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
            iRow = iRow + 1
            rs.MoveNext
        Loop
    End If
    rs.Close: Set rs = Nothing
End With

End Sub

 

[Darren Smith]

That`s perfect thanks for your help.