How do I create number list? 4

[eb1mom]

Switching from paper forms to Access is going very slowly. I am trying to create a requisition form to be used during this bumpy time. I need to create a list of numbers to be used on Access requisiton forms that will sequence with paper requisiton form numbers. For example one month our department will be given requisiton numbers 500-599 the next month perhaps 800-899, I would like to be able to enter 500 start and 599 end and have a full number list created in a table. If someone could point me to an example I would appreciate it. Thank-you

 

[Mute101]

Hi,

Here is an example of something you can adapt to do sort of what you need.

Dim dtToday, dtMonth, dtYear

    dtToday = Now()
    dtMonth = Me.txtMonth
    dtYear = Me.txtYear
    
If Month(dtToday) = 1 And dtMonth = 12 Then
    Me.txtNumber = 0
    Me.txtMonth = 1
    Exit Sub
End If

If Month(dtToday) > Int(dtMonth) Then
    Me.txtNumber = 1
    Me.txtMonth = Month(dtToday)
Else
    Me.txtNumber = Format(Me.txtNumber + 1, "0###")
End If

If Year(dtToday) > Year(dtYear) Then
    Me.txtYear = Year(dtToday)
Else
    Me.txtYear = Year(dtToday)
End If

MsgBox Me.txtYear & Me.txtMonth & Me.txtNumber

This code increments numbers in three fields on the form which you can then use to create one larger number you can use for your requisition number.

Create a new form, add three textboxes called txtYear, txtMonth and txtNumber and place the code above behind a buttons 'onClick' event. Then enter a year (2004) month (10) and number(1) into the text boxes. When you click the button the number will increment. when the month changes it will increment the month, same for the year. It is a simple matter to change the code to work from table values which can store the year, month and number in seperate fields.

Whilst this is a bit more work than what you requested, once in place you will never have to worry about number sequences again!

Hope that help and I haven't confused matters even more, if you need clarification just shout.

Simon

----------------------------------------
My doctor says that I have a malformed public duty gland and a natural deficiency in moral fibre and that I'm therefore excused from saving universes.
----------------------------------------

 

[BobStubbs]

Two ideas for you ...

1. A 'Cheat' using Excel.
-- Use the 'Fill ... Series ... Step by 1' function in Excel to create a worksheet with the range of numbers which you require.
-- Use 'File ... Get external data' to import the list of numbers into your database table.

2. A VBA example, which will create a set of records for you and set the ID number field in each one:

Private Sub btnMakeRecords_Click()

Dim intRecordID As Integer
Dim intFirstRecord As Integer
Dim intNumberOfRecords As Integer

Dim strSQL As String

    intFirstRecord = 301
    intNumberOfRecords = 100
   
    DoCmd.SetWarnings (False)
    
    For intRecordID = intFirstRecord To intFirstRecord + intNumberOfRecords - 1
        strSQL = "INSERT INTO devTestTable (RecordID) "
        strSQL = strSQL & "VALUES (" & CStr(intRecordID) & ")"
        DoCmd.RunSQL (strSQL)
    Next intRecordID

    DoCmd.SetWarnings (True)

    MsgBox "Finished"

End Sub

Add this code to a button on a form, which the DBA can use to create batches of blank records when required. If you want to be really neat, set the variables via text boxes on the form. In my example, I have coded the values directly to make things clearer.

-- Set intFirstRecord to the first record ID number required
-- Set intNumberOfRecords to the number of new records required.

My example code above will create 100 records, numbered from 301 to 400.

I hope that this helps.

Bob Stubbs

 

[M8KWR]

unsure if this will help, but you could create the number you want in excel, easily by dragging 500 down to 599. T

Then import this into access.

Create an Ammend query, and update the 100 records you have create in excel to an access table, to create the 100 records in access.

You could have 2 ID number, one that the auto number, and the other which is the number you want for the requisiton numbers.

Hope this helps..

 

[Trendsetter]

To create a table with a list of sequenced numbers can be done with this code:
Design a 'new' table say... tblRequisitions
Fields:
ReqID Autonumber - increment
ReqNum Text size 3
... whateever other fields you want to use

'new' form with 2 textboxes
txtStartNum
txtEndNum
Command button - cmdRunCode - caption "Make table"

Code on the On_click event of cmdRunCode
Dim intStart, intEnd, intReqNum As integer
Dim rst as recordset
Set rst = CurrentDb.OpenRecordset("Select * From tblRequisitions")
intStart = Me.txtStartNum
intEnd = Me.txtEndNum
intReqNum = intStart
Do while intReqNum <= intEnd
rst.AddNew
rst!ReqNum = intReqNum
rst.update
intReqNum = intReqNum + 1
Loop
rst.Close
Set rst = Nothing

I have not run this code and made it up on the fly, but the principle is there - if you need an explanation please let me know.





Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[eb1mom]

Thank-you everyone for all the help. You have given me many options and the road looks a lot smoother now.