Refresh Another Form On Form Close

[mtagliaferri]

I need to refresh a form after I added a new record from a popup form, I am aware of the requery function but I am struggling to put it in place; I believe I should ad a line on save comand which closed the popup form.

the code is:

Private Sub CmdSave_Click()
    On Error GoTo Err_CmdSave_Click
    DoCmd.Close
Exit_CmdSave_Click:
    Exit Sub
Err_CmdSave_Click:
    MsgBox Err.Description
    Resume Exit_CmdSave_Click

End Sub

Any suggestions?

 

[Gasman]

How are you calling the popup form?
If from the form that has the new record, you could put it there?

 

[missinglinq]

If you are opening the secondary form in Dialog mode (which halts code execution in the primary, calling Form) all you have to do, after the line that opens the secondary Form, is simply add

Me.Requery

Linq ;0)>

 

[jdraw]

I'm not sure why you have the pop up form, but I stumbled upon this technique that might be of interest to you --even if only for reference.

 

[Reichel]

I had a problem in the past with requery or refresh when the focus is on another form than what needs updating ( in this case the pop up)

you can try adding a empty control on main form( call it Z for example), make your pop-up move focus to Z on close. Set Z to requery once it gets focus.

it's probably not the best solution, but it worked for me.

 

[mtagliaferri]

I am afraid it does not open in Dialog

Private Sub CmdAdd_Click()
    DoCmd.OpenForm "frmDuty", acNormal, "", "", acAdd
    Forms!frmDuty.TripDate.SetFocus
End Sub

 

[jdraw]

see https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openform-method-access

expression. OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

and look at windowmode parameter

 

[missinglinq]

I am afraid it does not open in Dialog

Private Sub CmdAdd_Click()
    DoCmd.OpenForm "frmDuty", acNormal, "", "", acAdd
    Forms!frmDuty.TripDate.SetFocus
End Sub

Not surprising! The correct syntax would not be

DoCmd.OpenForm "frmDuty", acNormal, "", "", acAdd

but

DoCmd.OpenForm "frmDuty", acNormal, "", "", acFormAdd, acDialog

Not trying to be mean, or unkind, but in Access VBA, as in all coding, being 'close' just won't work...you have to be exact.

Linq ;0)>

 

[mtagliaferri]

Thank for the info!
I have changed the code to your suggestion and added Me.Requery and works perfectly.

Now I have another issue the mail form has the following code to scroll to the last 25 records on the list

Private Sub Form_Open(Cancel As Integer)
   With Me.Recordset
      .MoveLast
      .Move -25
   End With
End Sub

where should I put the above code to show the last 25 records?:banghead:

 

[isladogs]

Why not sort in reverse order and select the top 25 in the record source instead so the most recent is at the top?

 

[missinglinq]

I have changed the code to your suggestion and added Me.Requery and works perfectly.

Now I have another issue the mail form has the following code to scroll to the last 25 records on the list...where should I put the above code to show the last 25 records?

Glad that you got that first problem working!

As a rule, the Form_Open event is too early to do anything related to data...that's usually done in the Form_Load event...so you can try the code there, although I'm not sure that your

.Move -25

will do the trick.

I'd try something like this:

Private Sub Form_Load()

DoCmd.GoToRecord , , acLast

For i = 1 To 24
 DoCmd.GoToRecord , , acPrevious
Next i

End Sub

Linq ;0)>

 

[Gasman]

I have this code in one of my forms doing the same thing, but for 5 records.

    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    DoCmd.RunCommand acCmdRecordsGoToLast

HTH

 

[mtagliaferri]

Glad that you got that first problem working!

As a rule, the Form_Open event is too early to do anything related to data...that's usually done in the Form_Load event...so you can try the code there, although I'm not sure that your

.Move -25

will do the trick.

I'd try something like this:

Private Sub Form_Load()

DoCmd.GoToRecord , , acLast

For i = 1 To 24
 DoCmd.GoToRecord , , acPrevious
Next i

End Sub

Linq ;0)>

I have tried this option but not successful, the list form that is included into a tabbed main form stays open in the background as the Add Form opens as pop up, this might be the reason...
Anymore thoughs? I can possible sort them in reverse order but is not really the ideal solution...

 

[Sweetu]

I have tried this option but not successful, the list form that is included into a tabbed main form stays open in the background as the Add Form opens as pop up, this might be the reason...
Anymore thoughs? I can possible sort them in reverse order but is not really the ideal solution...

make one combobox & add Ascending & Descending list,
On After update Event paste this code

If Me.[COLOR=Blue]YourComboboxName[/COLOR].Value = "Ascending" Then
    DoCmd.SetOrderBy " [COLOR=blue]Primary kEy or your value for[/COLOR] ASC"
Else
    If Me.[COLOR=Blue]YourComboboxName[/COLOR].Value = "Descending" Then
    DoCmd.SetOrderBy "[COLOR=blue]Primary kEy or your value for[/COLOR] DESC"
End If
End If

then you can refer another box to put the value how many records you want to show & merge that with this ascending & descending code