Entering one item to create multiple entries in a table

[MCCDOM]

Hi all,

I am a little bit stumped on how to do this so would really appreciate some help.

Basically I am inputting stock into my database so I can have a record of what I have left and who it's gone too. Where I'm stuck though is when I want to add an item that I have more than one off but only enter it once and have it assigned a unique stock ID number (this is in the form of an autonumber) for however much quantity I choose to enter. So for example I have 5 keyboards, I type in 'Keyboard' into my item field (named txtItem) and then type '5' into my quantity field (named txtQuantity). When I then press Add Stock (named btnAddStock) I should then see in the table, 5 separate Keyboards listed each with a different Stock ID number.

Hopefully I've explained this enough but any questions please don't hesitate to ask.

Look forward to your replies

Thanks

Dom

 

[namliam]

Assuming you have the entry fields already unbound...
You are looking for something like....

dim rs as dao.recordset
Dim I as integer
set rs = currentdb.openrecordset("Select * from [YourStockTable]"
For I = 1 to Me.YourControlCOntaining5
    rs.addnew
    rs![YourDescriptionField] = me.YourControlContainingKeyboard
    rs![Otherfields] = ...
 etc.
    rs.update
next I
rs.close
set rs = nothing

 

[pr2-eugin]

Before giving you some explanation or solution I am intrigued to know why you would want 5 separate records as opposed to one record holding the quantity.

 

[MCCDOM]

Hi there namliam,

Thanks for your quick reply. The quantity field is unbound but not the item field as that is linked to the control source of the stock table. Do I need to re-design this to achieve what I want?

 

[MCCDOM]

Hi pr2-eugin,

Basically I want a list of all the stock I have and to be able to search for a specific item to see who it has been allocated to and when it was purchased etc. and this is why they all need a unique stockID even if they are the same item. So to save time I would like to enter an item into the form, choose the quantity (say 5) and have 5 of the same item entered individually 5 times with their own stockID into the stock table.

Hope this makes sense.

 

[namliam]

Well if your entry field is already bound to the table, then you already entered one.... so all you need to do is enter 4 more... shouldnt be that hard?

 

[pr2-eugin]

I am still not convinced, one order can have one or many quantities. As a person who is shopping, if I buy 5 keyboards I would want them to have one order number so I can retrieve my transaction quickly. Unless I buy one keyboard and one mouse which are two separate orders.

However, in this scenario, I would suggest you to add records using an unbound form rather than using a bound form, so you can loop to add as many records as you need.

Private Sub buttonClick()
    Dim iCtr As Integer
    For iCtr = 0 To Me.yourOrderQtyControlName
        CurrentDB.Execute "INSERT INTO yourOrderTableName (orderItem) VALUES ('" & Me.orderItemDescription & "')"
    Next
End Sub

 

[MCCDOM]

Apologies I might not of made this clear. This isn't a retail shop stock table but instead an office stock table. The scenario would be a member of staff phones up and says their keyboard is not working so they read out the unique stockID labelled on the keyboard. I then search for this stockID number taking me to the exact one they have with a load of details assigned to it like purchase date, staff name, copy of invoice etc. From here I can tell if it's under warranty or not to know whether to get it returned as faulty or binned. Now for adding the stock, we buy in parts for the company and stock pile them until needed. So we could have 20 keyboards arrive which will all need a unique stockID, so to save entering the same item in 20 times I would like to type it once and list the quantity as 20 and hit add to stock to create 20 entries of the same keyboard with unique stockID's.

Would this affect what you've just mentioned or is that I just need to create an unbound form?

Thanks

 

[pr2-eugin]

Okay now it makes more sense. Yes, create a simple Unbound form, and this will be just a few lines of code !

 

[MCCDOM]

I tried your code pr2-eugin but got a "Run-time error '424': Object required". I am new to access so more than likely I have entered part of your code that should of been edited out but can't see what so could you help me please.

Private Sub btnAdd_Click()

    Dim iCtr As Integer
    For iCtr = 0 To Me.txtQty
        Current.Execute "INSERT INTO tblTest (txtItem) VALUES ('" & Me.txtItem & "')"
    Next
End Sub

 

[pr2-eugin]

Well I mentioned CurrentDB, not Current. Please edit it to CurrentDB.Execute and try again.

 

[MCCDOM]

Sorry my bad. Should of seen that. I have corrected that and now get "Run-time error '3127': The INSERT INTO statement contains the following unknown field name: 'txtItem'. Make sure you have typed the name correctly, and try the operation again.

 

[pr2-eugin]

Is txtItem the field name in your table tblTest?

 

[MCCDOM]

No it would be just 'Item'. I changed it to the following:

CurrentDb.Execute "INSERT INTO tblTest (Item) VALUES ('" & Me.txtItem & "')"

Now when I click add it doesn't ping up with any errors but instead doesn't add anything to the table. See attached screen shots.

 

[MCCDOM]

Never mind I have got it going. Thanks both for all of your help.