select any number from a range

This function will give you non-repeating random numbers.

Usage:
x = getrand()

Function getrand()
Static lastnum As Long
Dim y As Long
Randomize
Do
y = Int(Rnd() * 1000) + 1
Loop Until y <> lastnum
lastnum = y
getrand = y

End Function

Pete

Basically, you wish to create a custom autonumber generator.  You need to
 
1) randomly generate a number between 1 and 1000
2) search the table of existing serial numbers for a match
    2a) if the serial number has been used, loop back to step 1
    2b) if the serial number has not been used, return the value

A check to verify that there are still possible entries would also be useful, to prevent infinite loops.

Public Function NewSerialNumber() as Long
  Const LNumber = 1
  Const UNumber  = 1000
  '<!-- Oh, look magic numbers! -->
  Dim dR as DAO.Recordset, dB as DAO.Database, It as Long
  Set dB = CurrentDb()
  Set dR = dB.OpenRecordset("Select SerialNumber from SerialNumbers;", dbOpensnapshot, dbReadOnly)
  If Not dR.EOF Then
    dR.MoveLast
    If dR.RecordCount > UNumber - LNumber Then
      dR.Close
      Set dR=Nothing
      dB.Close
      Set dB=Nothing
      Err.Raise 1, "NewSerialNumber", "All possible Serial Numbers have been used."
    End If
  End If
  Do
     It = Math.Round( Math.Rnd() * (UNumber - LNumber) +LNumber), 0)
     dR.FindFirst "SerialNumber=" & CStr(It)
  Loop Until dR.NoMatch
  dR.Close
  Set dR=Nothing
  dB.Close
  Set dB=Nothing
  NewSerialNumber = It
End Function

Hi rudwan

If your serial num is Type Number you could do something like this:

SELECT TOP 1 [SerialNum]+1 AS NextSerial FROM yourtable
WHERE ((([SerialNum]+1) Not In (SELECT SerialNum FROM yourtable) And ([SerialNum]+1)<1000))
ORDER BY [SerialNum]+1

Alan

You will need to mark the number once selected.
Here is how with DAO:

  Set rst = CurrentDb.OpenRecordset("Select Top 1 SerNo, Picked From tblYourTable Where Picked = False;")
  With rst
    If Not .EOF Then
      lngSerNo = !SerNo.Value
      .Edit
        !Picked = True
      .Update
    End If
    If lngSerNo = 0 Then
      ' Error. No numbers available.
    End If
  End With

/gustav

Comment from alanwarren:
    SELECT TOP 1 [SerialNum]+1 AS NextSerial FROM yourtable
    WHERE ((([SerialNum]+1) Not In (SELECT SerialNum FROM yourtable) And ([SerialNum]+1)<1000))
    ORDER BY [SerialNum]+1
----
This won't select a random number in the range, simply the next unused increment.  However, if randomness is not absolutely required, this is clearly a simpler method of generating a new number.
----
Comment from cactus_data:
    You will need to mark the number once selected.
----
This, of course, assumes that you have a pregenerated list of all possible serial numbers, rather than simply adding to the table when a new serial number is needed.  This works fine for a limited range (such as 1 to 1000), but if you increase the possible ranges of serial numbers, it may slow down query efficiency a little.

Hi GreymanMSC

slick bit of SQL hey?

Alan

Hmm ... these sweet notes on random and select solutions are nice, but read the original question again:

> i have a serial numbers between range of  1 to 1000
>
> i would like the VBA to select any number from this range
> once i pressed a button but without repeating it next time

Thus, somewhere the picked numbers must be recorded.

/gustav

hi
thanks for all who post , i tried to apply greyman code , but it is not accept it as DAO.recordset , it send error mesg for it as undefined type .
Peter code is ok as randomize , but it gives repeated number ,

If you have no DAO, here is how with ADO:

  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim strSQL As String

  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset

  strSQL = "Select Top 1 SerNo, Picked From tblYourTable Where Picked = False"
  rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
  With rst
    If Not .EOF Then
      lngSerNo = .Fields("SerNo").Value
        .Fields("Picked").Value = True
      .Update
    End If
    .Close
  End With
  If lngSerNo = 0 Then
    ' Error. No numbers available.
  End If

  Set rst = Nothing
  Set cnn = Nothing

/gustav

when comiling the code it gives at  
Set cnn = CurrentProject.Connection
 
invalid outside procedure

That means you have to put the code inside some SUB

Private Sub someSub()
' code
End Sub


ps... Did you try my sql ??

Alan

alan , ur sql lead to give me a serial number , while i need to select any number from the table , not as a serial number ,
my database has a form based on table  Views , each record of it has Identified like this
ViewID   Autonumber
ViewName  Text
ViewVoice Text ( ' location of sound file ' )
MyForm depends on View Table , has one bound command (ViewName) , and other 9 unbound commands , i want at current_event  the randomize code to select not in order any other 9 viewID , set its caption to its ViewNamw ,, when pressing voice button wil play sound file of the bound ViewName , and the kids has to select the correct Name from the 10 commands , ( one bound , and 9 unbound ) .
the other records like this also ,

Returning to your original question:

> i would like the VBA to select any number from this range
> once i pressed a button but without repeating it next time

For the event procedure for your button, do something like this:

Private Sub CommandButtonNumber_Click()

  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim strSQL As String

  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset

  strSQL = "Select Top 1 SerNo, Picked From tblYourTable Where Picked = False"
  rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
  With rst
    If Not .EOF Then
      lngSerNo = .Fields("SerNo").Value
        .Fields("Picked").Value = True
      .Update
    End If
    .Close
  End With
  If lngSerNo = 0 Then
    ' Error. No numbers available.
  Else
    MsgBox "Selected number:" & Str(lngSerNo), vbInformation + vbOkOnly, "Serial Number"
    ' Further code here to do something with the serial number.
  End If

  Set rst = Nothing
  Set cnn = Nothing

End Sub

As to your new question you would select 10 random records.
Watch the "Accepted Answer" here on how to do that:

  https://www.experts-exchange.com/questions/21111024/Random-Picks.html

You will, of course, have to adjust 100 to 10.

Open a recordset using this SQL and loop through it.
For each record, set the caption property of the view command buttons as desired and set the Tag property to a string which identify what to play. Then, when clicking the button, read the Tag property and run code to play the assigned track.

/gustav

catues , it is ok , ur code is ok , i made a query it run well
but when i apply it to the form as this :
Private Sub Form_Current()
Dim Fa As Recordset
Dim Ma As String
Ma = "SELECT TOP 9 * FROM Views where [viewid]<> viewid ORDER BY RandomNumber([ViewID]);"

Set Fa = CurrentDb.OpenRecordset(Ma)
Fa.AddNew
Fa![ViewID] = ViewID
Fa![ViewName] = ViewName
Fa.Update
Fa.MoveFirst
Do Until Fa.EOF
i = i + 1
Me.Comm& i.Caption = Fa![ViewName]
Fa.MoveLast
Loop
Fa.Close
End Sub
 it gives error that type mismach at set statement

sorry , adjust  
fa.movelast
loop
to be
fa.movenext
loop

it  refuse to acceot the :
Set Fa = CurrentDb.OpenRecordset(Ma)
as type mismach

note , me.comm & i    means command1,command2,......etc

Sounds like you don't have DAO referenced (menu: Tools, References: Microsoft DAO 3.x).
If not, either add this or adjust the code to use ADO (see previous postings).

Also, you can't do this:

  Me.Comm& i.Caption = Fa![ViewName]

Change it to:

  Me("Comm" & i & "").Caption = Fa![ViewName]

/gustav

  it  refuse to acceot the :
   Set Fa = CurrentDb.OpenRecordset(Ma)
   as type mismach
---
You have Fa declared as a Recordset.  This is a class used in both ADO and DAO.  I find it's best to always use explicit scope when declaring objects common to such libraries, even when you've only referenced one.  If you, or another designer, later adds a second reference, explicit scoping will avoid conflicts.

   Dim Fa As DAO.Recordset.

yes cactus , i added DAO to the references and it become accepted
now still only one thing , how to enforce sql to select the current record
id within the query ,?
note : the current id will be the correct answer to be clicked , so i dislike it to take a fixed command inside the form , it must be at every record has different command location

I'm not quite sure I can follow you, but if you mean how to store the IDs for the buttons, one - simple - method is to store it in the Tag property of the buttons:

  Me("Comm" & i & "").Tag = CStr(Fa![ViewID])

Then, later, read the value when you need it:

  lngID = Val(Me("Comm" & i & "").Tag)

If this is not what you have in mind, please elaborate.

/gustav

Hi cactus
Suppose ViewTable has 100 records , and MyForm based on ViewTable records , on_current of form , I have Me![ID]  , in addition to 10 commands , ur code will return 10 randomizes ID , where sated to the 10 commands. caption .
Now , suppose Me![ID]=6  , and the code run and returned the following 10 randomized ID :
10,15,8,19,33,98,77,55,20,4
SO , where is # 6 which must be reflected as one of the 10 commands captions ? in fact it is one which user must click it as the correct answer .
We need the code to be enforced to select # 6 as one record of the recordset .
Note: if u ask me why not adjust the code to select 9 values instead of 10  and set the first 9 caption to it , then let the 10th  command.caption to take Me![ID].Value ?
I will replay : it is not possible because :
1- may be the 9 selected randomize are : 10,15,8,19,33,98,77,55,6
 so , 6 will be dublicated .
2-the aim of the database is to make the correct answer take any place of the commands , not certain one , if we give it always command # 10 for example , that means the user notice that and will directly go to the 10th command and click it .

where r u cactus ?