Select Case Statement issue (1 Viewer)

[jimmy0305]

Hi All,
I have a select case statement and what I want do is, when the vbCancel is selected,
I want to go back to the subform control's original value.


any ideas?
Many thanks in advance.

Jim


Code:

Private Sub Command38_Click()
Dim ctl As Object
Cancel As Integer

Select Case MsgBox("Do you want to accept?", vbYesNoCancel + vbQuestion)

Case vbYes
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Record was saved"
DoCmd.GoToRecord , , acNewRec

End If

Case vbNo
For Each ctl In Me.frmEndorsementSubform.Controls
If ctl.Tag = "Clear" Then
ctl = Null
End If
Next ctl

Case vbCancel

Cancel = True
Me.Form!frmEndorsementSubform.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End Select

End sub

 

[KeithG]

Try Me.undo

 

[jimmy0305]

Hi Keith...
I tried that... It didn't work...

Is my Cancel as Integer declaration wrong?

Many Thanks..

 

[KeithG]

What error are you getting?

 

[KeithG]

Actually yeah it should be of a boolean data type

 

[missinglinq]

In point of fact, as I understand it, you can't simply Dim Cancel as Integer and then use it in one of the native Access event subs! Cancel can only be used in subs if it appears in the sub declaration, such as

Private Sub Form_BeforeInsert(Cancel As Integer)

or

Private Sub Form_BeforeUpdate(Cancel As Integer)

Also, you have a stray End If line here that needs to be removed

Case vbYes
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Record was saved"
DoCmd.GoToRecord , , acNewRec

[B]End If[/B]

Linq

 

[jimmy0305]

So I guess I can't use the Cancel=True in my code? since I am using a command button?
What I'm trying to do is to go back to the subform control's original value by using the Cancel=true.

Any suggestion how can I do this? Many thanks again...

Jim

 

[KeithG]

Check out formi. If you make updates to the record and hit the comman button you will be prompted if you want to save your changes. If you hit no the changes will not be made

 

[jimmy0305]

Thanks Keith,
I don't want to close the form if you select "No"... So, when I removed the DoCmd.Close...
An error occured and says "You tried to commit or rollback a trans. w/o first begining a transaction."

I would like to go back to the control's original value and add additional text on the control. Is that possible?

Many thanks again.. Jim

 

[missinglinq]

Keith's original suggestion of Me.Undo undoes any change that have been made, causing the previous data (if any) in the record to re-populate the record. I'm not sure exactly what you mean by

"I would like to go back to the control's original value and add additional text on the control." Perhaps you could explain a little more clearly.

Linq

 

[KeithG]

When the form opens the transaction should have started (begintrans). I just programmed the form to shut but as soon as you run the rollback method of the db workspace all changes will be dropped.

 

[ajetrumpet]

I have a select case statement and what I want do is, when the vbCancel is selected,
I want to go back to the subform control's original value.

Jim, maybe a method like the following would be easier to keep track of instead of your CASE statements (it is taken from my sample database):

Dim Message, Buttons, Choice

    Message = [I]"Your Message Here"[/I]
    Buttons = vbYesNoCancel
    Choice = MsgBox(Message, Buttons)

If Choice = vbYes
  [I](do what you need to do here)[/I]

ElseIf Choice = vbNo
  [I](do whatever else for this choice here[/I]

Else
  [I]set the control back to the initial value? (this is the Cancel option)[/I]

I noticed your control loop in the first post too.

If I were to say one thing about this (I'm just intruding here a bit, hope you don't mind!), I would say that there isn't enough information to provide you with the efficiency you need!

What do you mean by subform's original value? A subform is a pretty big control to be housing just ONE value! What do you mean by this? Do the subform CONTROLS hold values initially when it is opened? Do you want to store those values somewhere and retrieve them if the user changes his/her mind after changes have been made to the values? No offense, but there needs to be some specificity here! And since there isn't, any visual that you can provide would be a tremendous help to anyone and everyone!

 

[jimmy0305]

Thanks again Keith...

You're right Linq.... Sorry for not explaining it well... That's what I wanted to do... (Add additional text on the original value when vbCancel is selected. Also, Command38 button is on the main form.

My main form has patient's general info (e.i. last name, first name, DOB, etc...)
Subform contains patient's diagnoses, md's recommendations, etc...
on my main form, I have an exit button(Command38). So when the user clicked the
exit button, it will ask if he/she wants to accept all the info he/she entered. If let say,
he/she forgot to add something, if Cancel is clicked, I want the cursor to move back to the current value or info that the user just entered and not to erase or blanked out all the controls.

Adam... Thank you also... your approach is much better than mine... but I'm not really that proficient in access... could you give an example? Thanks alot guys for your help... I really appreciate it.

Jim

 

[missinglinq]

Not sure exactly what code you're using now, after clicking on Cancel in your messagebox, but this line should take you back to the last textbox to have focus, regardless of whether it's on the mainform subform.

Screen.PreviousControl.SetFocus

 

[jimmy0305]

That's the problem I have right now... Dunno what to do when user click Cancel.
I just thought that my code is right. ( setting the cursor to focus on the subform )
and then undo... but obviously, it's not working.

Can you still retain control's value when focus is lost? and then move to the last character on the controls value?

Any suggestions or ideas?

Thanks again.... Jim

 

[ajetrumpet]

      Case vbCancel
      
      Cancel = True
      [COLOR="Red"][B]Me.Form!frmEndorsementSubform.SetFocus[/B][/COLOR]
      DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
      End Select

End sub

Jimmy,

Isn't the code for subform controls supposed to be this:

Me!SubformContainerName.Form!controlname(.actionHere)

????

Per Bob's subform reference page:
http://www.mvps.org/access/forms/frm0031.htm