Help with set focus Error 2110

[Tony Williams]

I have a form with a subform. In the on error property of the sub form I
have this code
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim TitleStr As String
If DataErr = 3058 Then
Response = acDataErrContinue ' Don't display the default message
TitleStr = " No Quarter End Month!"
strMsg = "You can not enter a record without " _
& "completing the Qtr End Month" & vbCrLf & vbCrLf _
& "Do You Wish To Go Back " _
& "And Enter The Qtr End Month?"
If MsgBox(strMsg, vbYesNo, TitleStr) = vbYes Then
Forms!frmFDA!txtMonthlabela.SetFocus
Cancel = True
Else: Me.Undo
DoCmd.Close , frmFDA
End If
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If
End Sub

What I want to happen is if the answer to the question is YES I want the
focus to be set on the control txtmonthlabela on the main form. When I click
on YES I get Error 2110 Access can't move the focus to txtmonthlabela. Can
anyone help?

Thanks
Tony

 

[Andreas]

Cancel = True
What are you doing here?
This event does not have a "Cancel" argument.

Else: Me.Undo
This should really be:
Else
Me.Undo
DoCmd.Close , frmFDA
End If

Also, I think you could just write:
DoCmd.Close

Regards,
Andreas

 

[Tony Williams]

Thanks Andreas some bits left over after "cutting and pasting" The reason
the form name is there in the close is that this code is in the subform and
I want the main form to close. It seems to work oK but can't get the set
focus to work (see my message)
Tony

 

[Andreas]

Yes, subform, is this to do with the "other" post (Close Form?)?
If so, that explains a lot!

Regards,
Andreas

 

[Tony Williams]

Yes it is I've figured out the Close problem by adding the name of the main
form works fine it's now the set focus problem. This doesn't work
Forms!frmFDA!txtMonthlabela.SetFocus
Any ideas?
Thanks
Tony

 

[Andreas]

What action is creating the error you are trapping?
Have you fixed the rest of the code?
Please post your revised code.

Regards,
Andreas

 

[Tony Williams]

This is the code
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim TitleStr As String
If DataErr = 3058 Then
Response = acDataErrContinue ' Don't display the default message
TitleStr = " No Quarter End Month!"
strMsg = "You can not enter a record without " _
& "completing the Qtr End Month" & vbCrLf & vbCrLf _
& "Do You Wish To Go Back " _
& "And Enter The Qtr End Month?"
If MsgBox(strMsg, vbYesNo, TitleStr) = vbYes Then
Forms!frmFDA!txtMonthlabela.SetFocus

Else
DoCmd.Close , frmFDA
End If
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If
End Sub

The line Forms!frmFDA!txtMonthlabela.SetFocus creates error 2110 Access
can't move the focus to txtmonthlabela.
Any ideas? Thge control txtMonthlabela is on the main form frmFDA
Tony

 

[Andreas]

1)
Have made some minor alterations to code (for nicety).
See below.

2)
You have not told me what generates the error.

3)
What happens when you try to click in the mainform? If the record on the
subform can not be saved because of a data error, you can't go back to
the mainform, as moving between them will initiate a save of the
respective data.

4)
Sometimes it pays to set the focus to the form first, before setting the
focus to the control - some strange Access "feature":

Forms!frmFDA.SetFocus
Forms!frmFDA!txtMonthlabela.SetFocus

alternative:

Me.Parent.SetFocus
Me.Parent!txtMonthlabela.SetFocus

Revised code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim strTitle As String
If DataErr = 3058 Then
Response = acDataErrContinue ' Don't display the default message
strTitle = " No Quarter End Month!"
strMsg = "You can not enter a record without " _
& "completing the Qtr End Month" & vbCrLf & vbCrLf _
& "Do You Wish To Go Back " _
& "And Enter The Qtr End Month?"
If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
Forms!frmFDA!txtMonthlabela.SetFocus
Else
DoCmd.Close , frmFDA
End If
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If
End Sub

Regards,
Andreas

 

[Tony Williams]

Thanks Andreas, some background (sorry should have given you this first!)

The main form frmFDA only has one control txtmonthlabela. The subform called
subformFDA has a field txtmonthlabel which is used to link to the main form.
Both are key fields.

The user must input the txtmonthlablea control on the main form. If they
don't I want an message box. As there are no other controls on the main form
the user might go straight to the subform. So I'm using the subform to
capture the error because if they try and close the main form, (there is a
Close button on the main form), without entering the txtmonthlabela data
then txtmonthlabel on the subform wont be created. As it is a key field I
get an error message so I capture this error (which is 3058) and up comes my
message box. So far so good. If they answer No to the message box then the
form closes, no problem but if they answer yes then I want the focus to go
back to the main form and retain the data in the subform.

I tried using your suggested code for the setfocus and I still get the same
error message ie error 2110

Hope this explains the situation, I'm a newbie at VBA so don't yet fully
understand all the routines and at 60 I'm still learning!

Thanks for your help Andreas
Tony

 

[Tony Williams]

Andreas tried clicking on main form when error and I can't get focus on main
form as in your 3???
Tony

 

[Andreas]

Well, I think I'll still be learning when I get to 100

As far as I can see, the problem is as per my point 3 below.

- The user has entered data into the subform.
- A new record has been created.
- The primary key has not been filled into this record as it is not
available from the mainform.
- You are now trying to go back to the mainform.
- In order to do this, you have got to leave the subform.
- When you try to leave the subform, Access tries to save the record

And then there was thunder, and lightning, and ...

You have to verify that there is data in the mainform, before the user
enters data in the subform.

Have a look at the BeforeInsert event on the subform. Top of my head, I
think there is a Cancel argument. If so, use this event to check there
is data on the mainform. If so, great, if not, Cancel the event and put
the user back onto the mainform.

Regards,
Andreas

 

[Tony Williams]

Well I think you've struck oil!!! This is now my code
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_Insert
Dim MsgStr As String
Dim TitleStr As String
MsgStr = "You can not enter a record without " _
& "completing the Qtr End Date" & vbCrLf & vbCrLf _
& "Do You Wish To Go Back " _
& "And Enter The Qtr End Date?"
TitleStr = "Qtr End Date Must Be Entered"
If IsNull(Forms!frmFDA!txtMonthlabela) Or Forms!frmFDA!txtMonthlabela = ""
Then
If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Cancel = True
Forms!frmFDA.SetFocus
Forms!frmFDA!txtMonthlabela.SetFocus
Else
DoCmd.Close , frmFDA
End If
End If
Exit_Form_Insert:
Exit Sub
Err_Form_Insert:
MsgBox Err.Description
Resume Exit_Form_Insert
End Sub

And it seems to work OK is there anything about it you don't like?

I wish there was some way of us newbies thanking you guys in these
newsgroups for all the help and patience you give us. Although at 60 I don't
intend making a career out of this just do it for friends, I really don't
know where I would be without you. I know I can read the books (and I've got
plenty of them!) but I always find it difficult to find my problem in the
right book at the right time.

Thanks for sticking with me Andreas!
Tony

 

[Andreas]

Your thank you below does the job.
Been there, done that, and still doing it.

Regards,
Andreas