INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Form Buton to clear Y/N field in 5 tables.

Form Buton to clear Y/N field in 5 tables.

(OP)
I have a main form with 5 subforms. I use a yes/no field in each of the 5 underlying tables to manage my picks per subform.
EG I checkmark a few customer type in the first subform. I checkmark cust classes. etc.

However, I need to reset the 5 subforms to have an empty y/n when I am done.

I have a query inside EACH subform to allow me to clear or reset the y/n field.
That is 5 buttons that I hit. But I would like to consolidate into ONE Clear or reset button.

I inserted the sql that works for each button and tried placing them together (see below). But the One button method bombed out.
Anyway to do this? Thank You.

------------------------------------------------------------------------
Private Sub cmdClearALL_Click()

UPDATE tblCustType SET tblCustType.PickCusTypeID = Null;
UPDATE tblCusClass SET tblCusClass.PickCusclassID = Null;
UPDATE tblRep SET tblRep.PickRepID = Null;
UPDATE tblMailRegion SET tblMailRegion.PickMailRegionID = Null;
UPDATE tblReferredBy SET tblReferredBy.PickReferredBy = Null;

End Sub

RE: Form Buton to clear Y/N field in 5 tables.

"bombed out" that's the technical expression I am not familiar with... sad

Did you try something like this:

CODE

Private Sub cmdClearALL_Click()
Dim strSQL As String 

strSQL = " UPDATE tblCustType   SET PickCusTypeID    = Null"
DB.Execute strSQL
strSQL = " UPDATE tblCusClass   SET PickCusclassID   = Null"
DB.Execute strSQL
strSQL = " UPDATE tblRep        SET PickRepID        = Null"
DB.Execute strSQL
strSQL = " UPDATE tblMailRegion SET PickMailRegionID = Null"
DB.Execute strSQL
strSQL = " UPDATE tblReferredBy SET PickReferredBy   = Null"
DB.Execute strSQL

End Sub 

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Form Buton to clear Y/N field in 5 tables.

(OP)
Hi Andrzejek - I pasted in your code from above. I ran the main form button.
It sends me to debug at the following spot. It turned yellow.

Db.Execute strSQL

Any ideas? thanks much.

RE: Form Buton to clear Y/N field in 5 tables.

Try:
Docmd.RunSQL (strSQL, false)

instead.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Form Buton to clear Y/N field in 5 tables.

(OP)
Hi. I tried Andrzejek but it did not work. I do not know how to handle the idea of IDMF since I am not an SQL person.

I made a mini file for testing. If you don't mind. The item in question is in the On Click of
the red CLEAR ALL button on the form.

Thanks you. You both seem close to solving the matter.

RE: Form Buton to clear Y/N field in 5 tables.

(OP)
Got this Clear ALL button to work as follows. FYI. thanks alot.

Private Sub cmdClearResortID_Click()

Dim strSQL As String

strSQL = " UPDATE tblCustType SET PickCusTypeID = Null"
CurrentDb.Execute strSQL

strSQL = " UPDATE tblCusClass SET PickCusclassID = Null"
CurrentDb.Execute strSQL

strSQL = " UPDATE tblRep SET PickRepID = Null"
CurrentDb.Execute strSQL

strSQL = " UPDATE tblMailRegion SET PickMailRegionID = Null"
CurrentDb.Execute strSQL

strSQL = " UPDATE tblReferredBy SET PickReferredBy = Null"
CurrentDb.Execute strSQL

End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close