inserting data from unbound access form

Question

Post reply

inserting data from unbound access form

Niall Armstrong

SSC Enthusiast

Points: 122
More actions
October 19, 2005 at 6:25 am

#67000

Hope anyone out there can help me.
I have an unbound access form with 4 combo boxes:
Make, Model, Size & Amount.
The first uses a SELECT DISTINCT statement to list the Makes and an AFTERUPDATE event procedure to update the Model Combo box to list the related models. This is repeated for the Model Combo Box to update the size combo box.
What i would really like to know is how to select the appropriate Make, Model & Size and then insert the record(s) into a stock table according to the Amount (1,2,3,4 etc) that is selected. (i.e. Amount = 5 would insert five records into the table).
Any help would be much appreciated
Armo

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Why would you insert 5 records instead of one record with 5 as quantity?

Niall Armstrong SSC Enthusiast Points: 122 More actions · Answer 2

This is for an ordering system, i want the user to be able to create a list of stock ordered. However, each individual unit will be allocated an individual StockID and at a later stage (when delivery arrives) given a date in. Not all stock ordered will be delivered on the same day.

In other words i am trying to save the users time. We may order the stock in units, but each item is treated as an individual stock item once in stock.

I hope this makes sense.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Just create a numbers table >>

IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo')

DROP TABLE Numbers

GO

CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)

GO

INSERT INTO dbo.Numbers (dude)

SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80

GO

ALTER TABLE dbo.Numbers

DROP COLUMN dude

You might have to do the insert in a loop since you seem to be doing this in Access.

Then :

insert into table (col1, col2...) select @Value1, @Value2... from dbo.Numbers where PkNumber <= @Quantity

Niall Armstrong SSC Enthusiast Points: 122 More actions · Answer 4

Thanks for your time, i can create the numbers table using your code, however im not sure about creating an insert loop. As you may have worked out im new to SQL.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

in vb code:

Dim i as integer

i=0

while i<8000

currentproject.connection.execute "Insert into Numbers DEFAULT VALUES"

i=i+1

wend

and you don't need the dude column to make this insert that way.