Very basic "Update table via VBA" query

[Widge]

I can't believe it but I don't have a clue how to update a table using VBA!

What I'm trying to do is:

1) Do a search on the last 3 periods turnover for one client in particular and check to see if they are all over 60k
2) Go to a table which states rebate rates for each client and change that one clients rate based on whether they are over or under 60k for the 3 months

I'm assuming for part 1, I'd have to set up a 3 record array and have a SQLquery feed the results of a check for the last 3 months turnover into it, and then check each part of the array for the 60k mark.
Then part 2 I would have to update the relevant field. Although I'm not entirely sure how to go and pinpoint that record and update it.

Point out any flaws with my part 1 assumption. Thats how I believe I'd do it.

[Rabbit]

I don't know what "last 3 periods turnover" means. Can you provide some pseudo data?

[Widge]

Sorry. Entries like


Date field Amount Field
2007-05 £40000
2007-06 £45000
2007-07 £55000
2007-08 £60000

[Widge]

I'm having a go at writing the bit where I find the last 3 periods turnover, am I doing this in a totally long winded way??

Sub TweakRebates()

    Set cnn = CurrentProject.Connection
    Set Tweak = New ADODB.Recordset
    Dim Rec As Record
    Set Rec = New Record
    
frcSQL = "SELECT (Sum([Rebates_CBS Transaction Data].[Base Amount])*-1) AS [SumOfBase Amount]" _
        & " from [Rebates_CBS Transaction Data] GROUP BY [Rebates_CBS Transaction Data].[Base Amount], [Rebates_CBS Transaction Data].Name, [Rebates_CBS Transaction Data].[Rebate Date]" _
        & " HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])='" & Period & "' Or " _
        & " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period1 & "' Or" _
        & " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period2 & "')" _
        & " AND (([Rebates_CBS Transaction Data].Name)='Frc Furniture Resource Centre'))" _

    With Tweak
        .Open frcSQL, cnn, adOpenForwardOnly, adLockReadOnly
    End With

    a = 1
    
    Set Tweak = Rec.GetChildren
    
    
    While Not Tweak.EOF
        Set MonthCheck(a) = Tweak(a)
        Debug.Print MonthCheck(2)
        Tweak.MoveNext
        a = a + 1
    Wend
    

Debug.Print frcSQL

End Sub

What I'm doing is running a query and populating a recordset with the 3 periods turnover in question. I can't figure out how to reference them.

What I was going to do was load them into an array and check to see if they were all over 60k.

Now is this totally unnecessary? Because what I want is quite simple, but the process of doing it is getting increasingly more and more difficult. You should be able to see from the above that I'm not entirely used to using Recordsets and generally bumble about using F1 help trying different techniques.

[Widge]

Right, I've figured out the above now:

Sub TweakRebates()

    Set cnn = CurrentProject.Connection
    Set Tweak = New ADODB.Recordset
    
frcSQL = "SELECT (Sum([Rebates_CBS Transaction Data].[Base Amount])*-1) AS [SumOfBase Amount]" _
        & " from [Rebates_CBS Transaction Data] GROUP BY [Rebates_CBS Transaction Data].Name, [Rebates_CBS Transaction Data].[Rebate Date]" _
        & " HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])='" & Period & "' Or " _
        & " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period1 & "' Or" _
        & " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period2 & "')" _
        & " AND (([Rebates_CBS Transaction Data].Name)='Frc Furniture Resource Centre'))"
'    Debug.Print frcSQL
    With Tweak
        .Open frcSQL, cnn, adOpenForwardOnly, adLockReadOnly
    End With

    a = 1

    While Not Tweak.EOF
        Set MonthCheck(a) = Tweak![SumOfBase Amount]
        Debug.Print MonthCheck(a)
        Tweak.MoveNext
        a = a + 1
    Wend
    
End Sub

This will help me identify if the periods all go over the £60k turnover amount.

Now I have to figure out how to get into the table of rebate rates, go to the relevant suppliers record and amend the rebate rate.

I would assume I could use a find record to get to the relevant place BUT how to change the correct field?

Would an update SQL query work in this case?

[Widge]

And finally, my solution which works!

Sub FRCTweakRebates()

' the below skips all of this stuff if its not necessary

    If (Year(Period) <= 2007 And Month(Period) < 8) And (Year(Period) = 2007 And Month(Period) > 4) Then
        RebateToSet = 1
        GoTo SkipTo
    ElseIf (Year(Period) <= 2007 And Month(Period) <= 4) Then
        RebateToSet = 3
        GoTo SkipTo
    End If

'sets up recordset to hold the last 3 months from period specified turnover in drop down box

    Set cnn = CurrentProject.Connection
    Set Tweak = New ADODB.Recordset
    
frcSQL = "SELECT (Sum([Rebates_CBS Transaction Data].[Base Amount])*-1) AS [SumOfBase Amount]" _
        & " from [Rebates_CBS Transaction Data] GROUP BY [Rebates_CBS Transaction Data].Name, [Rebates_CBS Transaction Data].[Rebate Date]" _
        & " HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])='" & Period & "' Or " _
        & " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period1 & "' Or" _
        & " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period2 & "')" _
        & " AND (([Rebates_CBS Transaction Data].Name)='Frc Furniture Resource Centre'))"
'    Debug.Print frcSQL
    With Tweak
        .Open frcSQL, cnn, adOpenForwardOnly, adLockReadOnly
    End With

    a = 1

' writes the turnover to an array

    While Not Tweak.EOF
        MonthCheck(a) = Tweak![SumOfBase Amount]
        Debug.Print MonthCheck(a)
        Tweak.MoveNext
        a = a + 1
    Wend
    
    RebateToSet = 0
    
' test to see if they pass the 60k mark

    FlagTest = MonthCheck(1) > 60000 And MonthCheck(2) > 60000 And MonthCheck(3) > 60000

' sets rebate based on test

    If FlagTest = True Then
        RebateToSet = 1
    ElseIf FlagTest = False Then
        RebateToSet = 2
    End If
    
Tweak.Close
cnn.Close
Set cnn = Nothing
Set Tweak = Nothing
    
SkipTo:
    
'automatically updates the FRC PFH Rebate amount accordingly based on date and/or 60k check
    
    Set cnn2 = CurrentProject.Connection
    Set RebTab = New ADODB.Recordset
    
    RebTabSQL = "SELECT Rebates_Supplier.PfHRebateAmt from Rebates_Supplier WHERE " _
        & "(((Rebates_Supplier.Name) = 'Frc Furniture Resource Centre'))"
    
    RebTab.Open RebTabSQL, cnn2, adOpenKeyset, adLockOptimistic, adCmdText
    
    RebTab!PfHRebateAmt = RebateToSet
    RebTab.Update

cnn2.Close
Set cnn2 = Nothing



End Sub

Let me know if I've really beat around the bush on this.

[Rabbit]

I suspect you can do what you want using subqueries but I have trouble following what it is that you want to do.

I only know of two fields and what they represent in your table. You've given me [Rebate Date] and [Amount] as your relevant fields. But you mention a bunch of other fields and I don't know what they represent or how they relate to those two fields.