Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010

    Unanswered: Creating multiple records at same time


    I'm trying to create several records at the same time.


    There is a voucher book that holds 100 vouchers, each voucher has a number

    For example 100 to 199

    A record needs to be made into the database for each voucher.

    So i want a form in Access with two text boxes, one for first voucher and the second for last voucher. (again, eg, 100 and 199)

    I then want there to be a button so when it is pressed, it generates a record for each voucher between the numbers given.

    My table is called tlbvoucher and the field i want the number to be generated into is called 'voucherNo' and set as an integer.

    My form is called 'frmGenerate' and the two text boxes are called Text1 and Text2

    I'm guessing I need a bit of VB coding for the button in order to do this but I'm at a loss with everything I have tried.

    Any help would be much appreciated

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Place your two textboxes in the header section of the form, along with a Command Button.

    Name the Command Button GenerateRecords. Now use this code iin the OnClick event of the form:
    Private Sub GenerateRecords_Click()
    Dim mn As Long
    Dim Rec As Long
    Rec = (Me.Text2 - Text1) + 1
    For mn = 1 To CInt(Rec)
      DoCmd.GoToRecord , , acNewRec
      Me.VoucherNo = mn + (Text1 - 1)
    Next mn
    End Sub
    This has to be a Continuous or Single View form, of course. Datasheet View forms don't allow for Command Buttons, or Header Sections, for that matter.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Sep 2010
    thanks for the help!

    Got another query if you could helpl.

    It's also a function of the database to assign certain blocks of numbers to a department.

    Example: Vouchers 100 - 120 are assigned to Department 1
    Vouchers 121 - 140 assigned to Department 2

    At present I have an update query, and in a form the user enters the voucher numbers they need to update, then they type in the department in another text box.

    Then theres a button on the form that runs the update query and adds in the department to all the records. Which all works fine.

    However i want to add some validation so if a block of numbers already has a department assigned to it, then I want an error message to come up informing the user that they can't add to that field.

    Hope I've made sense

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts