Update table

[danian]

I have a form that is bound to a table and also a subform that is also bound to a table. On the Subform I have a Plus and Minus buttons that sort of work at as a tally chart. Every time you click the plus button it adds 1 to the quantity textbox and vice versa for the subtract button. What currently happens is when the Plus button is clicked the follwing runs:

Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
RS.Open "SELECT * from tblQuantity", Application.CurrentProject.Connection, adOpenStatic, adLockOptimistic
RS.AddNew
RS!Barcode = cmbProductName.Column(2)
RS!EXOrderID = Forms![tblorders].[ID]
RS!TransID = TransID
RS!TransDate = Date
RS!QuantitySold = 1
RS!QuantityBought = 0
RS.Update
RS.Close
Set RS = Nothing

What this does is create a record on the tblQuantity table, but it creates a new record each time the plus button is click.

So i.e.

I click the plus button 10 times and it creates 10 records

What I require is, it creates 1 record and just puts 10 in the QuantitySold column in the tblQuantity table. This will make the table easier to read.

I have attached the Database

Thanks
Danian

 

[ScottGem]

You need to separate these functions. One function to add a record, another to increment the value. The increment is simple:

Me!controlname = Me!ccontrolname + 1

 

[danian]

Scott,

It does do this. It adds the record, but i cant seem to get it to update. I get it to add a new record everytime to + buuron is clicked. what i need is a piece of code that runs on the after update. It would be something like:

After update
overwrite tblquantity.QuantitySold with txtQuantity Where forms!tbltransactions Subform.TransID = tblQuantity.TransID

Does this make sense?

 

[ScottGem]

No it doesn't make sense. I'm currently finishing up a similar functionality in an app for one of the deaprtments I support. The app is a timesheet app. Time In/Out is entered by a supervisor on a weekly basis. The way I set it up was with a main form that selected the employee and subform to enter the daily times. On the main form I have a button that adds a new record. This button takes the current record and duplicates it, incrementing the date. On the subform, I have +/- buttons on either side of each time control. Pressing these buttons increments/decrements the value in the control.

One point is that I have separated the functions. An add button to add a new record. And +/- buttons to adjust the value. For the +/- buttons I use a single line of code like I mentioned earlier.