By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,406 Members | 1,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,406 IT Pros & Developers. It's quick & easy.

Updating Yes/No Fields !

P: n/a
In a small Contacts Database, I have 6 Boolean(Yes/No) Fields so that
6 individuals in the company can maintain their own private mailing
list.

The names of the fields are as follows:
Commander
Iyer
Finance
Suresh
Jessie
General

I also have another Yes/No field called MARKED.

When the user selects one of the above 6 names in a combo box on my
form, I would like the MARKED field to get replaced with a YES for
each YES in the 6 Fields.

eg. if the user clicks COMMANDER, the MARKED field should get marked
with a YES for each YES in the COMMANDER field. Similarly with all the
other fields.

Can anyone please suggest how to go about doing this.

Thx & Best Rgds,
Prakash
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies

P: n/a
The only way I see doing this is with code. I hope I understand you
correctly.

On the "After Update" event of the combo box invoke the Code Builder and
type this:
(I am going to use cboUser as the combo box name)

Select Case cboUser.Value
Case "Commander"
[Commander] = vbYes
[Marked] = vbYes
Case "Iyer"
[Iyer] = vbYes
[Marked] = vbYes
Case "Finance"
[Finance] = vbYes
[Marked] = vbYes
Case "Suresh"
[Suresh] = vbYes
[Marked] = vbYes
Case "Jessie"
[Jessie] = vbYes
[Marked] = vbYes
Case "General"
[General] = vbYes
[Marked] = vbYes
End Select

Hope that helps!
On 13 Dec 2003 13:50:19 -0800, Prakash Wadhwani <si****@omantel.net.om>
wrote:
In a small Contacts Database, I have 6 Boolean(Yes/No) Fields so that
6 individuals in the company can maintain their own private mailing
list.

The names of the fields are as follows:
Commander
Iyer
Finance
Suresh
Jessie
General

I also have another Yes/No field called MARKED.

When the user selects one of the above 6 names in a combo box on my
form, I would like the MARKED field to get replaced with a YES for
each YES in the 6 Fields.

eg. if the user clicks COMMANDER, the MARKED field should get marked
with a YES for each YES in the COMMANDER field. Similarly with all the
other fields.

Can anyone please suggest how to go about doing this.

Thx & Best Rgds,
Prakash


--
"Justo, we have a problem!"
If you want to send email, you may do so at:
newsgroup at jwhap dot com
Nov 12 '05 #2

P: n/a
Thx Justo ... I think I did'nt get myself across correctly.

What I'd like is that when the user selects COMMANDER, I'd like to run
an SQL Statement changing all MARKED to YES for each COMMANDER=YES.

Hope you can help me.

Thx & Best Rgds,
Prakash.


Justo <ne*******@noemail.com> wrote in message news:<op**************@news.la.sbcglobal.net>...
The only way I see doing this is with code. I hope I understand you
correctly.

On the "After Update" event of the combo box invoke the Code Builder and
type this:
(I am going to use cboUser as the combo box name)

Select Case cboUser.Value
Case "Commander"
[Commander] = vbYes
[Marked] = vbYes
Case "Iyer"
[Iyer] = vbYes
[Marked] = vbYes
Case "Finance"
[Finance] = vbYes
[Marked] = vbYes
Case "Suresh"
[Suresh] = vbYes
[Marked] = vbYes
Case "Jessie"
[Jessie] = vbYes
[Marked] = vbYes
Case "General"
[General] = vbYes
[Marked] = vbYes
End Select

Hope that helps!
On 13 Dec 2003 13:50:19 -0800, Prakash Wadhwani <si****@omantel.net.om>
wrote:
In a small Contacts Database, I have 6 Boolean(Yes/No) Fields so that
6 individuals in the company can maintain their own private mailing
list.

The names of the fields are as follows:
Commander
Iyer
Finance
Suresh
Jessie
General

I also have another Yes/No field called MARKED.

When the user selects one of the above 6 names in a combo box on my
form, I would like the MARKED field to get replaced with a YES for
each YES in the 6 Fields.

eg. if the user clicks COMMANDER, the MARKED field should get marked
with a YES for each YES in the COMMANDER field. Similarly with all the
other fields.

Can anyone please suggest how to go about doing this.

Thx & Best Rgds,
Prakash

Nov 12 '05 #3

P: n/a
NB
Prakash

As I understand it, you can use Update Query and run it from code to
do the update. To prevent the warning message from displaying you can
wrap the line of code that run the query (docmd.openquery or
db.execute)
with

docmd.setwarnings false
'run the updatequery here
docmd.setwarnings true

NB
Nov 12 '05 #4

P: n/a
ni******@lycos.com (NB) wrote:
As I understand it, you can use Update Query and run it from code to
do the update. To prevent the warning message from displaying you can
wrap the line of code that run the query (docmd.openquery or
db.execute)
with

docmd.setwarnings false
'run the updatequery here
docmd.setwarnings true


Actually with db.execute you don't need the setwarnings at all.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror command instead of
docmd.runsql. For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText

If you're going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. Otherwise wierd things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As
always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #5

P: n/a
NB
> Actually with db.execute you don't need the setwarnings at all.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror command instead of
docmd.runsql. For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText
What about docmd.openquery ? I thought saved queries are faster than
running SQL from code?

If you're going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. Otherwise wierd things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.

Does that happen even if you put docmd.setwarnings true back on
immediately after executing the query?

NB
Nov 12 '05 #6

P: n/a
ni******@lycos.com (NB) wrote:
Actually with db.execute you don't need the setwarnings at all.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror command instead of
docmd.runsql. For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText


What about docmd.openquery ? I thought saved queries are faster than
running SQL from code?


You can still run saved queries using the Execute. Just put the query name in place
of strSQL.
If you're going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. Otherwise wierd things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.

Does that happen even if you put docmd.setwarnings true back on
immediately after executing the query?


No. You need the setwarnings true immediately after the query and in the error
handling.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.