Creating new records with do until loop 1

[access345]

I need to create a module (for the first time) that adds a specific amount of serial numbers to the database. I will have the form ask for a first serial number and a last serial number
What I want is for the loop to create the amount of records I have asked for Ex. First record = 255 Last record = 300 I would want the module to create 45 records in the database. With the Code name attached.

I have started with the following code:

Public Sub addserialnumbers()

On Error GoTo ErrorHandler

Dim Codename As Double
Dim SerialNumber As Long
Dim Firstrecord As Long
Dim LastRecord As Long


'Add user defined amount of serial numbers
For SerialNumber = Firstrecord To SerialNumber = LastRecord
Add.new SerialNumber
Add new Code Name
End if LastRecord = LastRecord
Next SerialNumber


End Sub

 

[jadams0173]

One way may be:
you will need to add a reference to microsoft DAO library

dim rs as dao.recordset
dim db as dao.database
dim x,start, end  as long

set db=currentdb

set rs=db.openrecordset("yourtable")

for x= start to end
   with rs
     .addnew
      !serialnumber=x
     .update
   end with
next x
rs.close
set rs=nothing
set db=nothing

I tried to have patience but it took to long! -DW

 

[Remou]

The following example is intended for a DAO recordset, so you will need to check that you have a reference to the Microsoft DAO 3.x Object Library. You will also need a form to run this code from with three textboxes, FirstRecord, LastRecord, CodeName

Public Sub addserialnumbers()

On Error GoTo ErrorHandler

Dim Codename As Double
Dim SerialNumber As Long
Dim Firstrecord As Long
Dim LastRecord As Long

Dim rs As DAO.Recordset
Set rs=CurrentDB.OpenRecordset("Name of Table")

'Add user defined amount of serial numbers
 For i = Me.Firstrecord To SerialNumber = Me.LastRecord
 rs.Addnew 
 rs!SerialNumber = i
 rs!CodeName = Me.CodeName
 rs.Update
 Next 'SerialNumber
  
End Sub

The above is typed, not tested, so please take it as an example, not functioning code.

 

[access345]

Remou, I decided to use your suggestion, Thanks! I created a form to use this sub so how do I call this sub from the form?

 

[Remou]

The usual way is to add a command button, you can call the code from the click event. You will need to do some checking to make sure users don't click twice and to make sure a suitable range is chosen.

 

[access345]

I added the button on the form this is the code
Private Sub btnNewSN_Click()
On Error GoTo Err_btnNewSN_Click


DoCmd.OpenModule "addnew"

Exit_btnNewSN_Click:
Exit Sub

Err_btnNewSN_Click:
MsgBox Err.Description
Resume Exit_btnNewSN_Click

End Sub


This is the module:

Public Sub addserialnumbers()

On Error GoTo ErrorHandler

Dim CodeName As Text
Dim SerialNumber As Long
Dim FirstSN As Long
Dim LastSN As Long

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TblPAQ3280")

'Add user defined amount of serial numbers
For i = Me.FirstSN To SerialNumber = Me.LastSN
rs.AddNew
rs!SerialNumber = i
rs!CodeName = Me.CodeName
rs.Update
Next SerialNumber

End Sub

When I try to compile this module I get an error
compile error user type not defined

I went to references and the following are checked:
Visual Basic for applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library

 

[Remou]

Private Sub btnNewSN_Click()
On Error GoTo Err_btnNewSN_Click


    [blue]Call addserialnumbers[/blue]

Exit_btnNewSN_Click:
    Exit Sub

Err_btnNewSN_Click:
    MsgBox Err.Description
    Resume Exit_btnNewSN_Click
    
End Sub

Public Sub addserialnumbers()

On Error GoTo ErrorHandler

Dim CodeName [blue]As String[/blue]
[blue]'[/blue]Dim SerialNumber As Long
Dim FirstSN As Long
Dim LastSN As Long
[blue]Dim i As Long[/blue]

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TblPAQ3280")

'Add user defined amount of serial numbers
 For i = Me.FirstSN To [s]SerialNumber = [/s]Me.LastSN
 rs.AddNew
 rs!SerialNumber = i
 rs!CodeName = Me.CodeName
 rs.Update
 Next [s]SerialNumber[/s]
  
End Sub

PS If you use [ignore]

[/ignore] around your code, it will be clearer.

 

[access345]

When I compile the new module

I get a compile error invalid use of me keyword at the Me.FirstSN


Public Sub addserialnumbers()

On Error GoTo ErrorHandler

Dim CodeName As String
Dim SerialNumber As Long
Dim FirstSN As Long
Dim LastSN As Long
Dim i As Long

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TblPAQ3280")

'Add user defined amount of serial numbers
For i = Me.FirstSN To Me.LastSN
rs.AddNew
rs!SerialNumber = i
rs!CodeName = Me.CodeName
rs.Update
Next

End Sub

 

[Remou]

Is the code in a module belonging to a form?

 

[access345]

No. It is in the module section of the database. How do I get the module to belong to the form?

 

[Remou]

Open the form in design view, and double-click on the command button to open the properties window, if it is not already open. Go to the Events tab and choose the On Click event. Set it to [Event Procedure], then click on the three little dots to the right. Choose Code if you are offered a list of things to do. Paste your code under the click event for the command button. The title bar of the code window will show the name of the form.

 

[access345]

This is the code I have on the click property of my command button

Private Sub btnNewSN_Click()
On Error GoTo Err_btnNewSN_Click


Call addserialnumbers


Exit_btnNewSN_Click:
Exit Sub

Err_btnNewSN_Click:
MsgBox Err.Description
Resume Exit_btnNewSN_Click

End Sub


This is the code I have in the module:

Option Compare Database

Public Sub addserialnumbers()


Dim txtCodeName As String
Dim SerialNumber As Long
Dim txtFirstSN As Long
Dim txtLastSN As Long
Dim i As Long

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TblPAQ3280")

'Add user defined amount of serial numbers
For i = Me.txtFirstSN To Me.txtLastSN
rs.AddNew
rs!SerialNumber = i
rs!CodeName = Me.txtCodeName
rs.Update
Next

End Sub


I still get the compile error: invalid use of the ME command

 

[Remou]

Cut this bit:

Public Sub addserialnumbers()


Dim txtCodeName As String
Dim SerialNumber As Long
Dim txtFirstSN As Long
Dim txtLastSN As Long
Dim i As Long

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TblPAQ3280")

'Add user defined amount of serial numbers
 For i = Me.txtFirstSN To Me.txtLastSN
 rs.AddNew
 rs!SerialNumber = i
 rs!CodeName = Me.txtCodeName
 rs.Update
 Next
  
End Sub

Out of the module and paste it just under the click code:

Private Sub btnNewSN_Click()
On Error GoTo Err_btnNewSN_Click


    Call addserialnumbers


Exit_btnNewSN_Click:
    Exit Sub

Err_btnNewSN_Click:
    MsgBox Err.Description
    Resume Exit_btnNewSN_Click
    
End Sub

[red]Paste Here[/red]

 

[access345]

It worked !!!! thanks so much for all your time