Generate several sequentially numbered records

[socasteel21 via AccessMonster.com]

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

 

[Allen Browne]

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

 

[socasteel21 via AccessMonster.com]

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

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.