dustywork
asked on
Microsoft, Access, 2003, finalizing survey form when to refresh
Hello,
ki ki was very helpful in getting me in the right direction as I am trying to create a survey form that consists of a main form where the user enters name, whether the survey is "internal" or "external" and a few other pieces of information. Based on the selection of internal or external, an append query will run appending tblSurveyDetail, which takes the QuestionID which belong to either internal or external and include a SurveyID which is created by the user entering the mainform data (i.e. name, survey type, etc.). The subform then refreshes and is reading tblSurveyDetail, and in that subform the user enters survey results.
My questions: 1. How do I save the main form record and then run the append query? In other words, what is the event that I can use to trigger running the action query.
2. once the action query is run, what is the event that will trigger running the refresh of the subform?
3. Sidbar question: The subform is in datasheet mode. Questions may be either a number (0-4) or text (survey comments). I have a checkbox indicating which Questions are text or value-based. How can I grey-out the value field for those questions which have textvalue = true?
Thanks!!!!! Listed below is kiki's response:
ki_ki:I see what you want to do.
Here is what I'd do:After the user enters her name, save that record in the tblSurvey. No need for the user to add the date. Make the default value for date as Now(). After that get surveyID (I am guessing it's an auto number). Use that(surveyID) and create an insert query (in code) : the insert query will insert as many records as you want in table tblSurveyDetail. Each record would have the PK as surveyID and the QuestionsResponse. At the end requery the subform...this will dispaly the questions in the subform (subform, starts as blank ...before the user added her name). On the subform lock the QuestionsResponse field (QuestionsResponse.locked=
ki ki was very helpful in getting me in the right direction as I am trying to create a survey form that consists of a main form where the user enters name, whether the survey is "internal" or "external" and a few other pieces of information. Based on the selection of internal or external, an append query will run appending tblSurveyDetail, which takes the QuestionID which belong to either internal or external and include a SurveyID which is created by the user entering the mainform data (i.e. name, survey type, etc.). The subform then refreshes and is reading tblSurveyDetail, and in that subform the user enters survey results.
My questions: 1. How do I save the main form record and then run the append query? In other words, what is the event that I can use to trigger running the action query.
2. once the action query is run, what is the event that will trigger running the refresh of the subform?
3. Sidbar question: The subform is in datasheet mode. Questions may be either a number (0-4) or text (survey comments). I have a checkbox indicating which Questions are text or value-based. How can I grey-out the value field for those questions which have textvalue = true?
Thanks!!!!! Listed below is kiki's response:
ki_ki:I see what you want to do.
Here is what I'd do:After the user enters her name, save that record in the tblSurvey. No need for the user to add the date. Make the default value for date as Now(). After that get surveyID (I am guessing it's an auto number). Use that(surveyID) and create an insert query (in code) : the insert query will insert as many records as you want in table tblSurveyDetail. Each record would have the PK as surveyID and the QuestionsResponse. At the end requery the subform...this will dispaly the questions in the subform (subform, starts as blank ...before the user added her name). On the subform lock the QuestionsResponse field (QuestionsResponse.locked=
Hi dustywork,
I'll do my best to help on each question asked:
1. How do I save the main form record and then run the append query? In other words, what is the event that I can use to trigger running the action query.
It depends how you want the form to work. If it is a data entry form the records are being updated live in the source table as the user fills in the fields.
if not and your query will insert the form fields into a table then you can either add a button to the form, which will run your update query when the user clicks it. Alternatively you can set the After update property of the last field to be filled in to run the query also. So to answer the question you can set the trigger to be anything on the form you like, I think.
2. once the action query is run, what is the event that will trigger running the refresh of the subform?
You can add a line to the end of the code which runs your query to refresh the subform. I think the line is:
DoCmd.RepaintObject acForm, "Your_Form_Name"
This should refresh the form on screen
3. Sidbar question: The subform is in datasheet mode. Questions may be either a number (0-4) or text (survey comments). I have a checkbox indicating which Questions are text or value-based. How can I grey-out the value field for those questions which have textvalue = true?
If you mean grey-out and make inacceible you could set the enabled propety of the field to false. This should grey out the field and prevent the user from editing it.
Hope this is some help.
Mark.
I'll do my best to help on each question asked:
1. How do I save the main form record and then run the append query? In other words, what is the event that I can use to trigger running the action query.
It depends how you want the form to work. If it is a data entry form the records are being updated live in the source table as the user fills in the fields.
if not and your query will insert the form fields into a table then you can either add a button to the form, which will run your update query when the user clicks it. Alternatively you can set the After update property of the last field to be filled in to run the query also. So to answer the question you can set the trigger to be anything on the form you like, I think.
2. once the action query is run, what is the event that will trigger running the refresh of the subform?
You can add a line to the end of the code which runs your query to refresh the subform. I think the line is:
DoCmd.RepaintObject acForm, "Your_Form_Name"
This should refresh the form on screen
3. Sidbar question: The subform is in datasheet mode. Questions may be either a number (0-4) or text (survey comments). I have a checkbox indicating which Questions are text or value-based. How can I grey-out the value field for those questions which have textvalue = true?
If you mean grey-out and make inacceible you could set the enabled propety of the field to false. This should grey out the field and prevent the user from editing it.
Hope this is some help.
Mark.
ASKER
Hello Mark,
Thanks for your comments. I'd like to offer some additional context behind each in hopes of getting to the desired end-state.
1. How do I save the main form record and then run the append query? In other words, what is the event that I can use to trigger running the action query.
Your comment:
"Alternatively you can set the After update property of the last field to be filled in to run the query also. "
I tried that by using the "On Enter" event of the subform to trigger the macro (i just created the macro in access via the drop down boxes no hard-code) which runs the append query. that is working great!
However, when i try to repaint the subform via the same macro which is running the append query, I receive the following error message "The object 'frmSubformSurveyDetail' isn't open. However, the subform is open within the main form.
3. Sidbar question: The subform is in datasheet mode. Questions may be either a number (0-4) or text (survey comments). I have a checkbox indicating which Questions are text or value-based. How can I grey-out the value field for those questions which have textvalue = true?
Your comment:
"If you mean grey-out and make inacceible you could set the enabled propety of the field to false. This should grey out the field and prevent the user from editing it."
Yes, in accessable and ideally the cell would be colored grey. As I don't know how to code this, what could I do to allow for the enabled property to be conditional based on the textvalue field being true?
Thanks,
Dusty
Thanks for your comments. I'd like to offer some additional context behind each in hopes of getting to the desired end-state.
1. How do I save the main form record and then run the append query? In other words, what is the event that I can use to trigger running the action query.
Your comment:
"Alternatively you can set the After update property of the last field to be filled in to run the query also. "
I tried that by using the "On Enter" event of the subform to trigger the macro (i just created the macro in access via the drop down boxes no hard-code) which runs the append query. that is working great!
However, when i try to repaint the subform via the same macro which is running the append query, I receive the following error message "The object 'frmSubformSurveyDetail' isn't open. However, the subform is open within the main form.
3. Sidbar question: The subform is in datasheet mode. Questions may be either a number (0-4) or text (survey comments). I have a checkbox indicating which Questions are text or value-based. How can I grey-out the value field for those questions which have textvalue = true?
Your comment:
"If you mean grey-out and make inacceible you could set the enabled propety of the field to false. This should grey out the field and prevent the user from editing it."
Yes, in accessable and ideally the cell would be colored grey. As I don't know how to code this, what could I do to allow for the enabled property to be conditional based on the textvalue field being true?
Thanks,
Dusty
ASKER
Jim,
I hope that my last post offers more context. I am creating a two surveys which will use the same form. Based on the user's selection in the combo box, SurveyTypeID, an action query is run based "On Enter" event of the subform, which will contain the survey questions based on the survey type.
As you'll note in the previous post, the issue is getting the subform to refresh so that it will be able to read the newly appended records in tblSurveyDetail.
Hope this is clearer, and thanks for taking the time.
I hope that my last post offers more context. I am creating a two surveys which will use the same form. Based on the user's selection in the combo box, SurveyTypeID, an action query is run based "On Enter" event of the subform, which will contain the survey questions based on the survey type.
As you'll note in the previous post, the issue is getting the subform to refresh so that it will be able to read the newly appended records in tblSurveyDetail.
Hope this is clearer, and thanks for taking the time.
Hi dusty,
Try using "Requery" instead of the "RepaintObject" command in your Macro. that may get rid of the error you are receiving.
As for
"Yes, in accessable and ideally the cell would be colored grey. As I don't know how to code this, what could I do to allow for the enabled property to be conditional based on the textvalue field being true?"
this is a bit more long winded.
By setting the enabled property of a field to "No" int the forms design view you will see that the field becomes grey and when you open the form the field will stay grey and you cannot access it.
As for setting this for each field you would have to check each field. You could create a macro with conditons that checks the textvalue for each of these fields and sets the enable probperty to true or false depending on the textvalue.
To do it in a Macro is far more lon winded than in code bacause you loose a lot of flexibility but it can be done.
You would have to create a "Conditional Macro" with a set of entries that change the field to enabled if the texvalue is 'A' and then below that have an entry that sets the field to Not Enabled id the textvalue is 'B'. You could cover every field in one macro and call it each time a record is changed, or have a single macro for each record and just call the required Macro for the field each time the field if changed (i.e. using the "After Updte" event.
Hope this is not too vague and confusing!
Mark.
Try using "Requery" instead of the "RepaintObject" command in your Macro. that may get rid of the error you are receiving.
As for
"Yes, in accessable and ideally the cell would be colored grey. As I don't know how to code this, what could I do to allow for the enabled property to be conditional based on the textvalue field being true?"
this is a bit more long winded.
By setting the enabled property of a field to "No" int the forms design view you will see that the field becomes grey and when you open the form the field will stay grey and you cannot access it.
As for setting this for each field you would have to check each field. You could create a macro with conditons that checks the textvalue for each of these fields and sets the enable probperty to true or false depending on the textvalue.
To do it in a Macro is far more lon winded than in code bacause you loose a lot of flexibility but it can be done.
You would have to create a "Conditional Macro" with a set of entries that change the field to enabled if the texvalue is 'A' and then below that have an entry that sets the field to Not Enabled id the textvalue is 'B'. You could cover every field in one macro and call it each time a record is changed, or have a single macro for each record and just call the required Macro for the field each time the field if changed (i.e. using the "After Updte" event.
Hope this is not too vague and confusing!
Mark.
ASKER
Thanks Mark!
Requery works like a champ. One question on this. What event for a given object can only trigger once? I used "On Enter" as the trigger for the subform and it creates the potential for the macro to run multiple times as the user clicks outside the subform and then clicks back in. I tried on dirty, but that requires data entry, and the whole point is to get the queried records in there and then execute data entry?
Any ideas? Working on your macro suggestion!
Kind Regards,
Dusty
Requery works like a champ. One question on this. What event for a given object can only trigger once? I used "On Enter" as the trigger for the subform and it creates the potential for the macro to run multiple times as the user clicks outside the subform and then clicks back in. I tried on dirty, but that requires data entry, and the whole point is to get the queried records in there and then execute data entry?
Any ideas? Working on your macro suggestion!
Kind Regards,
Dusty
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Mark! You've been ever so helpful!
Glad I could help!
Otherwise, since our mind-reading skills are lacking, we have no idea how to answer your question.