Append records to table only if user selects "True" on form

  • Thread starter Thread starter Rachel Garrett
  • Start date Start date
R

Rachel Garrett

1) I have a table, Scheduled Assessments, that consists of Question
Name [text, primary key], Scheduled Assessment Date [date], Assessor
[text], and Target Score [number].

2) I have another table, Assessment Scores and Dates, which is a
record of only assessments that have passed. The fields are Question
Name [text], Score in Assessment [number], Date of Assessment [date],
and Origin [text]. There is no primary key because Question Name can
be duplicated.

3) I have a form that allows the user to view information about the
question (this comes from a separate table). It has a subform
generated from Scheduled Assessments. (This is because the question
information remains the same, but each question can have multiple
assessments.)

4) I want to add a control to the Scheduled Assessments subform,
"Milestone Achieved", where the user selects YES or NO. But I'm not
sure how to code what happens afterwards:

***If the user selects YES, I want to append a new record to
Assessment Scores and Dates.***

[Scheduled Assessments].[Question Date] should become [Assessment
Scores and Dates].[Question Date]

[Scheduled Assessments].[Scheduled Assessment Date] should become
[Assessment Scores and Dates].[Date of Assessment]

[Scheduled Assessments].[Target Score] should become [Assessment
Scores and Dates].[Score in Assessment]

[Assessment Scores and Dates].[Origin] should be set to "Milestone
Assessment"



Should I go back to the Design View or SQL view of the query that I
based the form on? Or can I make a button in the subform to do an
append query if the user selects "YES"? Thank you in advance for any
assistance.

--Rachel
 
Rachel,

Few questions:
Are you looking to use an option group (either Yes or No or Nothing) or are
you just looking a button that appends certain data to a table when you push
Yes?

If you are just using a command button then under the On Click event use
this code:

Dim strSQL as String

strSQL = “INSERT INTO [Assessment Scores and Dates] (field1,field2,field3,
etc) VALUES (value of field1, value of field2, value of field 3,etc)â€

DoCmd.RunSQL strSQL

From your description, there is no way to relate the assessments that pass
to the data they are coming from. This would result in a list of assessments
that has no value other than being a list of assessments. If you are just
collecting the number of times that a particular question is being
successfully assessed, then no problem, however, you would not be collecting
much else.

For example, I am on Question 1 and I click Yes ten times just for fun. How
would you know that happened?

Hope this helps!
PJ
 
Rachel,

Few questions:
Are you looking to use an option group (either Yes or No or Nothing) or are
you just looking a button that appends certain data to a table when you push
Yes?  
Click to expand...

The latter.
If you are just using a command button then under the On Click event use
this code:

Dim strSQL as String

strSQL = “INSERT INTO [Assessment Scores and Dates] (field1,field2,field3,
etc) VALUES (value of field1, value of field2, value of field 3,etc)”

DoCmd.RunSQL strSQL
Click to expand...

Thank you!
From your description, there is no way to relate the assessments that pass
to the data they are coming from.  This would result in a list of assessments
that has no value other than being a list of assessments.  If you are just
collecting the number of times that a particular question is being
successfully assessed, then no problem, however, you would not be collecting
much else.
Click to expand...

That is true. I need to have a list of assessments. When I try to get
everything I need from the data directly, the queries work but they
make the form uneditable since they're pulling from several of the
same tables. So I needed a way to "force" the yes/no results into a
count of assessments that passed. Inelegant but it's all I know how to
do right now.

Thanks,
Rachel
 
Back
Top