Need to requery/refresh DLOOKUP (in unbound control) after listbox selection has changed

[skydivetom]

Hello:

I need some assistance with re-querying a DLOOKUP control.

Process:
- I have a form including 2 listboxes allowing to select a *SINGLE* value each.
- Upon executing a command button the 2 listbox values are passed into a query (criteria) and then automatically update the subform's data source.
- This works great!

Next:
- Upon reopening the form, the subform's reloads (reopens) the data source based on the previous query execution.
- While that's ok, I'd have added 2 unbound controls containing a DLOOKUP parameter.
- That said, when reopening the form, the 2 unbound control show the previous value selection which matches the subform's underlying data set.



Challenge:
- While the above works fine, the moment I re-execute the query with different values from the listbox, the displayed values in the 2 unbound controls do NOT reflect the current listbox values.

Control Source for DLookups in unbound control "txtFIELD_XYZ":

=DLookUp("FIELD_XYZ","Queryname")

Private Sub lst_LIST1_AfterUpdate()
    
    Me.txtFIELD_XYZ.Requery
  
End Sub

My question:
- What code is missing/required to ensure the unbound's control DLOOKUP is updated immediately after the listbox value has changed?
- Apparently, the above AFTERUPDATE function alone does not work properly. At least, the displayed value is NOT updated immediately... it seems like it's lagging 1 step behind.

[June7]

There is no "Single" option for listbox. Multi-select property offers: Simple, Extended, None.

Listbox cannot be set for multiple selection.

With setting of None, DLookup() updates immediately.

[skydivetom]

Property = None

[June7]

Works for me. No code needed.

Does query have multiple records?

Your DLookup can only always return value from first record because there is no search criteria.

=DLookup("FIELD_XYZ", "Queryname", "somefield=" &
[listboxname])

[skydivetom]

It would be awesome if that's working! Right now, I have placed the following into the unbound's control CONTROL SOURCE:

=DLookup("ORGANIZATION", "Q212_MainForm_CustomerReview_Subform", "txtORGANIZATION_SELECTED=" & [lst_Organization])

where:
Table/query field name = "ORGANIZATION"
Q212... = query name
Unbound control name = txtORGANIZATION_SELECTED
lst_Organization = listbox

Right now, the following occurs:
- Upon opening the form, unbound control shows: "#Error"... keep in mind, no value has been selected upon opening the form.
- Once I click the listbox, "#Error" is blinking superfast...


Process Recap:
- I select values from 2 listboxes and then click command button to execute/update existing query.
- Query is updated in the background and subform is immediately refreshed.
- Whether I have 1 record or 100 records, the ORGANIZATION value is always the same. So, I did like the idea of just grabbing the "first" record's organization value.
- But again, I do see the proposed solution as an issue as I want to immediately show an organization value once I open up the form... even before re-selecting the same/different organization value from the listbox.

Makes sense?

[June7]

txtORGANIZATION_SELECTED is wrong usage. Must be a field in table/query such as the key field.

Don't need to use DLookup. Include Organization field as a column of listbox. If it is in 3rd column, its index is 2 so expression in textbox: =lst_Organization.Column(2)

Why isn't Organization displayed in listbox anyway? What is listbox for?

[skydivetom]

June7:

Almost there... =[lst_Organization].[Column](0) immediately displays a value once I select/change the listbox value. Great!

HOWEVER... nothing is displayed upon opening the form! That's what I tried to use the DLookup pointing to the query (incl. the previously selected value from its last execution) which would always show a value.

So, this proposed solution still does not meet the envisioned requirement.

[skydivetom]

Oh, btw... yes, a listbox value is shown/highlighted once a selection has been made.

However, given my subform is automatically populated based on the last/current execution, it's confusing to have subform records when the listbox doesn't have a value selected when the form opens.

That's why I want to use the unbound control to always show the associated organization when the form opens.

[June7]

I don't understand "incl. the previously selected value from its last execution".

If Organization is same in all records, then agree, don't need filter in DLookup.

If nothing is selected in listbox, why would you need to have anything in textbox?

So listbox is not bound? What is RowSource for listbox? Would there always be a record with ID of 1? Could set its DefaultValue property.

[skydivetom]

"If nothing is selected in listbox, why would you need to have anything in textbox?"

Again, the subform is linked to the query! Listbox merely updates query criteria but subform will always display values. That's why I want the unbound controls to display.