Set value on multiple existing records with button click

[NickyG]

I have a database designed for administering a really long survey. The user
is presented with a list of all the agencies they mentioned throughout the
survey via a continuous subform and asked whether each has a "formal" or
"informal" relationship with their agency.

I'm trying to set up buttons so that the user could indicate that all
relationships are "formal" or all relationships are "informal" without having
to select each record unless they want to.

The field I'm trying to set is [FormalOrInformal] in the table "Referrals"
and the index that connects the main form "Survey" with the list of referrals
is [SurveyID].

Any help is appreciated!

 

[Douglas J. Steele]

Without know what the difference in terms of what data's stored, it's
difficult to give a definitive answer, but consider running an Update query
(or queries) in the button's Click event to set the data appropriately in
the table(s).

 

[NickyG]

The data I'm trying to update is numeric -- I want to update all the
"FormalorInformal" fields to equal 1 if a button is pushed. I set up an
update query named "FormalQuery" which does this, and put the following code
behind the button's OnClick:

Private Sub AllFormal_Click()
Dim strQueryName As String
strQueryName = "FormalQuery"
DoCmd.SetWarnings (False)
DoCmd.OpenQuery strQueryName
DoCmd.SetWarnings (True)
End Sub

However, when I click the button, it updates the first record in the list
only. Only if I select the next record on the form does the next one get
updated. I'm wondering if it's because I'm suppressing the warnings, so it
never gets that "Yes" message about updating multiple records? Any
suggestions?

My update query is the following:
UPDATE Survey INNER JOIN Referrals ON Survey.SurveyID = Referrals.SurveyID
SET Referrals.formalOrinformal = 1
WHERE (((Survey.SurveyID)=[Forms]![SurveyMainForm]![SurveyID]));

Without know what the difference in terms of what data's stored, it's
difficult to give a definitive answer, but consider running an Update query
(or queries) in the button's Click event to set the data appropriately in
the table(s).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a database designed for administering a really long survey. The
user
is presented with a list of all the agencies they mentioned throughout the
survey via a continuous subform and asked whether each has a "formal" or
"informal" relationship with their agency.

I'm trying to set up buttons so that the user could indicate that all
relationships are "formal" or all relationships are "informal" without
having
to select each record unless they want to.

The field I'm trying to set is [FormalOrInformal] in the table "Referrals"
and the index that connects the main form "Survey" with the list of
referrals
is [SurveyID].

Any help is appreciated!

 

[Douglas J. Steele]

I believe that the reason it's only updating one row is because you're
telling it to only update the row(s) that have the same Id as the currently
selected row on SurveyMainForm. Try removing the WHERE clause.

Using OpenQuery really isn't the best way to run Action queries. Try using
the Execute method:

Private Sub AllFormal_Click()
CurrentDb.QueryDefs("FormalQuery").Execute dbFailOnError
End Sub



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The data I'm trying to update is numeric -- I want to update all the
"FormalorInformal" fields to equal 1 if a button is pushed. I set up an
update query named "FormalQuery" which does this, and put the following
code
behind the button's OnClick:

Private Sub AllFormal_Click()
Dim strQueryName As String
strQueryName = "FormalQuery"
DoCmd.SetWarnings (False)
DoCmd.OpenQuery strQueryName
DoCmd.SetWarnings (True)
End Sub

However, when I click the button, it updates the first record in the list
only. Only if I select the next record on the form does the next one get
updated. I'm wondering if it's because I'm suppressing the warnings, so
it
never gets that "Yes" message about updating multiple records? Any
suggestions?

My update query is the following:
UPDATE Survey INNER JOIN Referrals ON Survey.SurveyID = Referrals.SurveyID
SET Referrals.formalOrinformal = 1
WHERE (((Survey.SurveyID)=[Forms]![SurveyMainForm]![SurveyID]));

Without know what the difference in terms of what data's stored, it's
difficult to give a definitive answer, but consider running an Update
query
(or queries) in the button's Click event to set the data appropriately in
the table(s).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a database designed for administering a really long survey. The
user
is presented with a list of all the agencies they mentioned throughout
the
survey via a continuous subform and asked whether each has a "formal"
or
"informal" relationship with their agency.

I'm trying to set up buttons so that the user could indicate that all
relationships are "formal" or all relationships are "informal" without
having
to select each record unless they want to.

The field I'm trying to set is [FormalOrInformal] in the table
"Referrals"
and the index that connects the main form "Survey" with the list of
referrals
is [SurveyID].

Any help is appreciated!

 

[NickyG]

That didn't work for me -- I kept getting an Error 3601 when I tried to use
Executie -- and I didn't want to remove the Where clause from the update
query, because that ended up changing the value for every record in the
table, not just the records related to the current survey -- each survey has
many referrals, but not every one in the table.

Eventually though, I just added a refresh command to the original code
(DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70) and that seemed
to do the trick.

Thanks!

I believe that the reason it's only updating one row is because you're
telling it to only update the row(s) that have the same Id as the currently
selected row on SurveyMainForm. Try removing the WHERE clause.

Using OpenQuery really isn't the best way to run Action queries. Try using
the Execute method:

Private Sub AllFormal_Click()
CurrentDb.QueryDefs("FormalQuery").Execute dbFailOnError
End Sub



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The data I'm trying to update is numeric -- I want to update all the
"FormalorInformal" fields to equal 1 if a button is pushed. I set up an
update query named "FormalQuery" which does this, and put the following
code
behind the button's OnClick:

Private Sub AllFormal_Click()
Dim strQueryName As String
strQueryName = "FormalQuery"
DoCmd.SetWarnings (False)
DoCmd.OpenQuery strQueryName
DoCmd.SetWarnings (True)
End Sub

However, when I click the button, it updates the first record in the list
only. Only if I select the next record on the form does the next one get
updated. I'm wondering if it's because I'm suppressing the warnings, so
it
never gets that "Yes" message about updating multiple records? Any
suggestions?

My update query is the following:
UPDATE Survey INNER JOIN Referrals ON Survey.SurveyID = Referrals.SurveyID
SET Referrals.formalOrinformal = 1
WHERE (((Survey.SurveyID)=[Forms]![SurveyMainForm]![SurveyID]));

Without know what the difference in terms of what data's stored, it's
difficult to give a definitive answer, but consider running an Update
query
(or queries) in the button's Click event to set the data appropriately in
the table(s).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a database designed for administering a really long survey. The
user
is presented with a list of all the agencies they mentioned throughout
the
survey via a continuous subform and asked whether each has a "formal"
or
"informal" relationship with their agency.

I'm trying to set up buttons so that the user could indicate that all
relationships are "formal" or all relationships are "informal" without
having
to select each record unless they want to.

The field I'm trying to set is [FormalOrInformal] in the table
"Referrals"
and the index that connects the main form "Survey" with the list of
referrals
is [SurveyID].

Any help is appreciated!