VBA to Refresh/Requery a Form

[justair07]

Hello,



I have a form that I need to requery/refresh from the press of a cmdbutton on a different form.

Here is what I tried but it's not working

Private Sub cmdSalesComp_Click()

DoCmd.Requery "frmPDMonitor"

End Sub

Thank you,

[ItsMe]

should work, maybe add acform

DoCmd.Requery acform, "frmPDMonitor"

[justair07]

Getting Compile error: "wrong number of arguments or invalid property assignment"

[justair07]

Does the form have to be closed for it to work? Because that would defeat the purpose. Basically this will replace the refresh button once I'm viewing the form. I want the form to refresh from a different form entirely. Does that make sense?

[ItsMe]

Yah that is not working. The form needs to be open. Try

Forms!frmPDMonitor.SetFocus
DoCmd.Requery

[justair07]

Nope, not working either. It seems like this should be so easy.

[ItsMe]

I have never tried to requery a form from another form before. I tested the code in post #6 and it did not cause an exception so I will assume it is requerying the table.

What is happening that causes you to believe the code in post #6 is not working? Perhaps saving the recordset in the original form will benefit the requery.

If Me.Dirty Then Me.Dirty = False
Forms!frmTestDateNull.SetFocus
DoCmd.Requery

[justair07]

I may be looking at this wrong. Let me explain from the beginning and maybe you can tell me.

I have a main form that the user will enter data. Once they hit a submit button, it saves the record and I was hoping it would also refresh the form in which is linked to a query. the form that is linked to a query will be displayed on a monitor 24/7. It will display pending orders. So I was hoping as new orders are placed, the query would display them without needing to be closed and then reopened.

[ItsMe]

Every workstation that opens a form will have their own instance of the form open on their local machine. Split DB's with FE and back end files vs. All in One files on a server do not change this. The workstation's OS uses its own RunTime to work with the database file.

The requery has to happen on the workstation that has the form open. Perhaps a dedicated front end file on a dedicated workstation for the monitor displaying the form. The front end file could run a timer and the timer could determine requery.

[justair07]

Every workstation that opens a form will have their own instance of the form open on their local machine. Split DB's with FE and back end files vs. All in One files on a server do not change this. The workstation's OS uses its own RunTime to work with the database file.

The requery has to happen on the workstation that has the form open. Perhaps a dedicated front end file on a dedicated workstation for the monitor displaying the form. The front end file could run a timer and the timer could determine requery.

Yes, that would be perfect! I figured I had to put the form on a FE all by itself if I wanted to display it 24/7. How do I set the timer for the requery? I want to test it out.

[ItsMe]

forms have built in timer that you could use in VBA to trigger a requery

you could build your own function and have it return a value. Maybe modify this example

Function BeginStop(intTime As Integer)

Dim dblCount As Double

dblCount = DateAdd("s", intTime, Now)

While DateDiff("s", Now, dblCount) > 0
Wend

MsgBox "Time is up"

End Function

[justair07]

Awesome thank you for all the help...

I also found this awesome article for timers... http://mooreaboutaccess.com/2010/05/...refresh-forms/

[ssanfu]

You might look here http://access.mvps.org/access/forms/frm0032.htm

I would use something like
(This would be in the form module "frmTestDateNull")

Private Sub Form_Timer()
   If isloaded("Forms!frmTestDateNull" Then
      Me.requery
   End if
End Sub

'start the clock
Private Sub cmdClockStart_Click()
    Me.TimerInterval = 60000      ' 1 minute between requery
End Sub

'need a way to stop requiring - maybe to add/edit data
Private Sub cmdClockEnd_Click()
    Me.TimerInterval = 0
End Sub

To run Visual Basic code at intervals specified by the TimerInterval property, put the code in the form's Timer event procedure. For example, to requery records every 30 seconds, put the code to requery the records in the form's Timer event procedure, and then set the TimerInterval property to 30000.

[ItsMe]

Glad you are working towards a solution.