[Solved] Subform Refresh after target field selection issue

[Maax555]

Hi All, I was sure I had tested this form totally and all fields were working as intended. However I lost a file and had to redo a very simple refresh macro and now when i test i am not getting the desired result.

So i have a macro to update the StatusDesc field which is on StatusSub form. As soon as I change a status code in a status listbox the description updates.
As soon as I add another listbox to the mainform (which is working fine), the StatusDesc box does not update as soon as the status code is changed in the list box. For this to work I am having to click in the StatusDesc field which instantly updates the field. I can then return to the Status listbox, update its contents but now it instantly updates the Statusfield. So as long as i give focus to the StatusDesc field once, it will updates correctly afterwards but not until I do this.

I am a little puzzled. Are there any immediate obvious errors anyone can suggest.

[UnklDonald418]

Try the following macro from the Changed event on the List Box control for Status.

Code: Select all   Expand viewCollapse view

Sub   RefreshStatus(oEvent As Object)
Dim oControl
Dim oForm

  oControl = oEvent.Source.Model
  oControl.Commit   'Save list box selection
  oForm = oEvent.Source.Model.Parent.getByName("StatusSub")
  oForm.reload()   'refresh SubForm
End Sub

[UnklDonald418]

It occurred to me that there is a scenario where the RefreshStatus macro can fail. If it executes before a new part number is saved to table PartNumber2 the visible data exists only on the form so a SubForm will see no data in Link Master field. Here is an updated version

Code: Select all   Expand viewCollapse view

Sub   RefreshStatus(oEvent As Object)
Dim oControl
Dim oForm, oMForm

  oControl = oEvent.Source.Model
  oMForm = oControl.Parent

  If oMForm.Row = 0 then
    Beep 
    MsgBox( "Cannot display status description"&chr$(10)& "until new Part Number is Saved", 48, "New Record")
  else
     oControl.Commit   'Save list box selection
     oForm = oMForm.getByName("StatusSub")
     oForm.reload()   'refresh SubForm
  End if
End Sub

[Maax555]

Hi Sorry for the delay in reply as I have been away for a couple of weeks. I changed the macro as per instruction above. If I choose new part number from forms there is no difference. Thats is that the field does not update unless i click inside it. A message is not displayed either. However if i choose the "new record" button within a form then the error message regarding updating and saving does appear.
If it is not possible to have the StatusDesc update automatically before the record is saved then i would prefer to remove the StatusDesc field.

[UnklDonald418]

If it is not possible to have the StatusDesc update automatically before the record is saved then i would prefer to remove the StatusDesc field.

In that case remove it.

[Maax555]

Yes, my thoughts. However as a compromise I will remove the field from the new entries form as a new entry will always be shown as "Newly added ECN" or Newly added RFQ". It may make even more sense to copy the forms to a New ECN form and a New RFQ form and have he field preset accordingly which will remove the need to add that particular status. I can then copy the form to a new Update PartNumber form where the field should update OK as the record has previously been saved. Well that's my thinking, will see how it works in practice.
Many thanks.