Solved SubForm not refreshing after running Update Query using command button

[AnilBagga]

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]

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]

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]

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

The command button code is as below

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

 

[Uncle Gizmo]

There are 2 command buttons

A minor upgrade you might like to consider would be to convert the two buttons into one.

See my video here on how to do this:-

 

[AnilBagga]

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]

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]

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]

Forms!frmCustRFQHdr.frmRFQDtls.Requery

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

 

[AnilBagga]

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]

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]

Best upload your db, else we will be here forever.

 

[AnilBagga]

Best upload your db, else we will be here forever.

Will sanitise, with relevant tables only and share

 

[Gasman]

I only have 2007, but I am sure someone will sort you out if I cannot open it..

 

[Gasman]

As you are here so much, it might be worth having a set of sanitised data all the time?

 

[AnilBagga]

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

 

[Gasman]

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.

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]

This is also not going to work?

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]

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.

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]

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.