Appending records to table by entering two values

[dark11984]

Hi i'm in the process of creating a form where I want to be able to enter two values then press a command button and it will append the the values and all values in between to a table. The values are the primary key
e.g. If i enter 1 and 5 on the form i want to append 1, 2, 3 , 4 & 5 to the table as the primary key.

At the same time i need to be abel to update the records with any common fields they may share. So if records 1-5 have the same company then i want to be able to enter xyz Pty Ltd on the form before i press the command button and this should update the company field with xyz Pty Ltd for records 1-5.

Any pointers to send me in the right direction would be much appreciated.

 

[Galaxiom]

Hard to be sure but there is a hint here that perhaps you could structure the database better to avoid having to do this.

Tell us more about the underlying need for this requirement.

 

[dark11984]

We issue out books that have sequential id number to different parts of the business and we want to keep track of which books are issued where. I may issue 10 books to one business at a time and rather then logging each books id number and details individually i want to be able to log all 10 books in one hit.

 

[SOS]

You should just set up the primary key to be an autonumber and then you can add whatever records you want. But it is possible to add records but how do you propose to identify the books to the records?

 

[dark11984]

Only 12 months later but got this one working this morning.

I created a new form with unbound fields and a command button with code attached to the command button.

Private Sub CmdPassport_No_add_Click()
If Me.TxtFirstPassportNo = "" Then
    MsgBox "You need to enter a starting range for passports you want to add."
ElseIf Me.TxtLastPassportNo = "" Then
    MsgBox "You need to enter a finishing range for passports you want to add."
 
ElseIf Me.CboCarrier = "" Then
    MsgBox "You need to enter a carrier for who the passports are being assigned to."
 
Else
    Dim PassportNoAddTable As Recordset
    Dim PassportNoCarrierAddTable As Recordset
    Dim X As String
 
    Set PassportNoAddTable = CurrentDb.OpenRecordset("TblPassport")
    Set PassportNoCarrierAddTable = CurrentDb.OpenRecordset("TblPassportEmp")
    X = Me.TxtFirstPassportNo
 
 
        Do Until X = Me.TxtLastPassportNo + 1
 
            PassportNoAddTable.AddNew
            PassportNoAddTable("PassportNo") = X
            PassportNoAddTable.Update
 
            PassportNoCarrierAddTable.AddNew
            PassportNoCarrierAddTable("Passportno") = X
            PassportNoCarrierAddTable("carrier") = Me.CboCarrier.Column(0)
            PassportNoCarrierAddTable.Update
 
            X = X + 1
        Loop
End If
 
Me.TxtFirstPassportNo = ""
Me.TxtLastPassportNo = ""
Me.CboCarrier = ""
 
End Sub

 

[Galaxiom]

Glad you found the solution.
One thing you could do to save typing and declutter the code.

Replace the likes of this:

PassportNoCarrierAddTable.AddNew
PassportNoCarrierAddTable("Passportno") = X
PassportNoCarrierAddTable("carrier") = Me.CboCarrier.Column(0)
PassportNoCarrierAddTable.Update

With:

With PassportNoCarrierAddTable
     .AddNew
     !Passportno = X
     !carrier = Me.CboCarrier.Column(0)
     .Update
End With

BTW, if Column(0) is the bound column you can leve that part off too.