Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Generate several sequentially numbered records

Discussion in 'Information Technology' started by socasteel21 via AccessMonster.com, Jul 28, 2006.

  1. socasteel21 via AccessMonster.com

    Guest

    I have a table called PartNumber. This table includes every part number
    every created in the factory. If the last part number I have is 140685, I
    would like to be able to have a button that I push to add 200 new records
    numbered as follows: 140686, 140687, 140688,....etc. There are also other
    fields in the table, but upon adding these numbers I want to leave these
    fields blank. They will be filled in later.


    Thanks for your help.

    Shannan

    --
    Message posted via http://www.accessmonster.com
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Punjab to use vacant land to generate revenues (New Kerala) Breaking News Feb 19, 2008
    India Hindu fanatics behind several sensational cases: Govt Breaking News Jan 11, 2013
    India Raid finds several schools locked Breaking News Sep 23, 2012
    Sikh News Several trains cancelled due to protest by Sikh bodies Breaking News Nov 4, 2009
    Sikh News Flood-like situation in several Punjab districts (Calcutta News) Breaking News Aug 14, 2008

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    This example shows how to open the table, get the highest number, and add
    the number of records passed in.

    It assumes a table named tblPart, with a Number field named PartNo.

    To add another 200 parts, you could open the Immediate Window (Ctrl+G) and
    enter:
    ? MakeData(200)

    Function MakeData(HowMany As Long)
    Dim strSql As String
    Dim rs As DAO.Recordset
    Dim lng As Long
    Dim lngStartFrom As Long

    strSql = "SELECT TOP 1 [PartNo] FROM [tblPart] ORDER BY [PartNo] DESC;"
    Set rs = DBEngine(0)(0).OpenRecordset(strsql)

    If rs.RecordCount = 0 Then
    lngStartFrom = 1
    Else
    lngStartFrom = rs![PartNo] + 1
    End If

    For lng = lngStartFrom To lngStartFrom + HowMany
    rs.AddNew
    rs![PartNo] = lng
    rs.Update
    Next

    rs.Close
    Set rs = Nothing
    End Function

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "socasteel21 via AccessMonster.com" <u11033@uwe> wrote in message
    news:6154584668be5@uwe...
    >I have a table called PartNumber. This table includes every part number
    > every created in the factory. If the last part number I have is 140685, I
    > would like to be able to have a button that I push to add 200 new records
    > numbered as follows: 140686, 140687, 140688,....etc. There are also
    > other
    > fields in the table, but upon adding these numbers I want to leave these
    > fields blank. They will be filled in later.
     
  4. socasteel21 via AccessMonster.com

    Guest

    If I paste this code into VBA, how do I use a button on one form to open the
    Part Number form, and run this function. Therefore, if I am understanding
    this code correctly, I press the button on my switch board, and the form
    (PartNumber) opens with several new records (however many I tell it to add).

    Thanks for your help.

    Shannan

    Allen Browne wrote:
    >This example shows how to open the table, get the highest number, and add
    >the number of records passed in.
    >
    >It assumes a table named tblPart, with a Number field named PartNo.
    >
    >To add another 200 parts, you could open the Immediate Window (Ctrl+G) and
    >enter:
    > ? MakeData(200)
    >
    >Function MakeData(HowMany As Long)
    > Dim strSql As String
    > Dim rs As DAO.Recordset
    > Dim lng As Long
    > Dim lngStartFrom As Long
    >
    > strSql = "SELECT TOP 1 [PartNo] FROM [tblPart] ORDER BY [PartNo] DESC;"
    > Set rs = DBEngine(0)(0).OpenRecordset(strsql)
    >
    > If rs.RecordCount = 0 Then
    > lngStartFrom = 1
    > Else
    > lngStartFrom = rs![PartNo] + 1
    > End If
    >
    > For lng = lngStartFrom To lngStartFrom + HowMany
    > rs.AddNew
    > rs![PartNo] = lng
    > rs.Update
    > Next
    >
    > rs.Close
    > Set rs = Nothing
    >End Function
    >
    >>I have a table called PartNumber. This table includes every part number
    >> every created in the factory. If the last part number I have is 140685, I

    >[quoted text clipped - 3 lines]
    >> fields in the table, but upon adding these numbers I want to leave these
    >> fields blank. They will be filled in later.


    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200606/1
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    If you have a text box named txtHowMany on some form, you could set the On
    Click property of a command button to:
    =MakeData([txtHowMany])

    Your PartNumber form does not need to be open to add these records.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "socasteel21 via AccessMonster.com" <u11033@uwe> wrote in message
    news:615f09329343b@uwe...
    > If I paste this code into VBA, how do I use a button on one form to open
    > the
    > Part Number form, and run this function. Therefore, if I am understanding
    > this code correctly, I press the button on my switch board, and the form
    > (PartNumber) opens with several new records (however many I tell it to
    > add).
    >
    > Thanks for your help.
    >
    > Shannan
    >
    > Allen Browne wrote:
    >>This example shows how to open the table, get the highest number, and add
    >>the number of records passed in.
    >>
    >>It assumes a table named tblPart, with a Number field named PartNo.
    >>
    >>To add another 200 parts, you could open the Immediate Window (Ctrl+G) and
    >>enter:
    >> ? MakeData(200)
    >>
    >>Function MakeData(HowMany As Long)
    >> Dim strSql As String
    >> Dim rs As DAO.Recordset
    >> Dim lng As Long
    >> Dim lngStartFrom As Long
    >>
    >> strSql = "SELECT TOP 1 [PartNo] FROM [tblPart] ORDER BY [PartNo]
    >> DESC;"
    >> Set rs = DBEngine(0)(0).OpenRecordset(strsql)
    >>
    >> If rs.RecordCount = 0 Then
    >> lngStartFrom = 1
    >> Else
    >> lngStartFrom = rs![PartNo] + 1
    >> End If
    >>
    >> For lng = lngStartFrom To lngStartFrom + HowMany
    >> rs.AddNew
    >> rs![PartNo] = lng
    >> rs.Update
    >> Next
    >>
    >> rs.Close
    >> Set rs = Nothing
    >>End Function
    >>
    >>>I have a table called PartNumber. This table includes every part number
    >>> every created in the factory. If the last part number I have is 140685,
    >>> I

    >>[quoted text clipped - 3 lines]
    >>> fields in the table, but upon adding these numbers I want to leave these
    >>> fields blank. They will be filled in later.

    >
    > --
    > Message posted via AccessMonster.com
    > http://www.accessmonster.com/Uwe/Forums.aspx/access/200606/1
     

Share This Page