Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a range (97 and XP)

    I am trying to develop a form that allows me to enter a range using two fields. I want the first field to be the first number in the range and the second field to be the last number in the range. I want the entire range to auto populate into the appropriate table. For example: I type in 184001 and 184060; this should create 60 records. Actually it would be best if I could create 2 ranges on my form. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a range (97 and XP)

    Welcome to Woody's Lounge!

    Say that you create text boxes txtStart and txtStop and a command button cmdCreate on the form. The On Click event procedure for the command button can look like this:
    The table is named tblData and it has a field of type Number (Long Integer) named SeqNum.
    <code>
    Private Sub cmdCreate_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Long

    On Error GoTo ErrHandler

    If IsNull(Me.txtStart) Then
    MsgBox "Please enter a start number.", vbExclamation
    Me.txtStart.SetFocus
    Exit Sub
    End If

    If IsNull(Me.txtEnd) Then
    MsgBox "Please enter an end number.", vbExclamation
    Me.txtEnd.SetFocus
    Exit Sub
    End If

    If Me.txtEnd < Me.txtStart Then
    MsgBox "The end number cannot be lower than the start number.", vbExclamation
    Me.txtEnd.SetFocus
    Exit Sub
    End If

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblData", dbOpenDynaset)

    For i = Me.txtStart to Me.txtEnd
    rst.AddNew
    rst!SeqNum = i
    rst.Update
    Next i

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    Substitute the appropriate names.

    Note: this code uses DAO. In Access 97, you automatically have a reference to the DAO library, but in Access 2002 (XP), it is not standard. Select Tools | References... in the Visual Basic Editor, and tick the check box for Microsoft DAO 3.6 Object Library.

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a range (97 and XP)

    What is the Control Source for txtStart and txtStop?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a range (97 and XP)

    I thought that you wanted to let the user enter start and end values. If so, the text boxes must be unbound, i.e. their control source is blank.

    If the start and end values are stored in a table, the table should be (part of) the record source of the form, and the text boxes should be bound to those fields.

  5. #5
    New Lounger
    Join Date
    Dec 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a range (97 and XP)

    No you are right I don't need a control source. Good point. I am a little new to Access-still getting the hang of it. The reason I asked is because I did what you said in 97 and used the code you provided and I get an error message when I click the cmdCreate command button. The error reads "Item not found in this collection." Does this mean anything to you. Is it talking about the command button or what. What is the item that isn't found? I don't get it.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a range (97 and XP)

    The error message probably means that the name of the table or field is incorrect. You can test it like this: temporarily make the line

    On Error GoTo ErrHandler

    into a comment by inserting an apostrophe ' in front of it:

    ' On Error GoTo ErrHandler

    When you click the command button, you'll be given a choice. Click Debug and note which line is highlighted in yellow. You can end code execution by clicking the Reset button on the toolbar.

  7. #7
    New Lounger
    Join Date
    Dec 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a range (97 and XP)

    Hey guys I'm back again with a question about the same form. I just realized that something is wrong. If I type in for the start field "900" and in the end field "1000" it gives me an error message saying that "the end number cannot be lower then the start number" apparently access thinks that 900 is higher than 1000. It is going by the first number. I currently have this number set up as a long integer. What do I do to remedy this?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a range (97 and XP)

    The problem is that Access compares the values alphabetically. To change this:
    Open the form in design view.
    Select txtStart and txtStop.
    Set the Format property to a number format, for example General Number or Fixed.
    This will make Access treat the values as numbers instead of as text values.

  9. #9
    New Lounger
    Join Date
    Dec 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a range (97 and XP)

    Worked perfectly!!! Why on earth would it default to alphabetical order? These are numbers we are talking about. Letters should be alphabetical, numbers should be numerical, and dates should be chronological. Seems like a no brainer to me!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create a range (97 and XP)

    For text boxes bound to a field, Access knows the data type of the field, so it knows how to treat the contents of the text boxes. However, txtStart and txtStop are unbound text boxes, so unless you tell Access explicitly to treat their contents as numbers or dates, it can only assume that their values are text.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •