Automatically Updating a Field in a Form in Access

[liam.gilligan]

Hey Everyone,

I am a bit of a noob at this, but have to build this database and
would love some help if anyone can provide any. I have a database
designed at capturing customer satisfaction surveys. I have a table
that captures answers to several different questions, ie;
Q1 - Rating (either Excellent, Good, Average, Poor)
Q1 - Score (either 10, 6.7, 3.3, 0)

The score corresponds with the text rating, these are then compiled to
give an average result for each staff member over a period of time.

The problem I am having is getting the "Score" to update automatically
depending on the result given in the "Rating" answer.

My table structure is basically like this:

SurveyResults:
SurveyID
CustomerName
EmployeeName
Q1Rating
Q1Score
.....
Q10Rating
Q10Score

Scores:
ScoreID
Rating
Score

I have tried using an OnChange function etc on the form, but can not
get it to work. Can anyone please point me in the right direction of
the best way to tackle this?

Thanks in advance!

 

[Scott McDaniel]

Hey Everyone,

I am a bit of a noob at this, but have to build this database and
would love some help if anyone can provide any. I have a database
designed at capturing customer satisfaction surveys. I have a table

I have tried using an OnChange function etc on the form, but can not
get it to work. Can anyone please point me in the right direction of
the best way to tackle this?

If you're using bound forms, then you don't have to do anything - Access will automatically save the data for you. If
your form has a value (table or query name, or an SQL statement) in the RecordSource property, and your form's controls
(textboxes, combos, etc) have a value in the .ControlSource property, then you're using bound forms.

If your controls have the word "Unbound" in them in Design view, and there is no value in the .Controlsource, then you
don't have a bound form and you'd need to use VBA to do this. However, since you are a self-described "noob" at this
you'd be a lot better off just binding your form to your table ...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[liam.gilligan]

If you're using bound forms, then you don't have to do anything - Access will automatically save the data for you. If

your form has a value (table or query name, or an SQL statement) in the RecordSource property, and your form's controls
(textboxes, combos, etc) have a value in the .ControlSource property, then you're using bound forms.

If your controls have the word "Unbound" in them in Design view, and there is no value in the .Controlsource, then you
don't have a bound form and you'd need to use VBA to do this. However, since you are a self-described "noob" at this
you'd be a lot better off just binding your form to your table ...

Scott McDaniel
scott@takemeout_infotrakker.comwww.infotrakker.com

Hey Scott,

I am using a bound form, but its not quite doing what I was
expecting... not sure if I am explaining myself very clearly (sorry).
I am trying to get it setup so that when you select a
"Rating" (currently set up as a combobox in the form), it will
automatically populate the corresponding "Score" in the field next to
it on the form (which i also curerntly have as a combobox - but maybe
that should change?) - ie "Q1Rating" of "Excellent" will populate
"Q1Score" to be 10. The combobox values are coming from the "Scores"
table, but populating the "SurveyResults" table through the form. Does
that make any sense or am being confusing?

Thanks for your help!

 

[Pat Hartman \(MVP\)]

There are two major issues with your table.
1. You are storing duplicate data - the rating and the score. Only one
is necessary since the one can be derived from the other. Store the Score
which is the numeric value.
2. Your table is not normalized. The answer to each question should be
a row rather than a column. Do some reading on normalization.

#1 is easy to fix. Remove the Rating fields from the table. Change the
Score field in the form to be a combo. Define the combo get the values from
your other table. Choose the score as the bound column and hide it by
setting its width to 0. That way your form will show the rating but the
table will hold the score. The wizard will help you build the combo.
You'll notice that you need to do this 10 times. Once for each question.
This is one of the penalties of a non-normalized structure. In a properly
defined table, you would have only one column in which to hold score and so
there would be only one combo to define.

Fixing #2 is more difficult but if you will ever have to do another survey,
well worth the effort. Dwayne Hookum has a survey sample on his web site.

 

[liam.gilligan]

There are two major issues with your table.
1. You are storing duplicate data - the rating and the score. Only one
is necessary since the one can be derived from the other. Store the Score
which is the numeric value.
2. Your table is not normalized. The answer to each question should be
a row rather than a column. Do some reading on normalization.

#1 is easy to fix. Remove the Rating fields from the table. Change the
Score field in the form to be a combo. Define the combo get the values from
your other table. Choose the score as the bound column and hide it by
setting its width to 0. That way your form will show the rating but the
table will hold the score. The wizard will help you build the combo.
You'll notice that you need to do this 10 times. Once for each question.
This is one of the penalties of a non-normalized structure. In a properly
defined table, you would have only one column in which to hold score and so
there would be only one combo to define.

Fixing #2 is more difficult but if you will ever have to do another survey,
well worth the effort. Dwayne Hookum has a survey sample on his web site.


news:[email protected]...

Thanks for your help - I feel like such an idiot... did as you
suggested with the combo box, much better - thanks!
An infinitely simpler solution to what I was trying to do.
Now, just to normalise it...

Thanks again for your help... I shouldve posted this a week ago and
saved the frustration.

 

[BruceM]

You can find the link to Duane Hookum's At Your Survey database here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp, along with lots of
other links. Take a look at some of Roger's examples, while you're at his
site, just so you get a better sense of the resources available to one and
all.