Solved Records in Form to point to first item of a filtered listbox (1 Viewer)

Woodywoodpecker

New member
Local time
Today, 11:09
Joined
Oct 23, 2020
Messages
6
Hi everybody,

I am new to this forum and self-taught beginner in access. So sorry if my questions/way of trying things looks stupid.

My problem is as follows :

To support my question, I prepared an example database in annex where I only left the elements necessary to explain my issue.

I have :
a table : company
a query based on that table : companyQuery
a form based on taht query : companyForm

In that form companyForm,
I have * a listbox in order to point the form to a specific record : that works ok
* a "search" control that filters the listbox items based on wildcard in order to reduce the number of items from that listbox
* if I want to reset my filter on the listbox, I type a "*" in the search control


after update of that search control,
* I want the first item in the listbox to be selected (that works ok)
AND
* I want the form to update/point to that first item selected in the listbox (wheter my filter on the listbox returns one or more match).

That is where my problem is. It sometimes works, sometimes not. The behavior of what I have done is not stable and/or not working. If you play several times opening companyForm and play with the search form looking for a few characters, and then reset the filter with "*", the filter of the listbox works 100% of the time, the selection of the first item of the lisbox works 100% of the time (at least, visually on the screen) but it comes a moment where the form does not automatically move to the first selected record of that filtered lisbox.

I made some tests and I think I could detect that itemdata() of the select box does not return the same value as selected(0). But I cannot do anything with that and I am stuck.

Could you please help me on that issue ?

Thanks in advance for your help !

François.
 

Attachments

  • Database_test.accdb
    620 KB · Views: 268

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:09
Joined
May 7, 2009
Messages
19,169
add code to the Form's Current event to update the Listbox.
see your Form.
 

Attachments

  • Database_test.zip
    26.2 KB · Views: 138

Woodywoodpecker

New member
Local time
Today, 11:09
Joined
Oct 23, 2020
Messages
6
Thank you arnelgp for your quick reaction but does not seem to work for me with your proposed changes.

Please see in attachment the screen capture with explanation of my problem.

Do you understand my point ? do you have the same behavior as mine ?

Thanks for your help anyway.
 

Attachments

  • access_issue_explanation.png
    access_issue_explanation.png
    134.4 KB · Views: 268

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:09
Joined
May 7, 2009
Messages
19,169
change your Textbox AfterUpdate event:
Code:
Private Sub companyFormSearch_AfterUpdate()
    [companyFormSelector].SetFocus
    [companyFormSelector].Requery
    [companyFormSelector].Selected(0) = True

    DoCmd.SearchForRecord , "", acFirst, "[companyID] = " & Me!companyFormSelector
End Sub
 

Woodywoodpecker

New member
Local time
Today, 11:09
Joined
Oct 23, 2020
Messages
6
Hi again,

In attachment, my files with all your suggested changes.

It does not work for me ... but sometimes yes.

Here below is a sequence that you can do as it seems to be repeatable :

1. open form -> change * by supplier -> hit return : filter is ok - record is NOT ok
2. hit return (once again) : filter still ok and record ok
3. change supplier by other -> hit return : filter ok - record ok !!!
4. close the form and reopen it
5. change the * by other -> hit return : filter ok - record NOT ok !!!

This is the kind of behavior I noticed for hours now without any way to sort it out ...
 

Attachments

  • Database_test1.accdb
    556 KB · Views: 120

Gasman

Enthusiastic Amateur
Local time
Today, 10:09
Joined
Sep 21, 2011
Messages
14,038
Walk through your code with F8, the listbox control is not getting updated at the correct time with the value from the search.
That is as much as I have been able to find.?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:09
Joined
May 7, 2009
Messages
19,169
test this one.
 

Attachments

  • Database_test1.zip
    67.1 KB · Views: 296

Users who are viewing this thread

Top Bottom