Requery on form not working (1 Viewer)

[sross81]

Hello,

I have have this code in the click event of a form that updates the database table with the current date when the button is pressed and it used to always then update a text box on the form with the date, but suddenly it just stopped working. I haven't changed anything. Everything I read says to use refresh and requery, but I am doing all of that and it still doesn't update. I have stepped through the code in VB and it does get through the code and I can run a query on the table on the server and I see that the table updates immediately. I can only see the updated date if I got out of the form and go back in now. Does anyone have any ideas?

Dim EncNbr As String
EncNbr = Me.EncounterNbr
With CodeContextObject

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE dbo_Providers SET dbo_Providers.DateEmailed = Now()" & _
"WHERE (((dbo_Providers.ProvTbleID)=[Forms]![frm_SendVarianceNotice]![ProvTbleID]))"
Me.DateEmailed.Requery
DoCmd.SetWarnings True

Me.Refresh
Me.Requery

DoCmd.SendObject acReport, "rpt_VariancesbyProvider_SendEmail", "PDFFormat(*.pdf)", .ProviderEmail, "", "", "#secure# Preliminary Variance Notification - Request Review Within 10 Days", "The attached quality measure did not comply with CMS guidelines and you have been assigned the variance" & " " & "For Enc#: " & EncNbr & "." & " " & "Please review and if you can demonstrate Core Measure CMS compliance respond to this notice within 10 days.", True, ""
End With

 

[April15Hater]

What is the name of your text box control?

Try this in the meantime:

Dim EncNbr As String
Option Explicit
Dim EncNbr As String
EncNbr = Me.EncounterNbr
With CodeContextObject
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE dbo_Providers SET dbo_Providers.DateEmailed = " & Now & _
        "WHERE (((dbo_Providers.ProvTbleID)=[Forms]![frm_SendVarianceNotice]![ProvTbleID]))"
    DoCmd.SetWarnings True
    Me.Refresh
    Me.Requery
    Me.DateEmailed.Requery
    DoCmd.SendObject acReport, "rpt_VariancesbyProvider_SendEmail", "PDFFormat(*.pdf)", .ProviderEmail, "", "", "#secure# Preliminary Variance Notification - Request Review Within 10 Days", "The attached quality measure did not comply with CMS guidelines and you have been assigned the variance" & " " & "For Enc#: " & EncNbr & "." & " " & "Please review and if you can demonstrate Core Measure CMS compliance respond to this notice within 10 days.", True, ""
End With

 

[sross81]

The text field is DateEmailed. I tried moving the refresh and requery up and it didn't help. I also do have the option explicit but it just is not part of this specific click event.

Dim EncNbr As String
EncNbr = Me.EncounterNbr
With CodeContextObject
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE dbo_Providers SET dbo_Providers.DateEmailed = Now()" & _
"WHERE (((dbo_Providers.ProvTbleID)=[Forms]![frm_SendVarianceNotice]![ProvTbleID]))"
Me.Refresh
Me.Requery
Me.DateEmailed.Requery
DoCmd.SetWarnings True


DoCmd.SendObject acReport, "rpt_VariancesbyProvider_SendEmail", "PDFFormat(*.pdf)", .ProviderEmail, "", "", "#secure# Preliminary Variance Notification - Request Review Within 10 Days", "The attached quality measure did not comply with CMS guidelines and you have been assigned the variance" & " " & "For Enc#: " & EncNbr & "." & " " & "Please review and if you can demonstrate Core Measure CMS compliance respond to this notice within 10 days.", True, ""
End With

 

[sross81]

I instead just created an unbound text box on the form that I set the date equal to now so that the user can see it update. I already confirmed that the date gets updated in the actual underlying table when they push the button. The form just doesn't want to refresh form some reason. It is tied to a recordset that links to a sql stored procedure. I don't know if that has anything to do with it. I assumed it doens't because it was working before.

 

[April15Hater]

Sorry for the late reply, for some reason this was sent to my junk folder

The Option Explicit shouldn't be within the procedure (between Sub/End Sub or Function/End Function) but inside the Declarations section (Very Top) of the module. I generally make if my first line on every module.

What other events do you have running? Might help to post your entire code module.

Thanks,

Joe

 

[sross81]

I did put the Option Explicit in the top of my module .

Actually I ended up changing this again. I forgot that this form was bound to a recordset that was pulled by a stored procedure. I instead just had the pass through query execute again and reset the record set to the new qdf and it solved the problem instantly.

Thanks!