URGENT HELP!! CHECK BOX PROBLEM

[desikan]

I am totally confused by this check box problem that I am facing.

I have a a Main form with a continous subform. I have a check box on the sub form which has on after update following code :

Private Sub BOX_AfterUpdate()
Dim sMsgReturn
sMsgReturn = MsgBox("CLICK YES IF YOU WANT TO SHIFT THE SELECTED CARD AS SPARE AND CLICK NO IF YOU WANT TO TRANSFER TO ANOTHER NODE-Take care as your actions cannot be reversed ", vbYesNoCancel, "Select option yes or no or cancel&quot

If sMsgReturn = vbYes Then
MsgBox Me![QTY-WKG] & " NOS OF SPARE CARD -- " & Me![CARD NAME] & " TRANSFERRED TO " & Me![CARD NAME], vbOKOnly + vbInformation
DoCmd.RunMacro "macro37"
ElseIf sMsgReturn = vbNo Then
DoCmd.RunMacro "macro39"
Else
Cancel=True
End If
End Sub

My problem is that the vbyesnocancel starts when the check box is made yes to no and also when made no to yes.
Obviously I want the action to startonly when the box is made "True".

I am getting no clues to proceed further.
Any help will be appreciated.

I tried by keeping a button along with check box but I do not know how to prevent the user from clicking more than 1 check box at a time??

 

[DBAMJA]

If I am reading your question correctly, all you need to do is add an if statement in your sub:



Private Sub BOX_AfterUpdate()
Dim sMsgReturn

If BOX = -1 then
sMsgReturn = MsgBox("CLICK YES IF YOU WANT TO SHIFT THE SELECTED CARD AS SPARE AND CLICK NO IF YOU WANT TO TRANSFER TO ANOTHER NODE-Take care as your actions cannot be reversed ", vbYesNoCancel, "Select option yes or no or cancel&quot

If sMsgReturn = vbYes Then
MsgBox Me![QTY-WKG] & " NOS OF SPARE CARD -- " & Me![CARD NAME] & " TRANSFERRED TO " & Me![CARD NAME], vbOKOnly + vbInformation
DoCmd.RunMacro "macro37"
ElseIf sMsgReturn = vbNo Then
DoCmd.RunMacro "macro39"
Else
Cancel=True
End If
End If
End Sub

This way the only time that the code is exicuted is when the check box is true.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[desikan]

Hi DBAMJA,
I tried your modification and it worked fine. That was exactly what I was trying for the last 2 days and you have lifted a burden from my mind. Many thanks.

If I may trouble you little more, I just have 2 small queries :

(i) I do not understand exactly about your putting "If BOX = -1" ( I was trying with True/False etc)and also ending with "end If" instead of the normal code Else "some function" and then End If. If you can educate me little bit about your method, I will be grateful.

(ii) Also, when the user ticks the check box, and later on the vb prompt, chooses cancel, the tick mark remains which is of concern to me since the option vbyes/no run delete queries based on the record having tick as "Yes". If the user negligently keeps the tick and then closes the form, then it might be a problem when the form is opened again for performing this operation.

Of course I can remove the cancel option from vbyesnocancel to prevent this from happening but if there is any better method, do let me know.

Once again thanks for all the help.

 

[DBAMJA]

desikan:

To address your issues:

(i) I do not understand exactly about your putting "If BOX = -1" ( I was trying with True/False etc)and also ending with "end If" instead of the normal code Else "some function" and then End If. If you can educate me little bit about your method, I will be grateful.

With check box the values are 0 for False (not checked) and -1 for True (checked). The reason that I just ended with the End If and not Else then End If is there wasn't anything else that was going to run in the code if the check box is 0 so there is no real reason to put the Else.

(ii) Also, when the user ticks the check box, and later on the vb prompt, chooses cancel, the tick mark remains which is of concern to me since the option vbyes/no run delete queries based on the record having tick as "Yes". If the user negligently keeps the tick and then closes the form, then it might be a problem when the form is opened again for performing this operation.


Here you just need to add a line of code:

Else
Me.BOX = 0
Cancel=True
End If

The above added line will set your check box back to False.

Hope this helps.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[desikan]

Hi DBAMJA,
Your suggestion was very clever and it worked beautifully and I only had to add Me.Requery before the line If Box=-1.

Now I am getting into a small problem when vbno is selected as the option,

As you have noticed, vbno starts macro39 which in turn starts a form say "Form2" for transfer of certain card from the continous subform (say subform1) referred before.

If Form2 is completed properly by the user, then it triggers a delete query defined in Form2.afterupdate. The delete qwery is based on the box ticked in subform1.

If Form2 is not filled properly by the user,the delete query is not run and previous window returns with the help of following code in before update of Form2 :

If Len(Me!RING1 & "&quot = 0 Or Len(Me!NODE & "&quot = 0 Or Len(Me!DATE & "&quot = 0 Or Len(Me!SIV & "&quot = 0 Or Len(Me![QTY-WKG] & "&quot = 0 Then

MsgBox "ENTRY OF RING/SYSTEM/NODE/DATE OF TRANSFER/QTY BEING TRANSFERRED IS COMPULSORY WITH OUT WHICH THIS DATA ENTRY WILL NOT BE RECORDED", vbInformation + vbOKOnly
Cancel = True
End If

Now when user is returned to Main form1(carrying subform1)forcibly, the box still ticked and to prevent it, I feel we should add a line in before update code of Form2 before the line Cancel=True like Me!Form "Form1!subform1.box =0 and I know the syntax is getting horribly wrong.

At this point your valuable help is required.

Many thanks for patiently bearing with me through this thread. It has been a great education for me.

 

[desikan]

Well I just tried out following syntax in before update code of Form2 and I was able to succeed in making value of [box] =0 when Form2 is not saved :
Forms![FormName]!{subformname]![box].value=0

Now I am finding only one anomaly viz If Form2 is closed without any data entry being done, then value of [box] is not made 0. Perhaps this is because there was no update and hence the code did not work.

Can you think of any other options?

Many thanks

 

[DBAMJA]

You could try putting a bit of code to reset the check box in the Close event of the form. That way when the form closes, the check box will be reset.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.