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

[cimoli]

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

 

[Andrzejek]

bombed out" that's the technical expression I am not familiar with... :-(

Did you try something like this:

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.

 

[cimoli]

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.

 

[Andrzejek]

Try:[tt]
Docmd.RunSQL (strSQL, false) [/tt]
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.

 

[1DMF]

Isn't it

CurrentDB.Execute SQL

OR

    Dim db As Database
Set db = CurrentDb
db.Execute SQL

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[cimoli]

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.

 

[cimoli]

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