northernfrog
New member
- Local time
- Today, 10:00
- Joined
- May 11, 2008
- Messages
- 4
I've inherited an Access file containing 7 forms used as educational tools to show some statistics gathering and calculations. The premise is similar to questionnaire or survey scenarios, but I don't need to create either, save data etc... Initially the file contained no tables, queries or reports. Data entry was made into combo boxes populated by value lists. and calculations coded behind the forms. Users would like to be able to print out the information from the forms and I'm trying to create a single report. The person who created the file, and has now moved to another project said a table would be needed.
I've read volumes of posts, downloaded a number of samples, and tried several approaches to accomplish this but have run into a roadblock each time. First I created tables containing question numbers, questions, that populated the report grouped by the form names. The first problem came with the table used as a control source for all the combo boxes. It was an unnormailised table with 80+ fields. Union queries to join the data so it could go into a normalised table failed. Access kept returning the error message that the query was too complex even when half the data was dropped. I don't know VBA so creating code to loop through the data fields as stated in one forum post is not an option.
Abandoning that I created a lookup table of all data that has to populate the combo boxes, and used a query as form record source to capture the necessary data particular to each form. Was doing ok until the latest problem... I want to populate each combo box using a query as row source. In order to populate a particular combo box with the right data, it has to match the question number with either the combo box name (each combo box is named in part with the question number) or if it's possible if the question number were placed in the combo box Tag property. Have searched all over trying to find how to correctly refer to this in a query. Any Like or = statement I've placed in the Where clause either errors or asks for control name or QuestionId. Is it even possible to reference a control name or tag property in a query without using VBA?
Any thoughts or suggestions appreciated.
I've read volumes of posts, downloaded a number of samples, and tried several approaches to accomplish this but have run into a roadblock each time. First I created tables containing question numbers, questions, that populated the report grouped by the form names. The first problem came with the table used as a control source for all the combo boxes. It was an unnormailised table with 80+ fields. Union queries to join the data so it could go into a normalised table failed. Access kept returning the error message that the query was too complex even when half the data was dropped. I don't know VBA so creating code to loop through the data fields as stated in one forum post is not an option.
Abandoning that I created a lookup table of all data that has to populate the combo boxes, and used a query as form record source to capture the necessary data particular to each form. Was doing ok until the latest problem... I want to populate each combo box using a query as row source. In order to populate a particular combo box with the right data, it has to match the question number with either the combo box name (each combo box is named in part with the question number) or if it's possible if the question number were placed in the combo box Tag property. Have searched all over trying to find how to correctly refer to this in a query. Any Like or = statement I've placed in the Where clause either errors or asks for control name or QuestionId. Is it even possible to reference a control name or tag property in a query without using VBA?
Any thoughts or suggestions appreciated.
