Execute my query in VBA Access while looping

Question

I am getting an error when I try to execute my query in my loop.
What I try to do is to loop through my recordset and return the value matching the ID. I have a table named Manufacturer and 2 columns named ID and Manuf.
I have commented out the 3 different execution methods, as I cannot get them to work probably.

Sub LoopExample()
On Error GoTo Error_Handler
Dim db              As DAO.Database
Dim rs              As DAO.Recordset
Dim iCount          As Integer
Dim strSQL          As String
Dim intManu         As Integer

strSQL = "SELECT * FROM Manufacturer"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL) 'open the recordset for use (table, Query, SQL Statement)

        Do While Not rs.EOF

            intManu = rs!ID
            strSQL = "SELECT Manuf FROM Manufacturer WHERE CustomerID='" & intManu & "'"

            'db.Execute (strSQL), dbFailOnError
            'DoCmd.OpenQuery strSQL, acViewNormal
            'CurrentDb.OpenRecordset (strSQL)
            Debug.Print strSQL

        rs.MoveNext
        Loop


rs.Close 'Close the recordset

Error_Handler_Exit:
On Error Resume Next
'Cleanup after ourselves
Set rs = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub

looks like you need to open a 2nd rst, the same as before strSQL = "SELECT * FROM Manufacturer" when you do this sql, do the same, for the 2nd sql, but call it rs2 or something — Nathan_Sav, Apr 28 '16 at 15:16
your first query just needs to be `strSQL = "SELECT ID FROM Manufacturer"` but then I don't know why you need the second query? — LiamH, Apr 28 '16 at 15:19
Not sure I understand @Nathan_Sav . I have tried to use ´Set rs2 = db.OpenRecordset(strSQL)` but it is not working. — Niclas, Apr 28 '16 at 15:23
@LiamH I am trying to use the 2nd query to loop through the Manuf-column and then get the Manufacturer name for all the records. — Niclas, Apr 28 '16 at 15:24
@AbdellahOUMGHAR The error message basically says that I cannot execute a SELECT query. — Niclas, Apr 28 '16 at 15:25
If you put this in your loop, does it show you what you want? -> `Debug.Print DLookup("Manuf", "Manufacturer", "CustomerID='" & intManu & "'")` — HansUp, Apr 28 '16 at 15:25
@HansUp if I try this, I get an error number 2471 - The expression you entered as a query parameter produced this error: 'CustomerID' — Niclas, Apr 28 '16 at 15:27
Does your `Manufacturer` table contain a field named `CustomerID`, spelled exactly that way? If so, is the datatype of that field text or numeric? — HansUp, Apr 28 '16 at 15:29
Wow @HansUp omg no it does not. It is just called ID. I mixed it up with another table! This helped, now I can use your debug.print to show what I want!! Thanks — Niclas, Apr 28 '16 at 15:31
But it seems like you should be able to get everything you need directly from the recordset: `Debug.Print rs!ID, rs!Manuf` In other words, I don't understand why you need to do anything extra to get the `Manuf` which matches the `ID` of the current recordset row. It should be right there in the recordset already. — HansUp, Apr 28 '16 at 15:40
@HansUp I am getting an error using the debug you just wrote. Honestly, I can see your point, but I cannot explain why it is not working. I am printing each value from "Manuf" to a text file, this is why I am looping through the recordset from 1 to X. — Niclas, Apr 29 '16 at 08:07
Since the recordset's source is `SELECT * FROM Manufacturer` and `Manufacturer` includes a field named `Manuf`, it makes no sense to me that Access complains *"Item not found"* about `rs!Manuf`. The only suggestion I can offer is to see whether [decompile](http://stackoverflow.com/questions/3266542/how-does-one-decompile-and-recompile-a-database-application) cures the problem. Good luck. — HansUp, Apr 29 '16 at 18:06

Execute my query in VBA Access while looping

0 Answers0