Solved SubForm not refreshing after running Update Query using command button (1 Viewer)

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
I created 2 Update queries as below and added it to a subform with command button using a wizard. The table was updating but the sub form was not refreshing. I read there is bug in Access on sub form Requery. I tried several methods as suggested in forums , some only are listed below


UPDATE tblCustRFQDtls LEFT JOIN tblCustRFQHdr ON tblCustRFQDtls.RFQHdrID = tblCustRFQHdr.RFQID SET tblCustRFQDtls.Flag = Yes

WHERE (((tblCustRFQHdr.ConsigneeCode)=[Forms]![FrmCustRFQHdr]![ConsigneeCode]));


UPDATE tblCustRFQDtls LEFT JOIN tblCustRFQHdr ON tblCustRFQDtls.RFQHdrID = tblCustRFQHdr.RFQID SET tblCustRFQDtls.Flag = No

WHERE (((tblCustRFQHdr.ConsigneeCode)=[Forms]![FrmCustRFQHdr]![ConsigneeCode]));


I then converted the macro to vba and added the below to both events of command buttons

Me.dirty = false
Me.recordset.requery


It did not work. I replaced Me.recordset.requery with me.refresh. Did not help

I tried Forms!frmCustRFQHdr.frmRFQDtls.Requery. Again not refreshing

The table is updating with the Update Queries. If I navigate to a new record, there is no issue

Please help!
 

LanaR

Member
Local time
Tomorrow, 02:46
Joined
May 20, 2021
Messages
113
Sorry I can't post a direct link yet, but use your preferred search engine and search on "Refer to Form and Subform properties and controls" look for a result in access dot mvps dot org should be near the top of the results. This site has the correct syntax for referring to forms/subform from numerous relative locations
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:46
Joined
May 7, 2009
Messages
19,169
there is no such thing as Yes or No.
if your field is Yes/No field, you use True or False or -1 or 0 when updating the field.

you have Identical query one that set it to True and Later set it to False.
 

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
there is no such thing as Yes or No.
if your field is Yes/No field, you use True or False or -1 or 0 when updating the field.

you have Identical query one that set it to True and Later set it to False.
Queries corrected - True and False. Not requerying


UPDATE tblCustRFQDtls LEFT JOIN tblCustRFQHdr ON tblCustRFQDtls.RFQHdrID = tblCustRFQHdr.RFQID SET tblCustRFQDtls.Flag = False
WHERE (((tblCustRFQHdr.ConsigneeCode)=[Forms]![FrmCustRFQHdr]![ConsigneeCode]));


There are 2 command buttons for these 2 queries

1622268441129.png


The command button code is as below
Code:
Private Sub FlagAll_Click()
On Error GoTo FlagAll_Click_Err

    DoCmd.OpenQuery "qryCustRFQItemsFlgYes", acViewNormal, acEdit


FlagAll_Click_Exit:
    Exit Sub

FlagAll_Click_Err:
    MsgBox Error$
    Resume FlagAll_Click_Exit

Me.Dirty = False
Forms!frmCustRFQHdr.frmRFQDtls.Requery
End Sub
 

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
A minor upgrade you might like to consider would me to convert the two buttons into one.

See my video here on how to do this:-

Thanks. That is neat. I need to however resolve the requery/refresh issue first. Any advise?
 

LanaR

Member
Local time
Tomorrow, 02:46
Joined
May 20, 2021
Messages
113
Code:
UPDATE tblCustRFQDtls LEFT JOIN tblCustRFQHdr ON tblCustRFQDtls.RFQHdrID = tblCustRFQHdr.RFQID SET tblCustRFQDtls.Flag = "False"

WHERE (((tblCustRFQHdr.ConsigneeCode)=[Forms]![FrmCustRFQHdr]![ConsigneeCode]));

You need to wrap the True/false in double quotes or simply use -1 instead of True and 0 instead of false
 

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
Code:
UPDATE tblCustRFQDtls LEFT JOIN tblCustRFQHdr ON tblCustRFQDtls.RFQHdrID = tblCustRFQHdr.RFQID SET tblCustRFQDtls.Flag = "False"

WHERE (((tblCustRFQHdr.ConsigneeCode)=[Forms]![FrmCustRFQHdr]![ConsigneeCode]));

You need to wrap the True/false in double quotes or simply use -1 instead of True and 0 instead of false
The tables were being updated earlier and even after changing to 0 and -1. The issue of Refresh/Requery remains
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:46
Joined
Sep 21, 2011
Messages
14,044
Code:
Forms!frmCustRFQHdr.frmRFQDtls.Requery

If frmRFQDtls is your subform, surely you need the .Form element to be able to requery?
 

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
Sorry I can't post a direct link yet, but use your preferred search engine and search on "Refer to Form and Subform properties and controls" look for a result in access dot mvps dot org should be near the top of the results. This site has the correct syntax for referring to forms/subform from numerous relative locations
I saw that. It says use me.recordset in the sub form which is what I did earlier. No result
 

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
Code:
Forms!frmCustRFQHdr.frmRFQDtls.Requery

If frmRFQDtls is your subform, surely you need the .Form element to be able to requery?
I tried me.recorset.requery
Forms!frmCustRFQHdr.frmRFQDtls.Requery
Forms!frmCustRFQHdr!frmRFQDtls.Form.Requery

No improvement!
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:46
Joined
Sep 21, 2011
Messages
14,044
Best upload your db, else we will be here forever.😔
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:46
Joined
Sep 21, 2011
Messages
14,044
I only have 2007, but I am sure someone will sort you out if I cannot open it..😀
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:46
Joined
Sep 21, 2011
Messages
14,044
As you are here so much, it might be worth having a set of sanitised data all the time?
 

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
As you are here so much, it might be worth having a set of sanitised data all the time?
DB enclosed. The problems are so varied and the development keeps happening, so creating sanitised data is not feasible

Anyway DB enclosed. Look forward to some help. The problem is in the form frmCustRFQHdr which has a sub form containing the 2 command buttons in question
 

Attachments

  • PP_New_30th May - FlgIssue.zip
    381.7 KB · Views: 491

Gasman

Enthusiastic Amateur
Local time
Today, 15:46
Joined
Sep 21, 2011
Messages
14,044
Is it the Flag buttons? with code like this?

Edit: I can see it is after going back through the threads, and I must apologise, I should have seen this earlier. :(

You code is going to fall through to FlagAll_Click_Exit:, so is NEVER going to change dirty or requery?
You need it BEFORE you exit the sub.

Code:
Private Sub FlagAll_Click()
On Error GoTo FlagAll_Click_Err

    DoCmd.OpenQuery "qryCustRFQItemsFlgYes", acViewNormal, acEdit


FlagAll_Click_Exit:
    Exit Sub

FlagAll_Click_Err:
    MsgBox Error$
    Resume FlagAll_Click_Exit

Me.Dirty = False
Me.Recordset.Requery

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:46
Joined
Sep 21, 2011
Messages
14,044
This is also not going to work?
Code:
Private Sub Form_GotFocus()
    Me.CustRFQDtls.Requery
    Me.CustRFQDtls.Form.Repaint

End Sub

You use the Form element for the Repaint, but not the Requery?, plus the name of the form is frmCustRFQDtls ?
I'd also use the Recordset.Requery as that does not move the record pointer?
 

AnilBagga

Member
Local time
Today, 21:16
Joined
Apr 9, 2020
Messages
223
Is it the Flag buttons? with code like this?

Edit: I can see it is after going back through the threads, and I must apologise, I should have seen this earlier. :(

You code is going to fall through to FlagAll_Click_Exit:, so is NEVER going to change dirty or requery?
You need it BEFORE you exit the sub.

Code:
Private Sub FlagAll_Click()
On Error GoTo FlagAll_Click_Err

    DoCmd.OpenQuery "qryCustRFQItemsFlgYes", acViewNormal, acEdit


FlagAll_Click_Exit:
    Exit Sub

FlagAll_Click_Err:
    MsgBox Error$
    Resume FlagAll_Click_Exit

Me.Dirty = False
Me.Recordset.Requery

End Sub
This worked. Thanks Gasman. It requeries now

I need to get rid of the error messages reg updation! How?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:46
Joined
Sep 21, 2011
Messages
14,044
This worked. Thanks Gasman. It requeries now

I need to get rid of the error messages reg updation! How?
Search here, I am sure that has been answered before. I *think* you use Execute and not Openquery ? also set warnings off and on. Google and experiment.
 

Users who are viewing this thread

Top Bottom