Use ListBox1 Value to Set ListBox2 List Values? - No Sub Form or Filter Table Required

skyking52 · November 5, 2022, 6:19pm

How do you get a ListBox changed value to create a SQL Query where the results will populate a second ListBox? After much trial and error I came up with this - and it doesn’t require a complicated Sub Form or Filter Table either.

Solution:

Set the Default Value of the Data Field bound to ListBox1 to a value for the SQL Query parameter (it will get updated when you change ListBox1 and is needed for a New Record as a null value will cause the SQL Query to fail.

Set ListBox2 to ValueList with some string like ‘Default’ in Form Control settings or Form Load will fail with null value error

Trigger the Macro in the Form ‘Record Change’ (to populate ListBox2 on Form Load) and also in the ListBox1 ‘Status Change’ to Update ListBox2 with different values based on a ListBox1 value.

Sub SetDAORecipName
  oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
  oField = oForm.GetByName("dao-name") '<- ListBox1 name
  oField.commit() '<- Force Data Field Update bound to ListBox1 after Change
  oField.refresh()
  oField1 = oForm.getByName("fmtdaoid")
  tVal = oField1.Text '<- Value from ListBox1 Bound Data Field to modify SQL Query
  oField2 = oForm.getByName("Recip-Name") '<- ListBox2 name
  oField2.ListSourceType = 3 '<- Change ListBox2 to SQL from Default ValueList
  if oForm.IsNew Then '<- Different form of Query for new record (if necessary)
    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) '<- Update ListBox2 with new data
  oField2.refresh()
End Sub

Ratslinger · November 5, 2022, 6:26pm

Is there a question here?

skyking52 · November 5, 2022, 6:30pm

Just passing on a solution - didn’t see a category for those…

Ratslinger · November 5, 2022, 6:38pm

That is best asked as a question by you and then provide an answer. Also seems to have been answered many times in this site. Not sure how this is different. Have also done this in a table control - much more difficult.
.
Busy currently but did see your other post. Not satisfied with your result. May look a bit closer at it later when time available.

skyking52 · November 5, 2022, 7:07pm

Solution on other post is what I posted here. Lots of trial and error to satisfy the form load, code here works if you follow the writeup to preset some things. I couldn’t get the code from the ComboBox post where you answered with similar code to this to work without some mods and presets. Now it works great and no complicated sub forms or filter tables.

Ratslinger · November 5, 2022, 7:13pm

Still, do not agree with solution you have. Too Patchy.

skyking52 · November 6, 2022, 10:50am

Thanks for the reference, I remember looking at that when trying to figure this out. My ListBox2 needs to be SQL as it’s bound to the Recipient ID field in the MainForm’s table - select name, store ID. I suppose a subform can use the same table and be on the same record (synced) but still need ListBox2 to update the record and couldn’t figure that out before I had figured out how to do it my way.
Started with DBXL in mid 80s, then php early-mid 90s, then VBA early 2000s before retiring in '05. So this has been more of an exercise in remembering how to code rather than learning how to code.

skyking52 · November 6, 2022, 10:59am

I still find it odd that when a form loads, it tries to process the SQL on a ListBox (but the SQL relies on a value in the form and fails since the form value isn’t set yet), then triggers a record change event before it has loaded the values of the first record into the form.
Ideally, the form should: load data from first record (if it exists), process SQL for ListBoxes, not trigger record changed event, then trigger Form Loaded event (doesn’t exist). Having a Form Loaded event would allow running a macro that relies on the form being populated with data.
20 years of Beta testing database software allows me to see where internal processes could use better organization, and the Base Form Load process just doesn’t feel logical.

Ratslinger · November 6, 2022, 5:08am

Macros are typically much more difficult than a sub form or Filter table in my opinion.
.
Have a look at The sample in the edit section of my post here → How to use user selected value from combobox1 in combobox2 'SELECT' statement - #4 by Ratslinger