Creating Multiple Records using button and text box to say how many to create

[Symon]

Hi all,

I am looking for a way to enable a user to fill in a number of fields on a form press a button(CreateButton) and duplicate the records how ever many times is stated in a text box(TxtQty). I also need this to increase the serial number by the amount of times stated in the text box.
There will need to be another text box (TxtNextSerialNumber) stating the next first available serial number

For Example:
TxtNextSerialNumber = AD-Oracle-00010
TxtQty = 5
Press CreateButton to create 5 records
TxtNextSerialNumber = AD-Oracle-00014
5 new records created with the below fields duplicated and the above happening.
I will then need a message box informing the user of the serial numbers created:
'You have created serial numbers AD-Oracle-00010 to AD-Oracle-00014'

Table Name: ADOracle
Form Name: ADOracleTestData

Fields Names:
CustomerName: Duplicate
PartNumber: Duplicate
OrderNumber: Duplicate
OrderDate: Duplicate
HoseKit: Duplicate
Returns: Duplicate
Comments: Duplicate
SerialNumber: + the amount shown in TxtQty starting on next serial number available.

Thanks for taking the time to read, and thanks in advance for helping me with this issue. I hope I have been clear in what my requirements are.

Cheers

Symon

 

[spikepl]

So you only have things called AD-Oracle that require a serial number, and you will never ever ever have anything else?

 

[Symon]

So you only have things called AD-Oracle that require a serial number, and you will never ever ever have anything else?

There are others too...

but due to the way our companies procedures work and the way people log information etc I have created separate forms for these so I would apply the VBA to each form.

Thanks

 

[shoji]

Symon,

I presume that you have the form open with the existing record. The following codes may cause a problem if you are looking at a new
record with all the data in but not saved yet.

Doing something like this on the form which also opens the same table
is notrecommended. I have not tested these codes and frankly I will
be surprised if they work. Amyway, this might give you some hints.

Shoji

Private Sub CreateButton_Click()
Dim rs As ADODB.Recordset
Dim I as Long
Dim lngCurrentRecord As Long
Dim strSN As String

    lngCurrentRecord = Me.CurrentRecord
   strSN = TxtNextSerialNumber

   Set rs = New ADODB.Recordset
   rs.Open "ADOracle", acOpenKeyset, acLockOptimistic
   With rs
     For I = 1 to TxtQty
       .AddNew
       !CustomerName = Me!CustomerName
       !PartNumber = Me!PartNumber
       ...

       strSN = NextSN(strSN)
       !OrderNumber: strSN

       .Update
    Next I
    rs.Close
    Set rs = Nothing

    Me.Requery
    ' This will requery the data source of the form.

    DoCmd.GotoRecord, , acGoTo lngCurrentRecord
    ' This will bring the form back to the current record. Without this, the first
    ' record will be shown on the form.

End Sub

Private Function NextSN(strSN1 As String) As String
Dim S As Long
    S = RevInstr(strSN1, "-",-1)
    strNum = Mid(strSN1, S+1)
    NextSN = Left(strSN1, S) & Right("000000" & CStr(Val(strNum) + 1), Len(strNum))
End Function

 

[Symon]

Symon,

I presume that you have the form open with the existing record. The following codes may cause a problem if you are looking at a new
record with all the data in but not saved yet.

Doing something like this on the form which also opens the same table
is notrecommended. I have not tested these codes and frankly I will
be surprised if they work. Amyway, this might give you some hints.

Shoji

Thanks Shoji, I'll have a play with this and see where I get to.

 

[Symon]

So I have had a look at this and it seems to be stuck on user defined type not defined - Dim rs As ADODB.Recordset -
but i cant seem to find the right reference to enable to get this to work..

here's the code so far with help from Shoji

Private Sub CreateButton_Click()
Dim rs As ADODB.Recordset
Dim I As Long
Dim lngCurrentRecord As Long
Dim strSN As String

    lngCurrentRecord = Me.CurrentRecord
   strSN = TxtNextSerialNumber

   Set rs = New ADODB.Recordset
   rs.Open "ADOracle", acOpenKeyset, acLockOptimistic
   With rs
     For I = 1 To TxtQty
       .AddNew
       !CustomerName = Me!CustomerName
       !PartNumber = Me!PartNumber
       !OrderNumber = Me!OrderNumber
       !OrderDate = Me!OrderDate
       !HoseKit = Me!HoseKit
       !Returns = Me!Returns
       !Comments = Me!Comments

       strSN = NextSN(strSN)
       !SerialNumber = strSN

       .Update
    Next I
    rs.Close
    Set rs = Nothing

    Me.Requery
    ' This will requery the data source of the form.

    DoCmd.GoToRecord , , acGoTo & "lngCurrentRecord" & Me.CurrentRecord
    ' This will bring the form back to the current record. Without this, the first
    ' record will be shown on the form.

End Sub

Private Function NextSN(strSN1 As String) As String
Dim S As Long
    S = RevInstr(strSN1, "-", -1)
    strNum = Mid(strSN1, S + 1)
    NextSN = Left(strSN1, S) & Right("000000" & CStr(Val(strNum) + 1), Len(strNum))
End Function
End Function

 

[shoji]

Oh, I didn't see your message until now.

Sorry I used ADO which is not the default library. I will use DAO instead this time. You don't have to add any new library. My DAO is a bit rusty, but I hope this does not include any errors.

Private Sub CreateButton_Click()
Dim db As Database
Dim rs As Recordset
Dim I As Long
Dim lngCurrentRecord As Long
Dim strSN As String

    lngCurrentRecord = Me.CurrentRecord
    strSN = TxtNextSerialNumber

    Set db = CurrentDb
    Set rs = db.OpenRecordset("ADOracle", dbOpenDynaset)
    With rs
     For I = 1 To TxtQty
       .AddNew
       !CustomerName = Me!CustomerName
       !PartNumber = Me!PartNumber
       !OrderNumber = Me!OrderNumber
       !OrderDate = Me!OrderDate
       !HoseKit = Me!HoseKit
       !Returns = Me!Returns
       !Comments = Me!Comments

       strSN = NextSN(strSN)
       !SerialNumber = strSN

       .Update
    Next I
    rs.Close
    Set rs = Nothing

    Me.Requery
    ' This will requery the data source of the form.

    DoCmd.GoToRecord , , acGoTo & "lngCurrentRecord" & Me.CurrentRecord
    ' This will bring the form back to the current record. Without this, the first
    ' record will be shown on the form.

End Sub

Private Function NextSN(strSN1 As String) As String
Dim S As Long
    S = RevInstr(strSN1, "-", -1)
    strNum = Mid(strSN1, S + 1)
    NextSN = Left(strSN1, S) & Right("000000" & CStr(Val(strNum) + 1), Len(strNum))
End Function