problems dropdown box in datasheet with linked tables (1 Viewer)

boerbende

Ben
Local time
Today, 08:51
Joined
Feb 10, 2013
Messages
339
Dear readers

I guess a difficult question here. I have tried to find a solution for many hours now but have only been able to create a workaround.

I have created and attached a strongly simplified version of a database I have developed for the company. Mainform with subform
In the Subform I have two dropdown fields, both linked to a searchtable
1- Material
2- Batchnumber of the material
Idea is when we create a mix, we are able to track the batches used in the mix.
Batchnumber is originating from a hugh table with materials and batchnumbers. So I was thinking to make it more easy for the user: The getfocus event of ID_Batch changes the rowsource to a filtered dataset, based on the material. The user gets a limited set of batchnrs, cannot select a batch from another material which makes it a little more failproof. When scrolling down in the subform, all the information remains visible (like it should). This works OK with local tables, so far so good.

When now changing to linked tables (the tables are stored in a SQL server database), I have the following experience:
- User clicks on the ID_Batch field, gets a correct filtered list of batchnumbers and selects the correct batch
- When the user now goes down with arrow to select the next batch, also here the selection in the dropdown is correct filtered. But the data in the previous field becomes invisible (does not disappear!)

The data is not gone, but the information is not shown any more, which is very confusing for the ones working with the database. Logically this has to do with the filter which is set for the field ID_Batches and so valid for all the fields in the datasheet. So when filtering the second material, the batch for the first cannot be shown because it is not part of the dataset.
I tried everything with all possible events to reset for example the rowsource to not filtered, but with linked tables NOTHING works.
When I make all tables local again, it works.
My workaround now is to have two columns for the user, one where they have to select and the other without tab stop which is only a view, but still I have the hope that I can manage with only one column

Anybody an idea how to solve this?

Thanks

Ben
 

Attachments

  • Database1.accdb
    556 KB · Views: 55

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:51
Joined
May 7, 2009
Messages
19,169
hello, you should realize that there is only one combobox in the datasheet or continous form. so when you filter your combo recordsource, the previous one will be blanked out since it cannot find the corresponding id (bound to the combobox) from the combobox.


there is a trick however, using a pop-up form with another combobox. but it is very ugly.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:51
Joined
May 7, 2009
Messages
19,169
Dear readers

I guess a difficult question here. I have tried to find a solution for many hours now but have only been able to create a workaround.

I have created and attached a strongly simplified version of a database I have developed for the company. Mainform with subform
In the Subform I have two dropdown fields, both linked to a searchtable
1- Material
2- Batchnumber of the material
Idea is when we create a mix, we are able to track the batches used in the mix.
Batchnumber is originating from a hugh table with materials and batchnumbers. So I was thinking to make it more easy for the user: The getfocus event of ID_Batch changes the rowsource to a filtered dataset, based on the material. The user gets a limited set of batchnrs, cannot select a batch from another material which makes it a little more failproof. When scrolling down in the subform, all the information remains visible (like it should). This works OK with local tables, so far so good.

When now changing to linked tables (the tables are stored in a SQL server database), I have the following experience:
- User clicks on the ID_Batch field, gets a correct filtered list of batchnumbers and selects the correct batch
- When the user now goes down with arrow to select the next batch, also here the selection in the dropdown is correct filtered. But the data in the previous field becomes invisible (does not disappear!)

The data is not gone, but the information is not shown any more, which is very confusing for the ones working with the database. Logically this has to do with the filter which is set for the field ID_Batches and so valid for all the fields in the datasheet. So when filtering the second material, the batch for the first cannot be shown because it is not part of the dataset.
I tried everything with all possible events to reset for example the rowsource to not filtered, but with linked tables NOTHING works.
When I make all tables local again, it works.
My workaround now is to have two columns for the user, one where they have to select and the other without tab stop which is only a view, but still I have the hope that I can manage with only one column

Anybody an idea how to solve this?

Thanks

Ben


this can be done, using a unbound pop-up form with another combobox in it.
 

boerbende

Ben
Local time
Today, 08:51
Joined
Feb 10, 2013
Messages
339
hello, you should realize that there is only one combobox in the datasheet or continous form. so when you filter your combo recordsource, the previous one will be blanked out since it cannot find the corresponding id (bound to the combobox) from the combobox.


there is a trick however, using a pop-up form with another combobox. but it is very ugly.

Or by hiding / unhiding the second combobox column and let it switch places with the first!!!
Thanks again arnelgp!.

Ben

(you don't need a new direction, it is often sufficient to be guided away from the route you are traveling on)
 
Last edited:

Users who are viewing this thread

Top Bottom