simple y or n text box help

[Gazza2]

Hope someone can help with this.

I have the following code in the beforeupdate event of a text box on a form

Dim response As Integer

Select Case Me.SupplierAdd1()

Case "y"
MsgBox "New record added", vbOKOnly

Case "n"
response = MsgBox("Are you sure you want to delete this record", vbYesNo)

If response = vbYes Then
'Cancel = True
Me.Undo
MsgBox "Record Deleted", vbOKOnly
(want to set focus to another control here)
End If
If response = vbNo Then
Cancel = True
End If

Case "D"

DoCmd.OpenForm "FrmDeliveryAddress"

Case Else
MsgBox "Y or N only", vbOKOnly
Cancel = True


End Select

What im having trouble with is that if i try to setfocus to a control in the highlighted area it wont let me and gives me an error saying :

Run-time error '2108'
you must save the field before you execute the gotocontrol action, the gotocontrol method, or the setfocus method.

I have also tried adding the line to the afterupdate event of the form and the control with no success.

Any help will be much appreciated.

Thanks
Gareth

 

[RuralGuy]

You should really be in the *AfterUpdate* event of your control rather than the BeforeUpdate event.

 

[NigelShaw]

Hi,

i would also probably set my code up this way-

Dim response As Integer

Select Case Me.SupplierAdd1()

   Case "y"
   MsgBox "New record added", vbOKOnly

   Case "n"
   response = MsgBox("Are you sure you want to delete this record", vbYesNo)

   If response = vbYes Then
      'Cancel = True
      Me.Undo
      MsgBox "Record Deleted", vbOKOnly
      Me.MyOtherObject.SetFocus
      Exit Sub
   Else
      Cancel = True
   End If

   Case "D"

   DoCmd.OpenForm "FrmDeliveryAddress"

Case Else
   MsgBox "Y or N only", vbOKOnly
   Me.SupplierAdd1.SetFocus
   Cancel = True

End Select

im guessing you have a box with an option to enter a letter "y", "n" & "d". have to set this up with a OnKeyPress to ensure ONLY these letters are entered?

regs

Nigel

 

[Gazza2]

Thanks for the help both.

Ruralguy i tried it in the afterupdate event and it would still update the record regardless of what i typed into the text box.

Nigel your code works if i enter it into the forms beforeupdate event.

i have changed the delivery address form to a subform but am now having trouble moving focus to the subform before going to the y/n text box.

i have tried the docmd.subfrmdeliveryaddres.setfocus event on the control before the subform with no success.

Any ideas

hope that makes sense

Thanks
Gareth

 

[NigelShaw]

Hi,

probably a schoolboy error

to set focus on form, you use

forms!MyForm!MyObject.SetFocus

to set focus to a subform, you use

forms!MyForm!MySubform!MyObject.SetFocus

changing "MyForm" & "MySubForm" to the relevant names you are using for your current form and embedded subform.

hth


Nidge

 

[Gazza2]

Thanks for the help

This doesnt seem to be working, would it make a difference if the control before it was a yes/no checkbox.

Thanks
Gareth

 

[RuralGuy]

Moving the focus from a MainForm to a control on a SubForm is a two step process.
SubFormControl.SetFocus
SubFormControl.FORM.ControlName.SetFocus

 

[Gazza2]

Thanks Rural guy tried that aswell still no joy.

This is really annoying now as the help you have given me should be working but im obviously doing something completely wrong and i can quite see where.

Maybe if i explain myself better you gurus may be able to see something i cant.

This main form is used to add supplier details and the subform is used to add other details.
The subform is hidden and will only show if the Yes/No checkbox is checked(this bit works)
The focus should then move to the first control on the subform( Problem area)
The code i have in the checkbox afterupdate event (at the moment) is

If Me.Text36 = "y" Then
Me.SubFrmDeliveryAddress.Visible = True
Me.SubFrmDeliveryAddress.SetFocus
Me.SubFrmDeliveryAddress.Form.Address1.SetFocus
'Forms!FrmAddtest!SubFrmDeliveryAddress.SetFocus
Else
Me.Text40.SetFocus
End If

Ive also tried

If Me.Text36 = "y" Then
Me.SubFrmDeliveryAddress.Visible = True
Forms!FrmAddtest!SubFrmDeliveryAddress.SetFocus
Else
Me.Text40.SetFocus
End If

Both of these give me a run-time error `2110`
Microsoft office access cant move the focus to the control
SubFrmDeliveryAddress

Thanks in advance

Gareth

 

[Gazza2]

Ok ive found out why i have the problem but not sure how to fix it.

The problem is with the code in the forms before update event.

If i remove that then the other code does as it should so i guess im back to the original question.

How can i have a text box only accept Y,N or any other letter i specify without updating the record until i answer the msgbox question.

Thanks for the help

Gareth

P.S. I said it was me that was doing something wrong lol