assign a range of numbers in access 2003

Posted on 2009-12-30
Last Modified: 2013-11-28
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?  
Question by:LadyHagood

    Author Comment

    any help.  anyone????
    LVL 38

    Expert Comment

    see if this link and free download comes close to what you want:
    LVL 31

    Accepted Solution

    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

    LVL 31

    Expert Comment

    Here is a screen shot of the form:
    LVL 41

    Expert Comment

    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.
    LVL 41

    Expert Comment

    If users must enter these numbers why they cannot enter them directly into the data grid? Uniqueness can be handled by unique index.
    LVL 51

    Expert Comment

    by:Mark Wills
    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...

    LVL 31

    Expert Comment

    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.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>any help.  anyone????<<
    Any feedback????

    Author Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now