assign a range of numbers in access 2003

Hi. wonder if possible.  Have a db.  have a form with a subform (datasheet view of a query).  This subform may contain 10 items or it may contain more ie up to 100.  The user needs to enter a range of numbers in a textbox ie 1-100 and have the numbers be assigned/distributed across the 100 records in the subform. Doesn't matter what order they land in as long as each only get one number and none have the same.  is this possible?  
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
Here is some code that will assign comma-separated numbers from a text box to records in a subform (I used a table; modify as needed for your database):
Private Sub cmdAssignNumbers_Click()
'Created by Helen Feddema 31-Dec-2009
'Last modified by Helen Feddema 31-Dec-2009

On Error GoTo ErrorHandler

   Dim strNumbersToAssign As String
   Dim strNumbers() As String
   Dim intUBound As Integer
   Dim i As Integer
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   strNumbersToAssign = Nz(Me![txtNumbersToAssign])
   If strNumbersToAssign = "" Then
      GoTo ErrorHandlerExit
   End If
   'Set up array of numbers to assign
   strNumbers = Split(strNumbersToAssign, ",", -1, vbTextCompare)
   intUBound = UBound(strNumbers)
   'Clear old numbers
   strSQL = "UPDATE tblAssignedNumbers SET AssignedNumber = Null;"
   DoCmd.RunSQL strSQL
   'Assign numbers to subform records
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblAssignedNumbers")
   For i = 0 To intUBound
      If Not rst.EOF Then
         rst![AssignedNumber] = strNumbers(i)
         GoTo Finish
      End If
   Next i
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in cmdAssignNumbers_Click procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

LadyHagoodAuthor Commented:
any help.  anyone????
see if this link and free download comes close to what you want:
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Helen FeddemaCommented:
Here is a screen shot of the form:
So, we have machines but users have to create a line of (up to) 100 unique numbers... If you simply use autonumber field it will do the work for you.
If users must enter these numbers why they cannot enter them directly into the data grid? Uniqueness can be handled by unique index.
Mark WillsTopic AdvisorCommented:
I think we might benefit from further explanation of "The user needs to enter a range of numbers in a textbox ie 1-100"

I am not so sure it means that the user is going to enter 100 different numbers - or does it ?

if entering (literally)  "1-10"  does that mean select 10 rows and make sure that have a unique number ?

If extracting rows from a detailed table, then ideally use the autonumber facility - so long as the table that you might use to source that column does provide you with unique rows in your query, then just use the select TOP nn * from datasource, or, could use a subquery (or function) to get a count of rows lesser than the current row - bit of a performance overhead, and there are a couple of other ways as per suggestions above.

Anyway, if you could please provide a quick explanation / detail your requirement a bit more, it would help a lot...

Helen FeddemaCommented:
Autonumbers would work unless the numbers need to be overwritten (as I assumed in my sample code), or if they are actually meaningful numbers entered by the users (not clear from the initial question).  More information would indeed be helpful here.
Anthony PerkinsCommented:
>>any help.  anyone????<<
Any feedback????
LadyHagoodAuthor Commented:
Hey all, sorry just got back in office from a much need holiday break.  Helen thank you so much.  I think this will work trying this morning.   The reason i like this approach is because the numbers may be 50-80 or 1870-1890 etc.  there is no telling. and our users like things in one place.  to much manual work and things get ...complicated. Let me try and will get let you know.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.