Updating several Records at the same time.

[dinaayoub]

I am building a database to be used for inventory control. I need to make a form for quick insertion of stock (instead of inserting each quantity of a certain item of supplies received at a time, inserting several!), in which case i want there to be, for example, 10 combo boxes with the item part numbers to choose from, and then a box to type in the quantity for each one of those combo boxes, such that when the user presses save all the records are updated (by adding this quantity the user entered to the quantity on hand)

The problem i have is the following:

1- when i try to do this using a form that displays all records at the same time, the field in which the user enters the quantity he/she wants to add gets changed for all the records! such that the user cannot enter in one field a number and in another field anothe rnumber (everytime he enters a number all others r replaced with it!)

2- when i try to do this one by one, like to actually make 10 different combo boxes and 10 different variables, iit goes absolutely nuts and adds all the numbers i put in the different boxes to the combo box i last changed!!

Can anybody help me and tell me how to fix this problem, or what to do to implement this form and allow several records to be updated at the same time? (if the problem isnt clear please tell me)
would appreciate it a lot, thanks

 

[Mark Wild]

Hi,

Are you sure its not going to be quicker to input line by line and have a save button for each record? This should be very quick is you move the cursor bac kto the first box and reset the valaues to nothing. It will also make your life a lot easier.

THis is a possiblity if you get no better options (I've not tested it but logically seems sound, if unconventional. It assumes the combo boxes and text boxes will be unbound.

Put the code behind a button. Consider what will happen if the user only has say 5 lines to input as in this instance you would have have to amend the code

Dim db As Database
Dim rst As Recordset
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TblName", dbOpenDynaset)

i = 1

With rst
    Do Until 1 = 10 ' or how ever many lines your going to have on you form
    
        .AddNew
        !Product = Me.ComboBox & i ' name each cmb and txt box the same, followed by number
        !Quantity = Me.txtQuantity & i ' eg CmbProduct1, then the next line CmbProduct2 etc
        
        .Update
        i = i + 1
    
    Loop
End With
    
Set db=Nothing
Set rst=Nothing

End Sub

 

[dinaayoub]

Emmm... Pat Hartman, i dotn understand what you mean exactly, but i tried a continuous form, and it just doesnt work because the value of all the boxes gets changed whenever i change it for one record.
i will try that other solution and see
thank you both anyway

 

[Rich]

Your combos aren't working the way you want because they're unbound