• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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?  
  • 3
  • 2
  • 2
  • +3
1 Solution
LadyHagoodAuthor Commented:
any help.  anyone????
see if this link and free download comes close to what you want:
Helen FeddemaCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now