Adding Multiple Records

[groups]

Ok, this may turn out to be longwinded, so pelase bare with me...

I am working on an inventory/stock control database for the IT systems
in the school I work at. I currently have two tables that deal with
printer consumables that I am trying to work with:

tblCartridgeType which holds the details for the model of cartridge
tblCartridges which holds a record for each cartridge we have in stock.

tblCartridgeType has a "Name" field (text) which holds our name for the
cartridge model and tblCartridges has a "CartridgeType" field (number)
which is related to tblCartridgeType's autonumber field (primary key).

What I am trying to do is create a form where I can select a cartridge
type from a drop down list (cboCartType) and then select a number from
a second list which relates to the number of records to add (ie, the
number of cartridges of that type to add to stock). When I click the
add button, I want Access to add that number of records into
tblCartridges, all with the CartridgeType field set to the value of
cboCartType. (Have I lost you yet... :P )

I thought it could be done with an Append query, but I just can't
figure out how. Also, I'm not to hot on VBA so I haven't been able to
figure out how to do it that way either.

Any help would be very greatly appreciated!

RobC.

[Br]

gr****@cowannet.com wrote:

Ok, this may turn out to be longwinded, so pelase bare with me...

I am working on an inventory/stock control database for the IT systems
in the school I work at. I currently have two tables that deal with
printer consumables that I am trying to work with:

tblCartridgeType which holds the details for the model of cartridge
tblCartridges which holds a record for each cartridge we have in
stock.

tblCartridgeType has a "Name" field (text) which holds our name for
the cartridge model and tblCartridges has a "CartridgeType" field
(number) which is related to tblCartridgeType's autonumber field
(primary key).
Don't use reserved words for field/object names. Use something like
"CartridgeName".
What I am trying to do is create a form where I can select a cartridge
type from a drop down list (cboCartType) and then select a number from
a second list which relates to the number of records to add (ie, the
number of cartridges of that type to add to stock). When I click the
add button, I want Access to add that number of records into
tblCartridges, all with the CartridgeType field set to the value of
cboCartType. (Have I lost you yet... :P )

I thought it could be done with an Append query, but I just can't
figure out how. Also, I'm not to hot on VBA so I haven't been able to
figure out how to do it that way either.

Any help would be very greatly appreciated!

RobC.

Sounds like you just need a simple loop...

Dim i as Long, myRS as Recorset
Set myRS = CurrentDB.OpenRecordset("tblCartridges")
For i = 1 to Me![NumberOfCartridges]
With myRS
.AddNew
![CatridgeTypeID] = Me![CatridgeTypeID]
' NOTE: you don't need to hold the value of the name as you can
just look it up from the ID
.Update
End With
Next

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response