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

bob0987654321 · #1 (**permalink**) 01-11-12, 17:54

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

Missinglinq · #2 (**permalink**) 01-12-12, 03:03

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)>

bob0987654321 · #3 (**permalink**) 01-12-12, 10:23

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

healdem · #4 (**permalink**) 01-12-12, 10:38

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