Random Number Trouble

[alastair69]

I have the following code:
First any refinements would be greatfully received.

This generates a random list of clients

Function RandomNumber(FeedNum As Long)

Randomize
RandomNumber = Int((FeedNum + 1000) * Rnd + 1)

End Function

Sub forms are running though query based on above RandomNumber([clientID]), top value is set to 50. Runs fine but, when i use a text box to hold the number of random records to show it keeps erroring the following message "Compile Error: ByRef argument type mismatch".

Can anybody help me please

Thanks

Alastair

 

[sonny]

did you declare the control as long

 

[alastair69]

on the form i used the following command

Private Sub NumberRecords_AfterUpdate()
strNum = Me.NumberRecords
Call RandomNumber(strNum)

End Sub

if that helps

Alastair

 

[RayH]

You don't CALL functions.
var=myfunction(x)

 

[weveritt]

Under VBA, variables are passed by reference unless you specify otherwise. So Function RandomNumber(FeedNum As Long) is equivalent to Function RandomNumber(ByRef FeedNum As Long). Look in the manuals for the difference between ByRef & ByVal

When YOU pass in strNum, it has not been declared, so a new variable of type VARIANT is created. This is not a LONG, so the call fails

To make your code work, change the call to RandomNumber(clng(strNum))

BTW, CALL is obsolete..

However, I recommend using Option Explicit. This forces you to declare all variables:

Option Explicit
...
Private Sub NumberRecords_AfterUpdate()
Dim iRecords as Long
Dim iResult as Long

iRecords = Me.NumberRecords
iResult = RandomNumber(iRecords)

End Sub

Function RandomNumber(FeedNum As Long) As Long

Randomize
RandomNumber = Int((FeedNum + 1000) * Rnd + 1)

End Function

Hope this helps

Will