?
Solved

Microsoft, Access, 2003, finalizing survey form when to refresh

Posted on 2007-08-07
9
Medium Priority
?
2,153 Views
Last Modified: 2013-11-28
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=true). And also don't allow additions and don't allow deletion on the subform. Hope I was clear enough.
0
Comment
Question by:dustywork
  • 4
  • 4
9 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 19646070
If this is a continutation of an existing question, you'll need to post all relevan info so that new experts can answer it.  
Otherwise, since our mind-reading skills are lacking, we have no idea how to answer your question.
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 19646174
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.
0
 

Author Comment

by:dustywork
ID: 19646802
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


0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:dustywork
ID: 19646838
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.
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 19647295
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.
0
 

Author Comment

by:dustywork
ID: 19647820
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
0
 
LVL 10

Accepted Solution

by:
OnALearningCurve earned 1000 total points
ID: 19649177
Hi Dusty,

You can use the "On Load" event on a for which will run as the form is loaded or "On Open" which works in a similar way.  From the help files I have the following:

"The Open event occurs when a form is opened, but before the first record is displayed. For reports, the event occurs before a report is previewed or printed."

"The Load event occurs when a form is opened and its records are displayed."

So you need top choose the one that suits your task.  But both should only run once and will not run again until the form is closed and re-opened.

Hope this helps and if you need any more info on the Macro let me know.  I got quite handy with Macros before I got brave enough to venture into the world of VB.

Mark.
0
 

Author Comment

by:dustywork
ID: 19649930
Thanks Mark!  You've been ever so helpful!
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 19650438
Glad I could help!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question