do.cmdRunSQL Question

[Paulsburbon]

Hello, I used this forum to help me create an access program that works great. I decided to change part of it and now it's doing something differently and I can't figure out how to go about fixing the new problem.

I had a button that took a group of fields (all numbers) in a table and provided a set discount. The problem with it was I had to manually type in the discount I wanted to give in visual basic. Once I did that I would run it and it would give a different percentage off each item on the form.

Here is the code I used:

Private Sub Toggle89_Click()
On Error GoTo Toggle89_Click_Err
    Dim vbrResult As VbMsgBoxResult
    Dim vbrResult1 As VbMsgBoxResult
    Dim RandomValue
    Dim Discount
    Dim strSQL As String
    Dim strSQL1 As String
    
    vbrResult = MsgBox("Run Auto Quote? This cannot be undone!", vbQuestion + vbYesNo, "Auto Quote")
    If vbrResult = vbYes Then
     Randomize
     RandomValue = Int((21 * Rnd) + 1)
        Select Case RandomValue
        Case 1: Discount = 0.1
        Case 2: Discount = 0.1025
        Case 3: Discount = 0.105
        Case 4: Discount = 0.1075
        Case 5: Discount = 0.11
        Case 6: Discount = 0.1125
        Case 7: Discount = 0.115
        Case 8: Discount = 0.1175
        Case 9: Discount = 0.12
        Case 10: Discount = 0.1225
        Case 11: Discount = 0.125
        Case 12: Discount = 0.1275
        Case 13: Discount = 0.13
        Case 14: Discount = 0.1325
        Case 15: Discount = 0.135
        Case 16: Discount = 0.1375
        Case 17: Discount = 0.14
        Case 18: Discount = 0.1425
        Case 19: Discount = 0.145
        Case 20: Discount = 0.1475
        Case 21: Discount = 0.15
        Case Else: Discount = 0
        End Select
        
    strSQL = "UPDATE [tblClaimLineItems] " & _
                "SET [curPrice] = ([curRetail] * (1-" & Discount & "))" & _
                "WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "' AND curPrice = 0;"
    DoCmd.RunSQL strSQL
    End If
    vbrResult1 = MsgBox("Would you like all the Replaced Item Text to be copied from the insured Description?", vbQuestion + vbYesNo, "Auto Quote")
    If vbrResult1 = vbYes Then
    strSQL1 = "UPDATE [tblClaimLineItems] " & _
                "SET [chrReplacedItemDescription] = [chrLostItemDescription]" & _
                "WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "' AND chrReplacedItemDescription Is Null;"
    DoCmd.RunSQL strSQL1
    End If
    Exit Sub
Toggle89_Click_Err:
    MsgBox "Error is " & Err.Description
    Exit Sub
End Sub

So I changed it to this :

Private Sub Toggle89_Click()
On Error GoTo Toggle89_Click_Err
    Dim SetpercentageResult As VbMsgBoxResult
    Dim RunAutoQuote As VbMsgBoxResult
    Dim RunCopyText As VbMsgBoxResult
    Dim Discount
    Dim strSQL As String
    Dim strSQL1 As String
    Dim MinMessage, MinTitle, MinDefault As String
    Dim MaxMessage, MaxTitle, MaxDefault As String
    Dim MinValue, MaxValue As Variant
    Dim MinShowBox, MaxShowBox As Boolean
    MinMessage = " Enter Minimum Value for AutoQuote "
    MinTitle = " Enter Minimum Value "
    MinDefault = "0"
    MinShowBox = True
    MaxMessage = " Enter Maximum Value for AutoQuote "
    MaxTitle = " Maximum Value "
    MaxDefault = "0"
    MaxShowBox = True
    
    SetpercentageResult = MsgBox("Set Percentage to use in Autoquote?", vbQuestion + vbYesNo, "Set Percentage")
        If SetpercentageResult = vbYes Then
            While MinShowBox = True
            MinValue = InputBox(MinMessage, MinTitle, MinDefault)
                If MinValue = "" Then
                MinShowBox = False
                Else
                    If IsNumeric(MinValue) = True Then
                    MinRandomPercent = MinValue
                    MsgBox "Minimum Value has been set"
                    MinShowBox = False
                    Else
                    MsgBox "Please enter numbers only"
                    End If
                End If
                MinRandomPercent = MinValue
            Wend
            While MaxShowBox = True
            MaxValue = InputBox(MaxMessage, MaxTitle, MaxDefault)
                If MaxValue = "" Then
                MaxShowBox = False
                Else
                    If IsNumeric(MaxValue) = True Then
                    MaxRandomPercent = MaxValue
                    MsgBox "Maximum Value has been set"
                    MaxShowBox = False
                    Else
                    MsgBox "Please enter numbers only"
                    End If
                End If
                MaxRandomPercent = MaxValue
            Wend
        End If
        
    RunAutoQuote = MsgBox("Run AutoQuote Between " & MinRandomPercent & " and " & MaxRandomPercent & "?", vbQuestion + vbYesNo, "AutoQuote")
        If RunAutoQuote = vbYes Then
        Randomize
        Discount = (Rnd() * (MaxValue - MinValue) + MinValue)
        MsgBox Discount
        strSQL = "UPDATE [tblClaimLineItems] " & _
        "SET [curPrice] = ([curRetail] * (1-" & Discount & "))" & _
        "WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "' AND curPrice = 0;"
        DoCmd.RunSQL strSQL
        End If
    RunCopyText = MsgBox("Would you like all the Replaced Item Text to be copied from the insured Description?", vbQuestion + vbYesNo, "Auto Quote")
        If RunCopyText = vbYes Then
        strSQL1 = "UPDATE [tblClaimLineItems] " & _
        "SET [chrReplacedItemDescription] = [chrLostItemDescription]" & _
        "WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "' AND chrReplacedItemDescription Is Null;"
        DoCmd.RunSQL strSQL1
        End If
        Exit Sub
Toggle89_Click_Err:
MsgBox "Error is " & Err.Description
Exit Sub
End Sub

I have this at the top of my code

Option Compare Database
Option Explicit

Dim MinRandomPercent
Dim MaxRandomPercent

Now it applies the same discount off to all the items instead of a different percent to each itme. Example. If I had two items each 100 the old way I could get 15 and 85 after running the code. the new way they would both be 85. I'm not sure what changed to make it that way? Any help?

Sorry for the long post and if I didn't type it right. It's been a while since I used this forum. Thanks again!

 

[pdx_man]

Just to restate what I believe I am reading:
If you had two records in tblClaimLineItems where the value of curRetail is 100, ran your first code, the curPrice would return 15 & 85 ... is this right?

Looking at the code, I do not see how this is possible, but some of my fine friends here may see something. Can you zip the db and post?