continuous subform keeps going back to first record

L

lala

Registered User.
Local time
Yesterday, 22:56
Joined
Mar 20, 2002
Messages
741
i apologize for the size of the db, i don't know what's making it so huge, i took out all i can.

this is driving me crazy, the subform keeps going to the first record no matter what i try. try updating Ttl amount, Rate amount, quantity, Rate quantity, and see what happens.

on after update of the 4 fields above i have a bunch code that runs and a bunch of fields that requery themself, but i don't understand how that makes the subform requery.

and even if it does, i tried, at the end of each afterUPdate event, to put a bookmark that makes it go bak to the record that i started on. and it seems like it does that and THEN requery and goes back up to first

can't figure this out

thank you
 

Attachments

With which form are you having problems?
 
subform all the way on the bottom, the 2nd nested.
 
this is driving me crazy, if you replace the AfterUPdate event on RAmount field with the below - it works. how do msgboxes make a difference?? because they provide delays??


Code: 
CurntRecrd = Me.ID

CurntField = "RAmount"


    If IsNull(Me.Percentage) And Me.RQuantityType = Me.BQuantityType Then
    Me.BAmount = Me.BQuantity * (Me.RAmount / Me.RQuantity)
    
    End If



DoCmd.RunCommand acCmdSaveRecord




Dim AmtPctOf As Long

If Me.ServiceItem = 77 Then 'Payment

    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!LCPaymentAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!LCPaymentAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=77 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    
ElseIf Me.ServiceItem = 78 Then 'Payment
    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!LCPaymentAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!LCPaymentAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=78 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    
ElseIf Me.ServiceItem = 64 Then 'LC orig
    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!LCFinalAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!LCFinalAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=64 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    
ElseIf Me.ServiceItem = 63 Then 'LC addl
    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!LCFinalAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!LCFinalAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=63 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    
ElseIf Me.ServiceItem = 68 Then 'LC Amd orig
    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!LCAmdAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!LCAmdAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=68 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    
ElseIf Me.ServiceItem = 147 Then 'LC Amd addl
    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!LCAmdAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!LCAmdAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=147 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    
ElseIf Me.ServiceItem = 48 Then 'Bridge Loan orig
    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!BridgeLoanAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!BridgeLoanAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=48 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    
ElseIf Me.ServiceItem = 117 Then 'Bridge Loan addl
    If Not IsNull(Forms!faddfile!FSubAddFILESERVICES!BridgeLoanAmount) Then
    AmtPctOf = Forms!faddfile!FSubAddFILESERVICES!BridgeLoanAmount
        DoCmd.RunSQL "UPDATE CLIENTFILESERVICEITEMS SET " & _
        "CLIENTFILESERVICEITEMS.BAmount = iif([RAmount]>([percentage]* " & AmtPctOf & ")*[bquantity],[ramount],([percentage]* " & AmtPctOf & ")*[bquantity]) " & _
        "WHERE CLIENTFILESERVICEITEMS.ServiceItem=117 AND CLIENTFILESERVICEITEMS.ClientFileServiceID=" & Forms!faddfile!FSubAddFILESERVICES!ID
    End If
    

End If


        'updates Amounts
    DoCmd.RunSQL "UPDATE CLIENTFILESERVICES SET CLIENTFILESERVICES.TtlChargesAmount = IIf(IsNull(DSum('BAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID])),0,DSum('BAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID])) " & _
    ", CLIENTFILESERVICES.TtlLiabilityAmount = IIf(IsNull(DSum('LAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID])),0,DSum('LAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID])) " & _
    "WHERE (((CLIENTFILESERVICES.TtlChargesAmount) Is Null OR (CLIENTFILESERVICES.TtlChargesAmount)<>IIf(IsNull(DSum('BAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID])),0,DSum('BAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID]))) " & _
    "AND ((CLIENTFILESERVICES.MarkTtlUnlocked)=0)) OR (((CLIENTFILESERVICES.TtlLiabilityAmount) Is Null OR (CLIENTFILESERVICES.TtlLiabilityAmount)<>IIf(IsNull(DSum('LAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID])),0,DSum('LAmount','CLIENTFILESERVICEITEMS','ClientFileServiceID=' & [CLIENTFILESERVICES].[ID]))) " & _
    "AND ((CLIENTFILESERVICES.MarkTtlUnlocked)=0));"


MsgBox "Where i am now before requery " & Me.ID


Forms!faddfile!FSubAddFILESERVICES!TtlOfBAmount.Requery
Forms!faddfile!FSubAddFILESERVICES!TtlOfLiability.Requery
Forms!faddfile!FSubAddFILESERVICES!TtlTtlOfBAmount.Requery
Forms!faddfile!FSubAddFILESERVICES!TtlTtlOfLiability.Requery



MsgBox "Where i am now after requery" & Me.ID




Dim rs As Recordset
Dim varBookmark As Variant
Set rs = Me.RecordsetClone
rs.FindFirst ("clientfileserviceitems.id = " & CurntRecrd)
'rs.FindFirst (Me.ID = CurntRecrd)



varBookmark = rs.Bookmark
Me.Bookmark = varBookmark

MsgBox "Where i am now " & Me.ID

'MsgBox "Where i started " & CurntRecrd
'Me(CurntField).SetFocus
'MsgBox "Where i am now " & Me.ID
 
sorry, the last 4 lines to that code should be

Code: 
MsgBox "Where i am now " & Me.ID

MsgBox "Where i started " & CurntRecrd
'Me(CurntField).SetFocus
MsgBox "Where i am now " & Me.ID


how does this make a difference???
 
forget it, sometimes it works, sometimes it doesn't. i can't solve this
 
FAddFILE, the one it opened on
 
So it is the FSubAddFILESERVICEITEMS SubForm that is giving you problems?
Frankly, with so many requeries in it I would be amaized if it didn't land back on the 1st record. I don't really understand your logic yet but why are you doing all of those requeries through the Forms collection?
 
i'm not defending my code, there's so much i don't like in this database, but i can't find better soluitions.
how else can i requery though? i'm requerying because there's no other way for me to recalc those numbers. those can't be calculated totals, they have to be fields because in some cases it's not a sum but a typed number, so they must have the ability to type in there sometimes.

so how else can i requery the form?
 
I would look into doing any calculations in the Query behind the form. Then no requery is necessary.
 
I am not asking you to defend your code. I'm sorry if it came out that way. I am simply trying to understand it.
 
RuralGuy said:
I would look into doing any calculations in the Query behind the form. Then no requery is necessary.
Click to expand...

really? i haven't thought of that, it would requery on it's own? the thing it thought, even if i take out the requery lines and just leave the SaveRecord part, it still doesnt work
 
RuralGuy said:
I am not asking you to defend your code. I'm sorry if it came out that way. I am simply trying to understand it.
Click to expand...

maybe it wasn't even you)))))))) i don't like a lot of it so i was expecting criticism and i read something you didn't write


i will try putting calculations in the query behind the form. but can you try taking out the requery lines and see what happens?
 
this is the line that makes it go to first record.

DoCmd.RunCommand acCmdSaveRecord


as soon as i comment it - all is well. why?
 
oh, i have this))))) my favorite thing, whoever wrote this is amazing in the way they compiled it and i go to it when things don't work but somehow it works like this as well

forms!form!subform1!subform2!fff
 
I'm aware of that anomaly and have no explaination. It is still good practice to follow the syntax enumerated in that link.
 
you're right, will make it a habit
 
You must log in or register to reply here.

Similar threads

D
Subform requery is starting a new record
Replies
4
Views
374
dullster
D
D
After update
Replies
17
Views
554
Pat Hartman
P
T
Solved How to make vba check all records in continuous form not only top one record
Replies
14
Views
425
TMK
T
D
Me.Requery or Me.Recalc
Replies
18
Views
443
Josef P.
J
C
Solved No Current Record on Access Close
Replies
11
Views
657
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom