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
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