Snowflake68
Registered User.
- Local time
- Today, 17:28
- Joined
- May 28, 2014
- Messages
- 452
Apologies for the very long post.
I know for some that as soon as they see 'Multi-Valued' field in Access they go "oh you shouldn't have used those" but I built my application without knowing really how many issues these can actually have. Now having read lots of posts on various different forums I understand now that the multi valued field can be pretty useless for many reasons especially when you want to use parameter queries with them which is the issue I have.
I now wish that I could have found an alternative way for allowing the user to select (and Store) multiple values from a list that will allow me to query them using parameters.
So I have spent several weeks building an application which uses the built in Multi-Valued field for some of the combo boxes on a form (I have 7 combo boxes all using this method) and I have already managed to overcome a few issues with these already so I don't really want to give up on them just yet especially as I have spent so much time and effort on this so far, but now I feel I have the biggest obstacle of all.
I have a form which is bound to my main data table (of which has several MV fields) to store the selections made from the list in the combo box as well as lots of other normal combo boxes and text boxes All good so far.
My issue;
I have a query that runs on main data table with a parameter that looks at the current record on the form in order to print and export that record. However tables containing MV fields cannot use parameter queries. But if I type the reference number of the record directly into the query and save it all works OK. I did this just for testing purpose so I know there isn't an issue with the query itself.
How to solve;
Is there a way of dynamically inputting the reference number into the query? Would this even work? Would I have to write the whole SQL statement in VBA?, although I dont know how to write the SQL to work within VBA.
OR
Do I need to completely redesign my database. I have sat and thought about how I can do this but these are the issues/lack of skill that I have.
How do I do the combo box so that the user can select multiple values?
Do I store them in a separate table with each selection as a separate record (along with their reference to join on later). But then if I join on them later how do I group those items into a single record for the report?
Apologies for the long post but I am desperate for some help with this especially now as I have to do a demo to the customer on Monday. I will be able to show them the MV combo boxes working but not the reports yet.
If I need to move away from the MV combos as they currently are then I would rather not show them at this stage.
I know for some that as soon as they see 'Multi-Valued' field in Access they go "oh you shouldn't have used those" but I built my application without knowing really how many issues these can actually have. Now having read lots of posts on various different forums I understand now that the multi valued field can be pretty useless for many reasons especially when you want to use parameter queries with them which is the issue I have.
I now wish that I could have found an alternative way for allowing the user to select (and Store) multiple values from a list that will allow me to query them using parameters.
So I have spent several weeks building an application which uses the built in Multi-Valued field for some of the combo boxes on a form (I have 7 combo boxes all using this method) and I have already managed to overcome a few issues with these already so I don't really want to give up on them just yet especially as I have spent so much time and effort on this so far, but now I feel I have the biggest obstacle of all.
I have a form which is bound to my main data table (of which has several MV fields) to store the selections made from the list in the combo box as well as lots of other normal combo boxes and text boxes All good so far.
My issue;
I have a query that runs on main data table with a parameter that looks at the current record on the form in order to print and export that record. However tables containing MV fields cannot use parameter queries. But if I type the reference number of the record directly into the query and save it all works OK. I did this just for testing purpose so I know there isn't an issue with the query itself.
How to solve;
Is there a way of dynamically inputting the reference number into the query? Would this even work? Would I have to write the whole SQL statement in VBA?, although I dont know how to write the SQL to work within VBA.
OR
Do I need to completely redesign my database. I have sat and thought about how I can do this but these are the issues/lack of skill that I have.
How do I do the combo box so that the user can select multiple values?
Do I store them in a separate table with each selection as a separate record (along with their reference to join on later). But then if I join on them later how do I group those items into a single record for the report?
Apologies for the long post but I am desperate for some help with this especially now as I have to do a demo to the customer on Monday. I will be able to show them the MV combo boxes working but not the reports yet.
If I need to move away from the MV combos as they currently are then I would rather not show them at this stage.