'After Record Change' Form Event Fires Before Form Controls Populated on Load - Solved

skyking52 · November 5, 2022, 11:04am

LO 7.4 running on Linux Mint 20, jdbc:Mysql 8

I am duplicating the recommended method to Update a Listbox based on a changed value in another ListBox as discussed in post 31801. The Macro is linked to Item status changed in daoname list box and in After record change on MainForm. When the Form loads it fires off the After Record Change event before the form is populated with data so tVal comes up null and the SQL Query fails when the form is opened. Once I acknowledge the errors and select the same value in the first ListBox (kicking off Item Status Changed) the macro executes and the second ListBox is correctly populated. The problem seems to be in the event sequence. If After Record Change would fire off after the form is populated, tVal would be set and the Query would run correctly.

This also fails on creating a new record as tVal is null, so will have to test for array(strSQL) is null (not sure how) then re-run query without dao-dist.daoid = " + tVal.

And this Sub won’t trigger an On Error event to process in the Sub.

Villeroy · November 5, 2022, 2:02pm

Why do you want to filter the record set by the first value of “fmtdaoid”? Let the form load without any macro, click form control “fmtdaoid” and then the auto-filter button.

skyking52 · November 5, 2022, 2:14pm

Read the OP referenced. Automated process to set ListBox values based on current record (fmtdaoid text box value). No extra clicks required. Toolbars removed for noob data entry people.

Villeroy · November 5, 2022, 3:35pm

Show the related dao items in a subform.

skyking52 · November 5, 2022, 5:20pm

UPDATE: This issue has been Resolved. Error was the second ListBox set to SQL but not getting populated by Macro running during Form Load (?) so I set it to ValueList in the Form Control Settings then added Field.ListSourceType = 3 before the SQL assignment in the macro. Valuelist satisfied the initial load then changing to SQL to assign the array once form was loaded.

New Record error was resolved by testing for form.IsNew and modifying the strSQL query .

Working Code:

Sub SetDAORecipName
  oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
  oField = oForm.GetByName("dao-name")
  oField.commit()
  oField.refresh()
  oField1 = oForm.getByName("fmtdaoid")
  tVal = oField1.Text
  oField2 = oForm.getByName("Recip-Name")
  oField2.ListSourceType = 3
  if oForm.IsNew Then
    strSQL = "SELECT `recip-name`.`recip-name` `recip-name`, `recip-name`.`recipid` `recipid` FROM `epicenter`.`recip-name` `recip-name`, `epicenter`.`dao-recip` `dao-recip` WHERE `recip-name`.`recipid` = `dao-recip`.`recipid` AND `dao-recip`.`daoid` = " + tVal + " GROUP BY `recip-name`.`recipid` ORDER BY `recip-name` ASC"
  else
    strSQL = "SELECT `recip-name`.`recip-name` `recip-name`, `recip-name`.`recipid` `recipid` FROM `epicenter`.`dao-recip` `dao-recip`, `epicenter`.`dao-dist` `dao-dist`, `epicenter`.`recip-name` `recip-name` WHERE `dao-recip`.`daoid` = `dao-dist`.`daoid` AND `recip-name`.`recipid` = `dao-recip`.`recipid` AND `dao-dist`.`daoid` = " + tVal + "ORDER BY `recip-name` ASC"
  End If
  oField2.listsource = array(strSQL)
  oField2.refresh()
End Sub