Refresh Another Form On Form Close (1 Viewer)

mtagliaferri · Mar 17, 2018

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:

Code:

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 · Mar 17, 2018

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

missinglinq · Mar 17, 2018

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 · Mar 17, 2018

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 · Mar 19, 2018

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 · Apr 7, 2018

I am afraid it does not open in Dialog

Code:

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

jdraw · Apr 7, 2018

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 · Apr 7, 2018

mtagliaferri said:
I am afraid it does not open in Dialog

Code:

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 · Apr 8, 2018

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

Code:

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 · Apr 8, 2018

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 · Apr 8, 2018

mtagliaferri said:
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:

Code:

Private Sub Form_Load() DoCmd.GoToRecord , , acLast For i = 1 To 24 DoCmd.GoToRecord , , acPrevious Next i End Sub

Linq ;0)>

Gasman · Apr 8, 2018

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

Code:

    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 · Apr 10, 2018

missinglinq said:
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:

Code:

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 · Apr 11, 2018

mtagliaferri said:
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

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

Refresh Another Form On Form Close (1 Viewer)

mtagliaferri

Registered User.

Gasman

Enthusiastic Amateur

missinglinq

AWF VIP

jdraw

Super Moderator

Reichel

New member

mtagliaferri

Registered User.

jdraw

Super Moderator

missinglinq

AWF VIP

mtagliaferri

Registered User.

isladogs

MVP / VIP

missinglinq

AWF VIP

Gasman

Enthusiastic Amateur

mtagliaferri

Registered User.

Sweetu

Registered User.

Similar threads

Users who are viewing this thread