Access version syntax help, looping

[shubhamgandhi]

Hi I'm writing a sort of complex query in Access 2003 with vba.


I have only started learning Access a few days ago, but I have some experience in C++, Java, etc.
My goal is to cross-reference each SKU within each order to see the most common ones sold together.
This is a test code as I'm figuring out how the language is structured.
Here is my code:

Sub getSKUS()

    Dim db As Database
    Dim rs_data As Recordset
    Dim rs_sku As Recordset
    Dim rs_order As Recordset
    Dim fld As Field
    Dim strSQL As String
    
    'Open connection to current Access database
    Set db = CurrentDb()
    
    Set rs_data = db.OpenRecordset("ORDER_DATA")
    Set rs_sku = db.OpenRecordset("SKUS")
    Set rs_order = db.OpenRecordset("ORDERS")
    
    Dim curSKU As String
    curSKU = "161-0363"
    
    rs_sku.MoveFirst
    Do While Not rs_sku.EOF
        If rs_sku.Fields("SKUS_ORDERED").Value = curSKU Then
        MsgBox "SKU found."

    rs_sku.MoveNext
    Loop

End Sub

All the record_sets are tables containing order info in my database.
When I try to run it I get the error "Loop without Do".

Can you help me out?

[Bob Fitz]

Hi

You may have other errors but this one jumps out at me.
You have a If/Then without an End If. See added line in red.

 
If rs_sku.Fields("SKUS_ORDERED").Value = curSKU Then
        MsgBox "SKU found."
End If

[shubhamgandhi]

Oh I see! No errors now when I press run!
This is probably the wrong place to ask this but since I have you:
How do I actually test this out on my tables?

[Bob Fitz]

Hi

How do I actually test this out on my tables?

I'm sorry but I don't understand what you want to test?

[shubhamgandhi]

In my code I have:

MsgBox("SKU found")

This doesn't pop up when I click the run macro (the play buton) button in the toolbar. I want to test out this code so that the box comes up.
It should, assuming the code is correct.
Heres what I changed it to:

 
Function getSKUS()
    Dim db As Database
    Dim rs_data As Recordset
    Dim rs_sku As Recordset
    Dim rs_order As Recordset
    Dim fld As Field
    Dim strSQL As String
    
    'Open connection to current Access database
    Set db = CurrentDb()
    
    Set rs_data = db.OpenRecordset("ORDER_DATA")
    Set rs_sku = db.OpenRecordset("SKUS")
    Set rs_order = db.OpenRecordset("ORDERS")
    
    Dim curSKU As String
    curSKU = "161-0363"
    
    rs_sku.MoveFirst
    Do While Not rs_sku.EOF
        For Each fld In rs_sku.Fields
            If fld.Name = "SKU_ORDERED" Then
                If fld.Value = curSKU Then
                MsgBox ("SKU found")
                
                End If
            End If
        Next
    rs_sku.MoveNext
    Loop
End Function

[Bob Fitz]

Hi

Have you tried placing a breakpoint at the start of the code, run the code and step thru it one line at a time using F8, to see where it goes wrong.

[shubhamgandhi]

The code is now working fine without errors, but I want to actually call the funciton on my SKU table.
I'm trying to use a macro to call this module but I cant figure out how it works...

[Bob Fitz]

I'm sorry, but have to get to bed now (11pm local time) as I have to be up at 4.30am tomorrow. I will try to help you further when I'm available next, if nobody else offers you some help, but that won't be until the day after tomorrow.

[Bob Fitz]

Hi

Sorry I haven't been available sooner.

I haven't used macros for years (I prefer VBA code) but I will try to help if I can.

but I want to actually call the funciton on my SKU table

I'm sorry, but I don't understand what this means.
In Acces, code/macros are called/invoked when an "event" occurs, usually conected to a form or report.