DoCmd.GoToControl

[JohnLute]

The following code works except for the DoCmd. It returns that there is no
control by that name. I'm not sure how to correct this. Anyone have any ideas?

Thanks!!!

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Forms!frmRevisions!sfrmRevisionsProfiles.Form!txtProfileID) Then
Beep
If MsgBox("Item ID is required!", vbOKOnly + _
vbQuestion) = vbOK Then
DoCmd.GoToControl
"Forms!frmRevisions!sfrmRevisionsProfiles.Form!txtProfileID"
End If
End If

End Sub

 

[Danny Lesandrini]

John, why is your form/control reference encased in double quotes?
That would confuse the DoCmd.GoToControl call.

 

[Dirk Goldgar]

The following code works except for the DoCmd. It returns that there is no
control by that name. I'm not sure how to correct this. Anyone have any
ideas?

Thanks!!!

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Forms!frmRevisions!sfrmRevisionsProfiles.Form!txtProfileID)
Then
Beep
If MsgBox("Item ID is required!", vbOKOnly + _
vbQuestion) = vbOK Then
DoCmd.GoToControl
"Forms!frmRevisions!sfrmRevisionsProfiles.Form!txtProfileID"
End If
End If

End Sub

Use (object).SetFocus instead, and set the focus both to the subform, if it
isn't already there, and to the control on the subform. If this code is
running on frmRevisions, write:

Me!sfrmRevisionsProfiles.SetFocus
Me!sfrmRevisionsProfiles.Form!txtProfileID.SetFocus

If the code is running on the subform itself, not on the main form, just
write:

Me!txtProfileID.SetFocus

Incidentally, if this code *is* running on the subform itself, then your "If
IsNull" test can be a lot simpler and more efficient. Instead of this:

If IsNull(Forms!frmRevisions!sfrmRevisionsProfiles.Form!txtProfileID)
Then

.... write this:

If IsNull(Me!txtProfileID) Then

 

[Dirk Goldgar]

John, why is your form/control reference encased in double quotes?
That would confuse the DoCmd.GoToControl call.

No, GoToControl wants a string argument.

 

[Danny Lesandrini]

My bad ... but I never use the GoToControl command, so didn't know that off
the bat.

What I do know is that there are no function calls that will take that sort
of string and resolve it to a control on another open form. At best, the
string that the GoToControl command wants will have to be in the form
"txtFirstName", not
"Forms("frmContacts")!objPersonSubform.Form!txtFirstName".

 

[Dirk Goldgar]

My bad ... but I never use the GoToControl command, so didn't know that
off the bat.

What I do know is that there are no function calls that will take that
sort of string and resolve it to a control on another open form. At best,
the string that the GoToControl command wants will have to be in the form
"txtFirstName", not
"Forms("frmContacts")!objPersonSubform.Form!txtFirstName".

I agree.

 

[JohnLute]

Hi, Danny.

If I don't use them then Access returns "An expression you entered is the
wrong data type..."

 

[Danny Lesandrini]

John:

Yeah, Dirk pointed that out. It just confused me to see a form reference in
quotes. That won't work. The example in the Access Help file does
something like this ...

Dim ctl As Control

Set ctl = Forms("frmContacts")!txtFirstName
DoCmd.GoToControl ctl.Name

It also points out, as did Dirk, that you can use the SetFocus method.

ctl.SetFocus

I don't know for sure, but I suspect that the above code, using GoToControl,
will break unless the code is called from the form containing the control.
I could be wrong, but the SetFocus method would definitely work, as it's
hooked into the actual instance of the control itself.

 

[JohnLute]

Hi, Dirk!

Use (object).SetFocus instead, and set the focus both to the subform, if it
isn't already there, and to the control on the subform. If this code is
running on frmRevisions, write:

Me!sfrmRevisionsProfiles.SetFocus
Me!sfrmRevisionsProfiles.Form!txtProfileID.SetFocus

You are correct in assuming frmRevisions. I gave this a shot and it returns
2110 Access can't move the focus to the control sfrmRevisionsProfiles.

???

 

[Dirk Goldgar]

Hi, Dirk!


You are correct in assuming frmRevisions. I gave this a shot and it
returns
2110 Access can't move the focus to the control sfrmRevisionsProfiles.

???

Hmm, looking back at the code in your original post, I think I can see why.
You're doing this in the BeforeUpdate event of the main form (frmRevisions),
which means that the current record on that form must be dirty and has not
yet been saved (because it's *about* to be saved, hence the BeforeUpdate
event). But you can't set the focus to a subform if the main form's record
is dirty and can't be saved, as entering a subform *requires* that the main
form record be saved. If the main-form record is dirty, moving to the
subform will save it if it can be saved, but the change of focus will not be
permitted if the record can't be saved, thus giving error 2110.

Your code in the form's BeforeUpdate event is trying to move the focus to
the subform before the dirty record is saved, and that is not permitted.

What exactly are you trying to do here? It seems that you are trying to
make the completion of the subform required before the main form record can
be saved, and that is just not possible. The closest you can come is to
refuse to move to a new record or close the form until after the subform is
completed. This can be done with some slightly complicated code in the
form's Current and Unload events, and isn't totally foolproof (though it
does require a very obstinate fool). However, it may well be that
rethinking the process will give you a cleaner and more reliable solution.

 

[JohnLute]

Hmm, looking back at the code in your original post, I think I can see why.
You're doing this in the BeforeUpdate event of the main form (frmRevisions),
which means that the current record on that form must be dirty and has not
yet been saved (because it's *about* to be saved, hence the BeforeUpdate
event). But you can't set the focus to a subform if the main form's record
is dirty and can't be saved, as entering a subform *requires* that the main
form record be saved. If the main-form record is dirty, moving to the
subform will save it if it can be saved, but the change of focus will not be
permitted if the record can't be saved, thus giving error 2110.

Your code in the form's BeforeUpdate event is trying to move the focus to
the subform before the dirty record is saved, and that is not permitted.

Yes. Good grief. Where the heck is my brain today?

What exactly are you trying to do here? It seems that you are trying to
make the completion of the subform required before the main form record can
be saved, and that is just not possible.

Leave it to me to think that even the impossible is possible.

The closest you can come is to
refuse to move to a new record or close the form until after the subform is
completed.

That's more precisely the effect I was trying to get.

This can be done with some slightly complicated code in the
form's Current and Unload events, and isn't totally foolproof (though it
does require a very obstinate fool).

Being one of them I know how they are

However, it may well be that
rethinking the process will give you a cleaner and more reliable solution.

I think I'll leave it as is for the moment. I don't know that I'm up for
this just yet. As always, thanks for the response. Sorry to have wasted your
time!