Solved Combos that disappear when I update them (1 Viewer)

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
Hi.

I have a problem with some dropdowns. As I know that it will be difficult for me to show the problem I have, I have recorded a video.

Anyway, I try to explain something better, because speaking in English is very difficult for me.

I have a form to enter data in eight tables, all of them with an identical structure. When opening the form, by means of a recordset I establish the origin of said form in the table that I want to introduce data. Since there is a dropdown, I also have to set the origin of it. My problem is with this dropdown, when I modify it (deleting what I have written, for example) it disappears all other data from the dropdowns. I have tried to put a Me.Requery when updating the dropdown, but it does not solve. It gives me that it has to be something from the origin of the data, but I don't know how to solve it.


Thanks a lot.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:42
Joined
Oct 29, 2018
Messages
21,357
I have a form to enter data in eight tables, all of them with an identical structure.
That statement causes an alarm for us to worry if your table structure is not properly normalized. It might be better if you could post a sample copy of your db with test data.
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
That statement causes an alarm for us to worry if your table structure is not properly normalized. It might be better if you could post a sample copy of your db with test data.
 

Attachments

  • ATC.accdb
    3.6 MB · Views: 182

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
Good Morning.

I have tried to pass the table parser to see if there were any normalization problems, and it did not give any errors.

@theDBguy, could you see anything?

Thank you!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:42
Joined
May 7, 2009
Messages
19,169
having Same structure is already a doubt.
you can have it in 1 table + 1 field (an identifier of "which" records/or group").
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
It is also true. I had not thought of it. Thanks a lot.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:42
Joined
May 7, 2009
Messages
19,169
i have not thoroughly gone through your code.
but i noticed that you set up things in the Open event.
try moving them on the Load event.

the controls are not yet "created" on the Open event.
while on the Load event, all the controls are alive.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:42
Joined
May 7, 2009
Messages
19,169
there is also this behaviour of your form.
it is initially showing 4 records + "new record"
when you edit/replace the content of a combobox and cancel the edit
(by pressing esc, as many times), your form "collapsed" and
show the "remaining records.
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
And how do I fix it? Because if I put everything together in a table, as you say, I would like to have a drop-down to select the previous level, and that it remains in a cascade, I don't know if I explain myself.
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
I have tried to separate the code: on the one hand, when opening, the one that establishes the origin of the form; and, when loading, everything else.

Also, I have been trying to add the code to establish the origin of the combobox row when undoing (it does nothing) and when entering (it gets caught). Lastly, I have tried to add a Me.Requery after updating the combobox, but it doesn't do anything either.
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
Good afternoon. I would like to reopen this thread. I have kept testing, and the error is not in setting the row origin using VBA.

Isolating my personal functions that I have in the event when opening and when loading, I have detected that if I deactivate the @MajP module, when I cancel the introduction of values in the dropdown, it does not erase the values of the previous dropdowns. What's more, I have tried another simpler database, and it has the same behavior.

@MajP, the problem comes when you write something in the drop-down, and then you want to delete the entry from the register, hitting the Escape key.

I leave the database here for you to check it yourself.

Could you check it out?
 

Attachments

  • ATC.accdb
    3.6 MB · Views: 208

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
I do not understand the issue or how to recreate.
If I type TIN I get
Antineoplastic and immunomodulating agents
If I hit esc before selecting a value it stops the find as you type, and if I hit it again it clears the value.
If I select the value I can hit a single ESC. This is as expected, so not sure of the problem.
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
I record a video for you so you can see how I do it.
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
Perhaps a clause could be added to avoid this behavior in case the user gave him by pressing escape several times.
 

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
With once you press escape you delete the content of the combo, but a second time you cancel the registration. That's where the fault is
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
This is normal behavior for a combo box on a continuous form and not unique to a find as you type. This is common with cascading combo boxes.
Assume your combo box has three choices.
Cat
Dog
Mouse

Assume you have previous records with choices of cat, dog, mouse.

If you filter a combobox to just Mouse, all replications of the combobox are effected. That means previous records of Cat and Dog will not display. The values are still there, but they cannot be displayed.

A combo box can ONLY display values that are in the ROW SOURCE, even if other values already exist. The other values are there but cannot be displayed.

There is nothing in the class module that can address this normal behaviour. The best trick is to overlay a text box over the combobox. See demo.
 

Attachments

  • ATC_MajP.accdb
    660 KB · Views: 219

zelarra821

Registered User.
Local time
Today, 12:42
Joined
Jan 14, 2019
Messages
803
Ahhh, okay. Perfect. Thank you very much for the explanation and for the proposed resolution. A greeting.
 

Users who are viewing this thread

Top Bottom