referring to first record =no

[Ken Snell [MVP]]

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

 

[Ken Snell [MVP]]

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

 

[Angi]

Thank you, Ken!! I've run out of tests and errors!!! Works like a
charm, of course! Thanks so much!!!

ang

 

[Ken Snell [MVP]]

You're welcome. I shoulda got it right the first time......

 

[Angi]

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

 

[Ken Snell [MVP]]

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?

 

[Angi]

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.

 

[Ken Snell [MVP]]

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

 

[Angi]

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???

 

[Ken Snell [MVP]]

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.