Mass change table data in a subform with a button (Two like items)

[tuggleport]

I am working on a custom business program for a distributor. They deal with returns and varying discounts. My task is to create two buttons:



1. Click the button and ALL the line item quantities are multiplied by "-1"

I currently have an Event Procedure as such:
"Private Sub Command107_Click()
Me.qtyord = -1 * Me.qtyord
End Sub"

No matter what I've tried it will ONLY change the current line item. I've even tried manually selecting all the line items without success. Any suggestions?


2. Click the button and ALL Discount fields change to reflect the "Base Discount" which is displayed on the Invoice form but ultimately comes from and is stored in the Customer form/table. I have attempted two methods by putting the button IN the subform & IN the main form. Neither has worked toward my goal and the main form version didn't work AT ALL.

I currently have an Event Procedure as such:

Subform Version:
"Private Sub Command45_Click()
Me.Discount = Forms!Invoices.Discount
End Sub"

Main Form Version (current attempt):
"Private Sub Command45_Click()
Forms!Invoices.Discount = Me!InvoiceDetail.Discount
End Sub"

If I can get either of these discount buttons to work it would be great! I have been mulling it over for two weeks now and am starting to think a query might be better but VBA should do the job. Maybe I'm suffering from programmers block? Who knows.

Thanks!

[ketbdnetbp]

Although a bit old and clunky, you could try this (in the click event of a button), as it should accomplish both things at once.

Dim X1 As Integer
Dim X2 As Integer
Dim Y3 As Variant
Y3 =[Forms]![Invoices]![Discount]

Forms!MainFormName!SubformName.Form.RecordsetClone .MoveLast
X1 = Forms!MainFormName!SubformName.Form.RecordsetClone .RecordCount

DoCmd.GoToControl "SubformName"
DoCmd.GoToRecord acActiveDataObject, , acFirst
X2 = 1
[Forms]![MainFormName]![SubformName].Form![qtyorder] = ([Forms]![MainFormName]![SubformName].Form![qtyorder]*-1)

[Forms]![MainFormName]![SubformName].Form![Discount] = Y3


Do Until x2 = x1
DoCmd.GoToRecord acActiveDataObject, , acNext
Debug.Print X2
X2 = X2+1
[Forms]![MainFormName]![SubformName].Form![qtyorder] = ([Forms]![MainFormName]![SubformName].Form![qtyorder]*-1)

[Forms]![MainFormName]![SubformName].Form![Discount] = Y3
Loop

'Change to reflect the names of your respective forms and add in your own error handling.

Hope this helps,

Jim

[tuggleport]

Jim,

Thanks for the help! I do have two seperate buttons but with minimal adjustment I got the Discount button working fine but the "Make everything negative" button needs debugging.

My QtyOrd button is now changing ONLY the first line item listed whereas before it would only change the selected line item.

For the QtyOrd * -1 portion I extracted everything I thought was needed and I have this now:

"Dim X1 As Integer
Dim X2 As Integer

Forms!Invoices!InvoiceDetail.Form.RecordsetClone.M oveLast
X1 = Forms!Invoices!InvoiceDetail.Form.RecordsetClone.R ecordCount

DoCmd.GoToControl "invoicedetail"
DoCmd.GoToRecord acActiveDataObject, , acFirst
X2 = 1
[Forms]![Invoices]![InvoiceDetail].Form![qtyord] = ([Forms]![Invoices]![InvoiceDetail].Form![qtyord] * -1)

Do Until X2 = X1
DoCmd.GoToRecord acActiveDataObject, , acNext
Debug.Print X2
X2 = X2 + 1

Loop"

[ketbdnetbp]

See below:

For the QtyOrd * -1 portion I extracted everything I thought was needed and I have this now:

Dim X1 As Integer
Dim X2 As Integer

Forms!Invoices!InvoiceDetail.Form.RecordsetClone.M oveLast
X1 = Forms!Invoices!InvoiceDetail.Form.RecordsetClone.R ecordCount

DoCmd.GoToControl "invoicedetail"
DoCmd.GoToRecord acActiveDataObject, , acFirst
X2 = 1
[Forms]![Invoices]![InvoiceDetail].Form![qtyord] = ([Forms]![Invoices]![InvoiceDetail].Form![qtyord] * -1)

Do Until X2 = X1
DoCmd.GoToRecord acActiveDataObject, , acNext
Debug.Print X2
X2 = X2 + 1

'Add:
[Forms]![Invoices]![InvoiceDetail].Form![qtyord] = ([Forms]![Invoices]![InvoiceDetail].Form![qtyord] * -1)

'into the loop to change all records.

Loop"

All the best,

Jim