I am creating a query for Excel to Access Database

[Darren Smith]

I`ve created a VBA query to not find various data see below. How could I make this work?
At the moment the Qry only shows red?

Private Sub Fill_DrNos()

TurnOff

If bReset = True Then Exit Sub


        Dim qry As String
        Dim i As Variant
        Dim prevPos As Long
        
        qry = "SELECT DISTINCT JobCardMaster.F2 FROM JobCardMaster" & _
        " WHERE (JobCardMaster.F2 Is Not Null) " & _
         And JobCardMaster.F2 Not Like "Drawing  No." & _
         And JobCardMaster.F2 Not Like "Drawing No." & "'"

    
        Application.EnableEvents = False
        Dim rs As Object: Set rs = OpenConAndGetRS(qry)
        If Not (rs.BOF Or rs.EOF) Then
            With Me.Print_DrNos
            prevPos = .ListIndex
                .Clear
                Do Until rs.EOF
                    .AddItem (rs.Fields("F2").Value)
                    rs.MoveNext
                Loop
                .ListIndex = prevPos
            End With
       End If
        rs.Close: Set rs = Nothing
 TurnOn
    
End Sub

 

[Darren Smith]

I had another go see below still no luck. Any chance of some help, please
Trying to add items to Combo box from Access database.

Sub getDrNos_From_Access()

Dim DBFullName As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmb_DrNo As MSForms.ComboBox

Set cmb_DrNo = Body_And_Vehicle_Type_Form.Print_DrNos

DBFullName = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Access Files\DrNo Data Base.accdb"

With cnn

.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFullName

With rst

.Open "SELECT * FROM" & DrNos & _
"WHERE[DrawingNos]= '" & cmb_DrNo.Value

End With
End With

Set rst = Nothing
cnn.Close
Set cnn = Nothing



End Sub

 

[Joe4]

I do not about the validity of your code, but I do see a few issues in buiding your SQL string. You seem to be missing some spaces and your last text qualifier.
What I recommend doing is storing the SQL string in a string variable, so you can use a message box to see what you are building, and making sure it is valid (you can try copying and pasting the results of the message box into an Access query (via SQL view), and try running it to make sure you have built a valid query.

So the changes I would make would look something like this:

Sub getDrNos_From_Access()

Dim DBFullName As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmb_DrNo As MSForms.ComboBox
Dim strSQL as String

Set cmb_DrNo = Body_And_Vehicle_Type_Form.Print_DrNos

DBFullName = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Access Files\DrNo Data Base.accdb"

With cnn
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFullName

    With rst
        strSQL = "SELECT * FROM " & DrNos & " WHERE [DrawingNos]= '" & cmb_DrNo.Value & "'"
        MsgBox strSQL  'temporary message box to confirm SQL string
        .Open strSQL
    End With

End With

Set rst = Nothing
cnn.Close
Set cnn = Nothing

End Sub

 

[Darren Smith]

Msgbox Says SELECT*FROM DRNos WHERE [DrawingNos]=' Print DrNos' This should return a set of Numbers. The Qry is not right don`t quite understand it should fill from the access database rather than the other way round??

 

[RoryA]

Clearly the value of cmb_DrNo is not a set of numbers. Where should this list of numbers come from?

 

[Darren Smith]

From the access database Table DrawingNos

 

[RoryA]

No, I mean where on your form? You are using it as criteria to determine what to extract from a table called DRNos, so where are you supplying the numbers to use as criteria?

 

[Darren Smith]

They are from an excel column that goes to the Access database then should come back to the excel VBA combo box on the form.
On the form, there is a button called Activate_DrNos which should fill the numbers to the combo box.

Private Sub Activate_DrNos_Click()

    With Me.Print_DrNos
    Me.Print_DrNos.Clear
   Call getDrNos_From_Access
   End With
   
End Sub

 

[RoryA]

That's nothing like what your code is doing. Your code is querying a database based on a set of criteria, but you still haven't explained where the criteria come from. Currently your code tries to retrieve records from the database where the 'DrawingNos' field in the table matches the value in your cmb_DrNo control. It appears that that control contains the text " Print DrNos", which presumably is not correct.

 

[Darren Smith]

Please help me to get the code working the code is trying to match the value in the combo box this is wrong should just collect the values in the access table.
The Criteria are just to take the Numbers from the Database.
The database is being fed Numbers from a live connector from Excel to the Access database