Updating Yes/No Fields !

[Prakash Wadhwani]

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]

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

[Prakash Wadhwani]

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

[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

[Tony Toews]

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

[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

[Tony Toews]

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