OpenRecordset with Multiple Tables

[DavidDCA]

Hello all. I am getting an "Item Not Found in this Collection" error with the following code:

    s = "SELECT Customers.*, SalesManagers.*, Children.*" & _
        "FROM (Districts INNER JOIN (Routes INNER JOIN (Children INNER JOIN Customers ON Children.ChildID = Customers.ChildID) ON (Routes.RouteID = Customers.RouteID) AND (Routes.RouteID = Customers.RouteID)) ON Districts.DistrictID = Routes.DistrictID) INNER JOIN SalesManagers ON Districts.SalesManagerID = SalesManagers.SalesManagerID;"
    
    Set r = CurrentDb().OpenRecordset(s, dbOpenDynaset, dbInconsistent)
    
    c = 0

    Do Until r.EOF
        With r
            .Edit
            
            'Calculate each Customer result
            !ResultRetailWeek1 = !AvgRetailWeek1 * !Children!RetailWeek1
            ' * !SalesManagers!RetailWeek1) + Customers!RetailWeek1
                      
            'Save and move to the next record.
            .Update
            .MoveNext
            c = c + 1
        End With
    Loop

    r.Close

I know the SELECT statement works, as I've tried it in a query.

My suspicion is that I am not correctly using the syntax to refererence the fields in the other tables, in this line right here:

            !ResultRetailWeek1 = !AvgRetailWeek1 * !Children!RetailWeek1

So, how does one call a field in a SELECT statement when there are two fields of the same name? Basically I'm looking for something like Table1.Field1

Any help? Thanks!

 

[pdx_man]

You already have all of the fields from Children in your recordset, r.

SELECT Customers.*, SalesManagers.*, Children.* ...

You should be able to just use:
!ResultRetailWeek1 = !AvgRetailWeek1 * !RetailWeek1

Do you have more than one instance of the field RetailWeek1 across your referenced tables?

 

[DavidDCA]

You already have all of the fields from Children in your recordset, r.

SELECT Customers.*, SalesManagers.*, Children.* ...

You should be able to just use:
!ResultRetailWeek1 = !AvgRetailWeek1 * !RetailWeek1

Do you have more than one instance of the field RetailWeek1 across your referenced tables?

Yes, that's where the problem lies. My tables (Customers, SalesManagers, Children) all have fields called RetailWeek1 (2,3,4,etc). So, saying:

!ResultRetailWeek1 = !AvgRetailWeek1 * !RetailWeek1

Doesn't work because RetailWeek1 exists in three tables. So, how can I reference exactly which RetailWeek1 I want, since I'm going to want all three in this equation I'm building.

I imagine it is something like this:

!ResultRetailWeek1 = !AvgRetailWeek1 * !Children.RetailWeek1

But it doesn't like that syntax.

Thanks!

 

[pdx_man]

You will have to iterate each field and alias the fields that are the same.

Cumbersome, but the cost of having the same field names in different tables.

SELECT Customers.Field1, Customers.Field2, Customers.RetailWeek1 AS Cust_RetWk1, SalesManagers.Field1, SalesManagers.Field2, SalesManagers.RetailWeek1 AS Sales_RetWk1, Children.Field1, Children.Field2, Children.RetailWeek1 AS Chld_RetWk1

Then reference the alias in your VBA.

 

[DavidDCA]

I was messing around with different ways of doing the syntax and I found one that works!

![Tablename.Fieldname]

Made my life a lot easier.

Thanks for your help, though.