October 19, 2005 at 6:25 am
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
October 19, 2005 at 6:28 am
Why would you insert 5 records instead of one record with 5 as quantity?
October 19, 2005 at 6:40 am
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.
October 19, 2005 at 6:55 am
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
October 19, 2005 at 7:32 am
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.
October 19, 2005 at 7:37 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply