 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / April 2005referring to first record =no
Considering some of the more advanced coding I've done, I should be able to know how to do this but I don't. I have a form that allocates payments to invoices. I need to find the first record where Paid=No (yes/no field) and then do something with if...then statements and then go to the next record. I don't know how to refer to the recordset I need. It's the invoicemain table (PK InvoiceID, FK InvoiceDetailID) with the InvoiceDetail table giving me the total for the invoice (DSum of ExtPrice...got that..works fine). Can someone please tell me the correct syntax for referring to a recordset? TIA! Are you opening a recordset that is based on those tables? or is the form based on a query that contains both of those tables? Please provide some more details about the context of what you want to do in code. In general, if you have a DAO.Recordset named rst, you can do something like this: rst.MoveFirst rst.FindFirst "Paid=False" If rst.NoMatch = False Then ' code goes here because a match was found End If  SignatureKen Snell <MS ACCESS MVP> > Considering some of the more advanced coding I've done, I should be > able to know how to do this but I don't. [quoted text clipped - 9 lines] > > TIA! Ken, Just the man I was hoping would reply!! I'm actually basing this on an answer by you to a previous post (see below). I've done everything you said and I understand the logic. Now I'm trying to complete steps 3 and 5. Also, someone else recommended using DAO.recordset for another issue and it wasn't recognized. I'm using Acc 2002 with 2000 format. I'm assuming you mean Dim rst as DAO.recordset My payment form is based on the payments table. There's a subform to show outstanding invoices for that customer that is based on the invoicemain and invoicedetails tables (view only). Thanks for your help! Your answer to a post about allocating payments on 12/31/03 Generically, here is what you do in the code: 1. store the total payment amount in a variable that tracks the amount remaining to be distributed. 2. open a recordset based on the invoice table, selecting records for that customer that are not yet paid (how you determine this depends upon your table's structure). 3. start with the first record in the recordset. 4. check if the amount due for the invoice is less than, equal to, or greater than the amount remaining to be distributed. 3.1. if the amount due is less than the amount remaining to be distributed, mark that invoice as paid in full, and subtract from the amount remaining to be distributed the amount due for this invoice. 3.2. if the amount due is equal to the amount remaining to be distributed, mark that invoice as paid in full and end the process. 3.3 if the amount due is more than the amount remaining to be distributed, decide how to record a partial payment, store that info into the table's record, and end the process. 5. if step 4.1 was followed above, go to the next record in the recordset and repeat step 4. 6. close the recordset Oh oh -- < g > -- my past comes back to me! I'm glad you posted the previous information... quite honestly, I don't even recall that exchange (31 December 2003, eh?). Yes, by DAO.Recordset, I mean Dim rst as DAO.Recordset In ACCESS 2002 (and 2000), the DAO library is not part of the default references. You will have to add that library to your database reference list (from Visual Basic Editor, Tools | References ). My reply about the .MoveFirst, etc. should now be workable here for you, if you've opened a recordset that has the desired records in it. Post back the exact code that you have so far and where you're needing additional information/steps and let's see if we can get you a working solution. SignatureKen Snell <MS ACCESS MVP> > Ken, > Just the man I was hoping would reply!! I'm actually basing this on an [quoted text clipped - 37 lines] > and repeat step 4. > 6. close the recordset Please forgive me...but I'm confused. I'm putting the code in the paymentamt_afterupdate event on my payment form. Isn't the recordset then going to be the Payments table (recordsource for pmt form)?? Let's start at the beginning. What is the recordsource query for the form? Are you wanting to modify the records that are already showing in the form? Is a subform involved? What is the recordsource query for the subform? Post the code that you have for the paymentamt_afterupdate event procedure. SignatureKen Snell <MS ACCESS MVP> > Please forgive me...but I'm confused. I'm putting the code in the > paymentamt_afterupdate event on my payment form. Isn't the recordset > then going to be the Payments table (recordsource for pmt form)?? I'll answer your questions first. The recordsource for the PmtForm is the Payments table (no query). The records I need to modify are in the InvoiceMain table. I do have a subform, PmtsSubform, with a recordsource of : SELECT InvoiceMain.InvoiceID, InvoiceMain.OrderDate, InvoiceMain.AmtRemaining, InvoiceMain.Paid, DSum("[ExtPrice]","invoicedetails","Invoiceid =" & invoicemain.InvoiceID)+DSum("[freightamt]","invoicemain","Invoiceid =" & invoicemain.InvoiceID) AS Total, InvoiceMain.CoID FROM InvoiceMain INNER JOIN InvoiceDetails ON InvoiceMain.InvoiceID=InvoiceDetails.InvoiceID WHERE (((InvoiceMain.Paid)=No) And ((InvoiceMain.CoID)=Forms!Payments!coid)) ORDER BY InvoiceMain.OrderDate; Here's my code, so far: Private Sub PaymentAmount_AfterUpdate() Dim rst As DAO.Recordset Dim InvTot As Integer Dim PmtAmt As Integer Dim RemAmt As Integer Dim InvNo As Integer Dim sqlPaid As String Dim sqlPartial As String PmtAmt = Me.PaymentAmount rst.MoveFirst rst.FindFirst "Paid = false" If rst.NoMatch = False Then InvNo = InvoiceID sqlPaid = "Update invoicemain Set invoicemain.paid= yes WHERE (((invoicemain.invoiceid)=" & InvNo & ")" InvTot = DSum("[ExtPrice]", "invoicedetails", "Invoiceid =" & InvNo) If InvTot < PmtAmt Then CurrentDb.Execute sqlPaid RemAmt = PmtAmt - InvTot rst.FindNext ElseIf InvTot = PmtAmt Then CurrentDb.Execute sqlPaid RemAmt = PmtAmt - InvTot ElseIf InvTot > PmtAmt Then RemAmt = InvTot - PmtAmt sqlPartial = "Update invoicemain Set invoicemain.amtremaining= " & RemAmt & " WHERE (((invoicemain.invoiceid)=" & InvNo & ")" CurrentDb.Execute sqlPartial End If End If End Sub i just realized i need to get rid of the RemAmt variable and make them all PmtAmt...otherwise PmtAmt will stay the same and apply the whole payment to the next invoice. Before I forget, the expression that you're using to build the SQL string for sqlPaid has one too many left parentheses in it. Same comment is true for the sqlPartial build. Second, your code needs to assign rst to a recordset. You say that the subform is already based on a query using the InvoiceMain table. Can that subform be the source of the records that you want to "mark as paid"? Or does the subform contain a different set of records? How we would write the code will depend upon whether you can use the subform's recordset or must use a new recordset. Third, your code will not fully loop through the recordset (whatever it may be), because, after the rst.FindNext step, you don't go back through the "If..Then" block to mark the record as paid. If you can use the subform's recordset (which, it appears to me, based on its recordsource query, contains the desired records), let me suggest a modified set of code: Private Sub PaymentAmount_AfterUpdate() Dim rst As DAO.Recordset Dim InvTot As Integer Dim PmtAmt As Integer Dim RemAmt As Integer Dim InvNo As Integer Dim sqlPaid As String Dim sqlPartial As String PmtAmt = Me.PaymentAmount Set rst = Me.PmtsSubform.Form.RecordsetClone rst.MoveFirst Do While rst.EOF = False InvNo = Me.InvoiceID InvTot = DSum("[ExtPrice]", "invoicedetails", "Invoiceid =" & InvNo) If InvTot < PmtAmt Then rst.Edit rst!Paid = True rst.Update RemAmt = PmtAmt - InvTot rst.MoveNext ElseIf InvTot = PmtAmt Then rst.Edit rst!Paid = True rst.Update Exit Do ElseIf InvTot > PmtAmt Then rst.Edit rst!amtremaining = RemAmt rst.Update Exit Do End If End If Set rst = Nothing End Sub SignatureKen Snell <MS ACCESS MVP> > I'll answer your questions first. The recordsource for the PmtForm is > the Payments table (no query). The records I need to modify are in the [quoted text clipped - 48 lines] > > End Sub Ken, OK...here I go. I had to replace the me.pmtssubform with the whole syntax because it kept giving me an error about object not found..no error now. Commented the sql's for now since we're using the rst.update. Rather than write new code to accomodate the remamt, I just kept it pmtamt. Changed the last End If to Exit Do...don't know if that's right, but makes sense. I'm getting the error: Compile error: Do without Loop and it highlights the End Sub Thanks for all your help and time on this!! I really appreciate it! Here's my new code: Private Sub PaymentAmount_AfterUpdate() Dim rst As DAO.Recordset Dim InvTot As Integer Dim PmtAmt As Integer Dim InvNo As Integer Dim sqlPaid As String Dim sqlPartial As String 'sqlPaid = "Update invoicemain Set invoicemain.paid= yes WHERE ((invoicemain.invoiceid)=" & InvNo & ")" 'sqlPartial = "Update invoicemain Set invoicemain.amtremaining= " & RemAmt & " WHERE ((invoicemain.invoiceid)=" & InvNo & ")" PmtAmt = Me.PaymentAmount Set rst = Forms!payment!paymentsubform.Form.RecordsetClone rst.MoveFirst rst.FindFirst "Paid = false" Do While rst.EOF = False InvNo = Me.InvoiceID InvTot = DSum("[ExtPrice]", "invoicedetails", "Invoiceid =" & InvNo) If InvTot < PmtAmt Then rst.Edit rst!Paid = True rst.Update PmtAmt = PmtAmt - InvTot rst.MoveNext ElseIf InvTot = PmtAmt Then rst.Edit rst!Paid = True rst.Update Exit Do ElseIf InvTot > PmtAmt Then rst.Edit rst!AmtRemaining = InvTot - PmtAmt rst.Update Exit Do End If Exit Do Set rst = Nothing End Sub Aarrgh... the problems with copy/paste/edit..... Change the last lines: End If Set rst = Nothing End Sub to these lines: Loop Set rst = Nothing End Sub SignatureKen Snell <MS ACCESS MVP> > Ken, > OK...here I go. I had to replace the me.pmtssubform with the whole [quoted text clipped - 56 lines] > > End Sub Ken...I think I'm almost there! I had to put a break in the code and step through because it was doing nothing. No errors, but no updates either. Using the step into I get this error at the InvNo = Me.InvoiceID line Invalid use of null changed it to: InvNo = Forms!payments!PaymentSubform.InvoiceID and now error is: Run time error '438': Object doesn't support this property or method. There are two invoices for the test company and they ARE showing on the subform. What did I do wrong??? Change Forms!payments!PaymentSubform.InvoiceID to Forms!payments!PaymentSubform!InvoiceID SignatureKen Snell <MS ACCESS MVP> > Ken...I think I'm almost there! > [quoted text clipped - 14 lines] > There are two invoices for the test company and they ARE showing on the > subform. What did I do wrong??? Ken I think I love you!!! <g> Works beautifully now! I even figured out how to requery the rst all by my lonesome! Thank you so much for ALL your help!!!! Gratefully, angi Ok Ken...one more problem I didn't consider but just tried. If the payment amt is greater than the total of oustanding invoices I get the following error: Run time error '3021' No current record and it highlights the rst.movenext in the first IF statement. I understand that it's looking for a record to apply the remaining amt to but I don't know how to get around it. I thought the Do While rst.EOF = False would take care of it. Now what?? :) Hmmm.. that "love" didn't last too long, eh? < g >! Rrom what I'd posted, I wouldn't expect this error to be occurring, so let's see what your code is now. SignatureKen Snell <MS ACCESS MVP> > Ok Ken...one more problem I didn't consider but just tried. If the > payment amt is greater than the total of oustanding invoices I get the [quoted text clipped - 7 lines] > but I don't know how to get around it. I thought the Do While rst.EOF > = False would take care of it. Now what?? :) Ken, Naaahhh...I still "love" ya...just not as much! <g> This form is beautiful...as long as the customer doesn't overpay!! Here's the code: Private Sub PaymentAmount_AfterUpdate() Dim rst As DAO.Recordset Dim InvTot As Integer Dim PmtAmt As Integer Dim InvNo As Integer PmtAmt = Me.PaymentAmount Set rst = Forms!payments!PaymentSubform.Form.RecordsetClone rst.MoveFirst rst.FindFirst "Paid = false" Do While rst.EOF = False InvNo = Forms!payments!PaymentSubform!InvoiceID InvTot = Forms!payments!PaymentSubform!AmtRemaining If InvTot < PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update rst.Requery PmtAmt = PmtAmt - InvTot rst.MoveNext ElseIf InvTot = PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update rst.Requery Exit Do ElseIf InvTot > PmtAmt Then rst.Edit rst!AmtRemaining = InvTot - PmtAmt rst.Update rst.Requery Exit Do End If Loop Set rst = Nothing End Sub Ahhhhhhhh... you added a "requery" step to your code. That is why I didn't see why you were getting this error. When you requery the recordset, the record that you just marked as "Paid" is removed from the recordset. If that was the only record in the recordset, then yes, the error that you're seeing will indeed occur, because after the requery the recordset will be empty, and the MoveNext will indeed error. Is there a reason that you need to do the requery step at this point? The code that I provided does not require the requery in order to properly loop through the unpaid invoices. But, if you do want it so that it will update the display on the form/subform, then why not do it at the end of the code, after all the records have been marked "paid" that need to be so marked? Try this, for example: Private Sub PaymentAmount_AfterUpdate() Dim rst As DAO.Recordset Dim InvTot As Integer Dim PmtAmt As Integer Dim InvNo As Integer PmtAmt = Me.PaymentAmount Set rst = Forms!payments!PaymentSubform.Form.RecordsetClone rst.MoveFirst rst.FindFirst "Paid = false" Do While rst.EOF = False InvNo = Forms!payments!PaymentSubform!InvoiceID InvTot = Forms!payments!PaymentSubform!AmtRemaining If InvTot < PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update PmtAmt = PmtAmt - InvTot rst.MoveNext ElseIf InvTot = PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update rst.Requery Exit Do ElseIf InvTot > PmtAmt Then rst.Edit rst!AmtRemaining = InvTot - PmtAmt rst.Update rst.Requery Exit Do End If Loop rst.Requery Set rst = Nothing End Sub SignatureKen Snell <MS ACCESS MVP> > Ken, > Naaahhh...I still "love" ya...just not as much! <g> [quoted text clipped - 44 lines] > > End Sub Ken, So much for figuring out how to requery the recordset all by my lonesome, huh???!! It works now of course! Thanks so much!! BR, Ang Just when you thought I was going to leave you alone...I'm baaaack! One more test and one more error: Entering a payment when there are NO outstanding invoice. Don't laugh...this happens if they pay an invoice twice. Get same error: no current record but now it's highlighting the rst.movefirst I tried adding if rst.nomatch=false, but didn't work. Says it's not false and it's not because there are invoices, they're just all paid. I think I need to move the rst.findfirst Paid=false either in the line or above it, but I don't know where. Any advice?? Here's the new code (you know the dims): PmtAmt = Me.PaymentAmount Set rst = Forms!payments!PaymentSubform.Form.RecordsetClone If rst.NoMatch = False Then rst.MoveFirst rst.FindFirst "Paid = false" Do While rst.EOF = False InvNo = Forms!payments!PaymentSubform!InvoiceID InvTot = Forms!payments!PaymentSubform!AmtRemaining If InvTot < PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update PmtAmt = PmtAmt - InvTot rst.MoveNext ElseIf InvTot = PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update Exit Do ElseIf InvTot > PmtAmt Then rst.Edit rst!AmtRemaining = InvTot - PmtAmt rst.Update Exit Do End If Loop End If rst.Requery Set rst = Nothing End Sub Let's change the If statement (I should have noted this earlier, sorry): If rst.RecordCount > 0 Then rst.MoveFirst rst.FindFirst "Paid = false" Do While rst.EOF = False InvNo = Forms!payments!PaymentSubform!InvoiceID InvTot = Forms!payments!PaymentSubform!AmtRemaining If InvTot < PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update PmtAmt = PmtAmt - InvTot rst.MoveNext ElseIf InvTot = PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update Exit Do ElseIf InvTot > PmtAmt Then rst.Edit rst!AmtRemaining = InvTot - PmtAmt rst.Update Exit Do End If Loop End If rst.Requery Set rst = Nothing SignatureKen Snell <MS ACCESS MVP> > Just when you thought I was going to leave you alone...I'm baaaack! > [quoted text clipped - 48 lines] > > End Sub And, let's move the requery inside the If..Then block: If rst.RecordCount > 0 Then rst.MoveFirst rst.FindFirst "Paid = false" Do While rst.EOF = False InvNo = Forms!payments!PaymentSubform!InvoiceID InvTot = Forms!payments!PaymentSubform!AmtRemaining If InvTot < PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update PmtAmt = PmtAmt - InvTot rst.MoveNext ElseIf InvTot = PmtAmt Then rst.Edit rst!Paid = True rst!AmtRemaining = "0" rst.Update Exit Do ElseIf InvTot > PmtAmt Then rst.Edit rst!AmtRemaining = InvTot - PmtAmt rst.Update Exit Do End If Loop rst.Requery End If Set rst = Nothing SignatureKen Snell <MS ACCESS MVP> > Let's change the If statement (I should have noted this earlier, sorry): > [quoted text clipped - 80 lines] >> >> End Sub Thank you, Ken!! I've run out of tests and errors!!! :) Works like a charm, of course! Thanks so much!!! ang You're welcome. I shoulda got it right the first time...... SignatureKen Snell <MS ACCESS MVP> > Thank you, Ken!! I've run out of tests and errors!!! :) Works like a > charm, of course! Thanks so much!!! > > ang Ken, One more question about this... How do I set the recordset to the InvoiceMain table if I decided to get rid of the subform, the current recordset for this code?? Or would it be easier to keep the subform and hide it? Or should I add the fields I need to the record source for the main form as hidden fields? Thanks, Ang I'm not seeing the context of your question, so post more details about what you want to do. Are you talking about eliminating the need for the subform completely? Or just not wanting it to be used for some situations? or something else? SignatureKen Snell <MS ACCESS MVP> > Ken, > One more question about this... [quoted text clipped - 6 lines] > Thanks, > Ang Ken, I need to get rid of the subform, but that's the recordset used for the code you helped me with. So I was wondering how I could change Set rst = Forms!payments!PaymentSubform.Form.RecordsetClone which uses the Invoicemain table to Set rst = Invoicemain table Sorry I didn't explain that better. I'm just frustrated about constant changes from the users. Changes from users..... hmmm, that must be a unique problem, as I can't recall such "requests" from my users...... < g > at least not within the last five minutes, that is! If you just want to use the table directly, then you'll need to open a recordset based on the table: Dim dbs As DAO.Database Dim rst As DAO.Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Invoicemain", dbOpenDynaset) ' .. code goes here to do things rst.Close Set rst = Nothing dbs.Close Set dbs = Nothing SignatureKen Snell <MS ACCESS MVP> > Ken, > I need to get rid of the subform, but that's the recordset used for the [quoted text clipped - 6 lines] > Sorry I didn't explain that better. I'm just frustrated about constant > changes from the users. Ken, Just some background. There is now an option group with Deposit (1) or Payment(2). I'm using Select Case to do the code. Now..I got Case 1 to work. Now I'm having a problem with Case 2. Using the same code as before, I need to change the rst.FindFirst "Paid = false" to (but it's not working) rst.FindFirst "Paid = false" AND "coid = forms!payments!coid" AND "deprec = true" I've tried rst.FindFirst "Paid = false & coid = forms!payments!coid & deprec = true" since it's a string, but I get that error that Jet doesn't recognize forms!payments!coid. After it's done with those records, I need it to hit the deprec=false records. I assume that's just another Do While Loop (right?) Also, I took out the rst.requery. Having fun yet??? :) Change: rst.FindFirst "Paid = false" AND "coid = forms!payments!coid" AND "deprec = true" to this: rst.FindFirst "Paid = false AND coid = " & forms!payments!coid & " AND deprec = true" The above assumes that coid is a numeric field. If it's a text field: rst.FindFirst "Paid = false AND coid = '" & forms!payments!coid & "' AND deprec = true" Regarding the "deprec = false" steps, yes, that probably would be another Loop. SignatureKen Snell <MS ACCESS MVP> > Ken, > Just some background. There is now an option group with Deposit (1) or [quoted text clipped - 15 lines] > records. I assume that's just another Do While Loop (right?) Also, I > took out the rst.requery. Having fun yet??? :) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.