Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Inserting multiple records depending on the value of a drop down menu

    Hi

    Im having a few problems with a school project i am working on in Microsoft Access. Im working on a system that manages the stock levels of ink cartridges for the school. The thing that i am particularly stuck on is how would i insert multiple ink cartridges in one go.

    I would be using a form where the type of ink cartridge would be selected and then a value in a drop down menu would be selected or a value typed into a text box for the number of cartridges of that particular type and then that number of records would be created.

    I have searched high and low to find a solution for this and from what i can tell i would have to use a recordset or some kind of VB module to perform this.

    Any help would be greatly appreciated.

    Ben

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    • Place an Unbound Textbox in the Header Section of your Form.
    • Name it NumberOfRecords.
    • Place a Command Button in the Header of the Form.
    • Name it CreateMutliRecords

    Place this code behind the Button:
    Code:
    Private Sub CreateMutliRecords_Click()
    
    If Nz(Me.CartridgeType, "") = "" Then
      MsgBox "You Must First Enter a Cartidge Type!"
      Exit Sub
    End If
    
    If Nz(Me.NumberOfRecords, 0) <> 0 Then
      varNumRecords = Me.NumberOfRecords
    Else
      MsgBox "You Must Enter the Number of Records to be Created!"
      Exit Sub
    End If
    
    For I = 1 To varNumRecords - 1
      If Me.Dirty Then Me.Dirty = False
      varCartridge = Me.CartridgeType
      DoCmd****nCommand acCmdSelectRecord
      DoCmd****nCommand acCmdCopy
      DoCmd.GoToRecord , , acNewRec
      DoCmd****nCommand acCmdPaste
      Me.CartridgeType = varCartridge
    Next I
    
    End Sub
    Because of a quirk in this website, it won't allow us to type and display anything containing a Dot (.) followed by ru, so in the code above, everwhere you see

    DoCmd****nCommand

    replace it with

    DoCmd . RunCommand


    simply Deleting the spaces on either side of the Dot.

    Now all you need to do is

    • Create a New Record and enter the Cartridge Type
    • Enter the total number of Records you want to generate
    • Click on the Button

    Access will create the number of Records (including the one already created) indicated with the given CartridgeType.

    You could use a Combobox instead of a Textbox, but that's really overkill in order to simply enter a number.

    Linq ;0)>
    Last edited by Missinglinq; 01-12-12 at 04:07.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2012
    Posts
    2

    Thanks

    Hi

    Thanks for the brilliant reply. One question though and this will probably seem very thick and rather a basic question but how do i link that to a specific cartridge type?

    thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you define a cartridge type define in that how many comprise a set
    then use dlookup or simialr to find the number int h set
    use that variable as the loop upper limit
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As I said:

    Quote Originally Posted by Missinglinq View Post

    Now all you need to do is

    • Create a New Record and enter the Cartridge Type
    • Enter the total number of Records you want to generate
    • Click on the Button

    Access will create the number of Records (including the one already created) indicated with the given CartridgeType.
    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Posting Permissions

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