Why does MsAccess Record entry only Update (and show in table) after form close and reopen?-CodePudding

Some Background:

I have a database that acts as a ledger (keeps tabs on current payments).

1:Payments for each customer are stored in one table (PaymentTbl),

2:I have another table (TermAgreementTbl) that holds information on the agreed terms of the service

3: My last table (PdUpToTbl) takes the payment information as well as term agreements information from the two other tables and calculates/displays the information in a clearer manner. One of the ways it will do this, is by deleting the last record in PdUpToTbl, and replacing it, or by just adding a new record (case dependent).

Now MY Issue:

I have a form for my TermAgreementTbl that has a subform showing the relevant PdUpToTbl. A button opens a pop-up form to enter a new payment and update the related PdUpTotbl.

Everything in the back end is functional, however after I enter a new payment (and save and close the pop-up payment form), NO new record is shown in my PdUpToTbl Subform. Instead it shows something like (some irrelevant info redacted):

For the new record to display properly, I have to close the entire form, and reopen it. There has got to be a way to get around this through vba with some code, right?

Thank you for taking the time.

Edit 1:

By the way, after I perform A LOT of vba code, I use this to enter my record:


With pdUpToRS
            .AddNew
                ![DatePaid] = NewRecordSet.Fields("DatePaid").Value
                ![Amount] = Amount
                ![AppliedAmount] = AppliedAmount
                ![OnAcct] = OnAcct
                ![AllPdUpTo] = AllPdUpTo
                ![RemainBalDue] = RemainBalDue
                ![PdUpToString] = PdUpToString
                ![PaymentType] = NewRecordSet.Fields("PaymentType").Value
                ![PaymentNumber] = PaymentNumber
                ![ID] = NewRecordSet.Fields("ID").Value
                ![PmntTblID] = PmntTblID
                ![BdCk] = BdCk
                ![Late] = Lte
                ![ApplyDiscount] = ApplyDiscount
                ![ForgetUnderage] = ForgetUnder
                ![ForgetOverage] = ForgetOver
                ![Note] = Note
            .Update
        End With

Update using requery

I have tried to Requery using:

Forms![MainForm]![Subform].Requery

But it gives me the error:

2118 - - - You must save the current field before you run the Requery action.

And if I add the save line:

DoCmd.RunCommand acCmdSaveRecord
Forms![MainForm]![Subform].Requery

I get the resulting error:

2046 - - - The command or action 'SaveRecord' isn't available now.

CodePudding user response：

Ok, the docmd "menu" options to save a record?

They OFTEN run on the form that has the current focus - so often, then the form you want to save is not the one you expected.

I suggest you replace this:

DoCmd.RunCommand acCmdSaveRecord

with

if me.Dirty then me.Dirty = false

Now, above above "me" is is the current form WHERE that code is running, not some form that might happen to have the focus.

Now, as for a form "requery" (to refresh without close then re-open)?

Again, assuming the above just did the save of the data, then to force a re-load of the current form (again, the form in which the code is running), then:

me.Requery

In fact, if you did not have multiple sub-forms, then a me.refresh would probably work (and a me.Refresh will also save the current record).

So, while the if me.dirty = true then me.dirty = false is about the best way to save the current reocrd in the current form where the code is running?

It is a question of where your code is running, and often when the code is running.

In place of the me.dirty = false, you can also do this, but it often will cause a lot more flicker and refreshing then you want.

But, the shortest code to ensure a save of the forms data, and then requery (same as form close then open), would thus be this:

Me.Refresh
me.Requery

However, often issues can arise if you have some dialog form open - so perhaps a closer look at how your code is updating is often imporant.

But, a me.Requery will re-load everything, but you of course want to ensure you force record saves of the data first.

CodePudding user response：

I think the Requery function will serve you well.

https://docs.microsoft.com/en-us/office/vba/api/access.subform.requery