Quesiton about Random Numbers

[Paulsburbon]

Hello all, I have a quesiton about using random numbers. I have a table for items in a database each with a price. I need to go thru and discount the price 10% to 15% randomly on each and put the new amount into a different field. I havn't the slightest idea how to do this. The closest I've gotten to doing this is:

Private Sub txtPrice_DblClick(Cancel As Integer)
If Me!Combo83 = 57 Then
txtPrice = ([curRetail] - ([curRetail] * 0.15))
Else
txtPrice = ([curRetail] - ([curRetail] * 0.12))
End If
If Me!Combo83 = 45 Then
txtPrice = ([curRetail] - ([curRetail] * 0.1))
Else
txtPrice = ([curRetail] - ([curRetail] * 0.12))
End If
End Sub

This only works on one record at a time. I would like something that worked on a set of records all at once. Thank all of you for your time.

 

[modest]

Do you want to be able to choose the discount? Or do you just want a random discount to be given?

 

[tkpstock]

I'm not sure what you're trying to randomize from your example.

If you want to randomly discount either 15 or 12 percent, then you could do something like:

txtBox = ([CurrRetail] - ([CurrRetail] * (IIF(rnd<0.5,0.15,0.12))))

The Rnd function returns a number randomly between 0 and 1. You can use the Randomize function to reset the "seed" number at the beginning (the seed number determines the initial calculations of the first random number. With the same seed number you will end up with the same list of random numbers, thus making them not random!)

HTH

 

[modest]

Dim RandomValue
Dim Discount

Randomize                           ' Initialize random-number generator
RandomValue = Int((5 * Rnd) + 1)    ' Generate random value between 1 and 5

Select Case RandomValue
    Case 1: Discount = .1
    Case 2: Discount = .12
    Case 3: Discount = .13
    Case 4: Discount = .14
    Case 5: Discount = .15
    Case Else: Discount = 0
End Select

strSQL = "UPDATE [Table Name] SET [DiscountFieldName] = [PriceField] * (1-" & Discount & ")"

Docmd.RunSQL strSQL

 

[Paulsburbon]

Whoa.. You guys are fast. I'll try that out too and see how it goes. You guys are really something. I feel kinda bad asking all these questions without really having anything to give back.

 

[tkpstock]

Or, if you want to create a random discount between .10 and .15 (inclusive):

discount = CInt((rnd *5) + 10) / 100

 

[modest]

Agreed!

Paul, my demonstration was if you wanted to specifically point out what random number was what discount. Like if you didn't want to give a 13% discount, then do 4 * Rnd and make
Case 3: Discount = .14
Case 4: Discount = .15
and get rid of Case5


Notice my case does not have an 11% discount

 

[Paulsburbon]

Ok, I got it to work. Except it will only run correctly if I click on an item and then if I click on a different item it will give it the same discount. I was hoping to click on a button on the form and it would go thru all the items on the form and give it a random discount. Here is the code I'm using:

Private Sub Toggle89_Click()
On Error GoTo Toggle89_Click_Err
Dim intAnswer As Integer
Dim RandomValue
Dim Discount
Dim strSQL As String
intAnswer = MsgBox("Run Auto Quote? This cannot be undone!", vbQuestion + vbYesNo, "Auto Quote")
If intAnswer = vbYes Then
Randomize ' Initialize random-number generator
RandomValue = Int((5 * Rnd) + 1) ' Generate random value between 1 and 5
Select Case RandomValue
Case 1: Discount = 0.1
Case 2: Discount = 0.12
Case 3: Discount = 0.13
Case 4: Discount = 0.14
Case 5: Discount = 0.15
Case Else: Discount = 0
End Select
strSQL = "UPDATE [tblClaimLineItems] SET [curPrice] = [curRetail] * (1-" & Discount & ")"
DoCmd.RunSQL strSQL
End If
Exit Sub
Toggle89_Click_Err:
MsgBox "Error is " & Err.Description
Exit Sub
End Sub

 

[modest]

First thing I suggest, use a more descriptive variable:
Dim intAnswer As Integer to:
Dim vbrResult As vbMsgBoxResult



Second thing I suggest, use 6 variables or do the way that Tom suggested:
RandomValue = Int((5 * Rnd) + 1) ' Generate random value between 1 and 5 to:
RandomValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6 (and add a 6th case for Discount = .11)



Third thing: The whole table is going to be updated with the same discount unless you choose a specific record. This can be done by using the WHERE clause in the SQL statement. For Example:
strSQL = "UPDATE [tblClaimLineItems] SET [curPrice] = [curRetail] * (1-" & Discount & ") WHERE [field in your table]='" & Me.[Something On Your Form] & "'"

 

[Paulsburbon]

Modest thank for your reply. Here is what I ended up with.

Private Sub Toggle89_Click()
On Error GoTo Toggle89_Click_Err
Dim vbrResult As VbMsgBoxResult
Dim RandomValue
Dim Discount
Dim strSQL As String
vbrResult = MsgBox("Run Auto Quote? This cannot be undone!", vbQuestion + vbYesNo, "Auto Quote")
If vbrResult = vbYes Then
Randomize ' Initialize random-number generator
RandomValue = Int((21 * Rnd) + 1) ' Generate random value between 1 and 5
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 [lngClaimNumber] = '" & _
Me!TXTClaimNumber & "'"
DoCmd.RunSQL strSQL
End If
Exit Sub
Toggle89_Click_Err:
MsgBox "Error is " & Err.Description
Exit Sub
End Sub

I added a bit more. Thank you so much for your help. Does this look better?

 

[Paulsburbon]

Well, It's giving me an "There is an invalid use of the . (dot) or ! operator or invalid parentheses" I changed it to me.TXTclaimnumber and get the same error

 

[Paulsburbon]

Ok this is weird. Out of no where it works! Modest, Thank you so much for your help. I'm just barely getting all this VB code but it is getting easier for me. I'm sureI will have more questions about doing other things but for now this works perfect.

 

[Paulsburbon]

Shoot.. I hate to be a pain in the butt... It is giving the same discount to all the items... What's the best way to give a different discount to every item on the list? I'm sorry if I'm being too much trouble.

 

[Paulsburbon]

Ok I got kinda an idea how to go thru this. I'm sure I'm going to get many things wrong.

I would first ;

Dim rs As ADODB.Recordset,

Then open the record set I want to change the amount to with;

rs.Open "Table Name" Other options

( I have no idea how to filter with only certain records though)

Then After I have the recordset I would go thru each one running the above discount Select case part of the code and then moving to the next.

Then when it gets to the last record. To stop. I have a bunch of research to do so I'll post what I come up with. Am I on the right track?

 

[tkpstock]

I use DAO - it's easier.

After setting a reference to DAO:

Dim rst as DAO.Recordset
Dim sql as String

sql = "SELECT * FROM TableName WHERE Field = 'Value'"

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
If rst.RecordCount = 0 Then
    Msgbox "No records found!"
    Exit Sub
End if
rst.MoveFirst
While Not rst.EOF
rst.Edit
rst!DiscountPrice = rst!DiscountPrice - discount
rst.Update
rst.MoveNext
wend

HTH