posted
Don't know if we have any VBA programmers here, but it's worth a shot....
I want to build code into a button click event that will loop through all the records displayed in a given subform and set values to two fields. One field, the value is static. The other will have to pull the value from the main form.
Better explanation... I'm building a payment form for my auction Access database. There are two main tables involved with this: Payments and Sales. Payment details are handled on the main form with the Sales lines listed on the subform. I want a button that will update the Paid field to Yes and put the PaymentID into the PaymentID field of the Sales records. PaymentID is an autonumber field on the Payment record.
I can't figure out how to do this. Anyone have a better idea on how to do this than I do?
Posts: 5948 | Registered: Jun 2001
| IP: Logged |
posted
You don't actually need to use VBA to achieve this, you know. An Update query running on the Sales join would work just as well.
But I'm also confused as to why you need to put a PaymentID into each Sales record. Can each Payment record have MULTIPLE Payment IDs? If not, why aren't you actually joining the Sales records in the subform on the PaymentID of the Payment record in question? The only reason not to do this, as far as I can see, would be if you needed to track payment on each Sales item individually, but your desired script appears to do exactly the opposite.
Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
I join the Sales records on the subform to the Payment record via the BidderID.
The purpose of the database is for the annual charity auction at my son's old school. They take in donated items (Inventory) from various people in the community (Donors) for a number of months prior to the event. At the event, they have both silent and live auction items, as well as a raffle and dessert run. All this is tracked to the bidder via the Sales table. At the end, when the bidders come to check out, an invoice is generated, and last year that's as far as it went. I didn't build anything to track the money coming in. That's what this form is for. I added table Payments, which I want to allocate to particular Sales lines. What we found last year was that many of the bidders checked out early, thinking they were done, but would end up buying a few things from a silent auction table that was still live, or would get some more raffle tickets. So I need to track which payment belongs to which sales lines. For that, I just want to make sure that the sales line gets checked as paid and which payment it was paid from. The payment record tracks the total payment with the method of payment.
In the payment form, the user enters the bidderid and tabs over to the total payment amount. When the bidder id is entered, the unpaid sales lines for that bidder id appears in the subform. That part was easy. I want the user to have the option to checkmark particular lines for the entered payment, or click a button to select all lines for payment. When Apply is clicked, I want it to check to make sure that all the data is logically correct (that there isn't an unapplied balance), save the records, and clear the screen for the next entry.
Is that making better sense, or have I fubbed my design? I work on accounting systems, and this is a dumbed down version of what I work on. Set your sales records to paid, give them a link to the payment record for auditing. If I was really going for it, I'd add a paid amount to the sales line, which would give people the option of paying the for the same item using two different payment methods. That'll be for the next release, though.
Posts: 5948 | Registered: Jun 2001
| IP: Logged |
Blayne Bradley
unregistered
posted
darn I'm not at databases yet.
IP: Logged |
I believe subforms in Access are treated as collections. You should be able to hit the subform recordset (ADODB.Recordset) with MoveFirst and While Not EOF, and then pull out the [Sales subform].Form.Controls collection. Do a For Each on the Controls collection, and you should be able to loop through all the controls on the subform.
Posts: 37449 | Registered: May 1999
| IP: Logged |
Tom, that's really great, but I don't know VBA. I'm just now learning. So I don't know any of the syntax for that. I got as far as figuring out that it probably could be done with looping through the collection, and that meant declaring a recordset, but I'm definitely not figuring it out right because it doesn't work.
Here's the syntax I'm using right now, but it errors out with "Current Recordset does not support updating. This may be a limitation of the provider, or of the selecting locktype."
Plus, I'm thinking that my Open statement isn't right..I want the collection in the subform, not a general SQL statement. Which isn't in any of the examples I have.
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset rst.ActiveConnection = cnn
rst.Open ("SELECT Paid FROM Sales"), , adOpenStatic
Do Until rst.EOF rst!Paid = Yes rst.Update Loop
rst.Close
Set rst = Nothing Set cnn = Nothing
Posts: 5948 | Registered: Jun 2001
| IP: Logged |
posted
You're calling the records themselves; I think you want the subform controls, which are themselves a collection. This is untested code, and I'm working from my own poor recall of the terminology involved, but I think you want something closer to:
Dim rst As ADODB.Recordset Set rst = [Sales subform].Form.Recordset
If Not rst.BOF Then rst.MoveFirst EndIf While Not rst.EOF For Each lineitem In rst If rst!ApplyPaymentToThisLineItem rst!Paid = True rst!PaymentID = Payments!PaymentID EndIf Next lineitem rst.MoveNext Wend
======
This assumes that you have a checkbox control on the form called ApplyPaymentToThisLineItem, and you only want to mark as paid (and set PaymentID for) those items that have this control checked.
That said, I still think you could accomplish this more easily -- if less flexibly -- with an Update query triggered by an onChange on the checkboxes.
Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
Oh, I like that last idea the best. I think I *could* do that way easier. THank you Tom!!! I really appreciate your input.
I am thinking that just reading books isn't going to get me the understanding of VBA I want. I picked up Transact SQL so easily that I thought I could just read my way into decent understanding. But I understood database query language before I started studying the syntax of SQL. I'm 13 years from my last programming class, and almost no occasion to use it since. So this is coming a lot harder.
Thanks again for your help. I appreciate it.
Posts: 5948 | Registered: Jun 2001
| IP: Logged |
Printer-friendly view of this topic
