Set focus problem

[lmcc via AccessMonster.com]

I created the code below to do the following:

1. Check to see if me.cboAgency is visible.

2. If it is visible, then check for null.

3. If null, then display a message requesting data.

4. Then set focus to cboAgency field so I can enter the Agency name.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.cboAgency.Visible = False Then
Else
If Me.cboAgency.Visible = True Then
If (IsNull(Me.cboAgency)) Then
MsgBox "You must provide the Agency name.",
vbOKOnly, _
"ETA - Required Field"
Me.cboAgency.SetFocus
Cancel = True
Exit Sub
End If
End If
End If
End Sub

The problem is that after the message is displayed and I hit the OK button,
the form closes instead of setting the focus to me.cboAgency.

Any ideas of what I am missing?

Thanks for your help!

 

[Dirk Goldgar]

I created the code below to do the following:

1. Check to see if me.cboAgency is visible.

2. If it is visible, then check for null.

3. If null, then display a message requesting data.

4. Then set focus to cboAgency field so I can enter the Agency name.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.cboAgency.Visible = False Then
Else
If Me.cboAgency.Visible = True Then
If (IsNull(Me.cboAgency)) Then
MsgBox "You must provide the Agency name.",
vbOKOnly, _
"ETA - Required Field"
Me.cboAgency.SetFocus
Cancel = True
Exit Sub
End If
End If
End If
End Sub

The problem is that after the message is displayed and I hit the OK
button,
the form closes instead of setting the focus to me.cboAgency.

Any ideas of what I am missing?

Under what circumstances is the BeforeUpdate event being raised? Does the
form close when you force a save by moving to a new record, or is it
happening when the user has attempted to close the form, but you don't want
the form to close because you don't want to save the incomplete record?

There's nothing in that code that ought to close the form, but if the record
is being saved because you are closing the form, then possibly you have
warnings turned off via SetWarnings, so you don't get the built-in warning
about "can't save the record, do you want to close anyway?" If that's the
problem, you have to locate the places in your code where you call
DoCmd.SetWarnings False, and make sure that every such case is balanced by
an inescapable DoCmd.SetWarnings True.

 

[lmcc via AccessMonster.com]

When I hit cmdOK button to save and close the form; it displays the message,
I hit the OK button on the message dialog box, and then it closes.

I do not have DoCmd.SetWarnings True. No error checking code added.

I created the code below to do the following:

[quoted text clipped - 28 lines]

Any ideas of what I am missing?

Under what circumstances is the BeforeUpdate event being raised? Does the
form close when you force a save by moving to a new record, or is it
happening when the user has attempted to close the form, but you don't want
the form to close because you don't want to save the incomplete record?

There's nothing in that code that ought to close the form, but if the record
is being saved because you are closing the form, then possibly you have
warnings turned off via SetWarnings, so you don't get the built-in warning
about "can't save the record, do you want to close anyway?" If that's the
problem, you have to locate the places in your code where you call
DoCmd.SetWarnings False, and make sure that every such case is balanced by
an inescapable DoCmd.SetWarnings True.

 

[Dirk Goldgar]

When I hit cmdOK button to save and close the form; it displays the
message,
I hit the OK button on the message dialog box, and then it closes.

What code do you have in your cmdOK button's Click event?

I do not have DoCmd.SetWarnings True. No error checking code added.

Do you have the statement "DoCmd.SetWarnings False" anywhere in your code?

 

[lmcc via AccessMonster.com]

No. Here is the code:

DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt

 

[Dirk Goldgar]

No. Here is the code:

DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt

I've tested, and I see you're right -- it does behave differently when you
close via DoCmd.Close versus clicking on the X button. Do this in your
cmdOK button's Click event procedure:

'------ start of code ------
Private Sub cmdOK_Click()

On Error GoTo Err_Handler

' Save record if dirty; raise error if it can't be saved.
If Me.Dirty Then Me.Dirty = False

DoCmd.Close acForm, Me.Name, acSavePrompt

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number <> 2101 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'------ end of code ------

You may have to edit the error number, 2101, that the code is set up to
ignore. That's the error I get on my Access 2003 system when the record
can't be saved.

 

[lmcc via AccessMonster.com]

Okay, Dirk.

I tried it and it doing what I want--not closing and setting the focus on
cboAgency field. But it gives me the error message: No current record. I
guess this is the code to ignore.

 

[Dirk Goldgar]

Okay, Dirk.

I tried it and it doing what I want--not closing and setting the focus on
cboAgency field. But it gives me the error message: No current record.
I
guess this is the code to ignore.

Hmm, that's a problem. Can you figure out what is causing that message? Do
you have code or macros in any other form or control events?

 

[lmcc via AccessMonster.com]

No, I have not been able to figure out what the problem is. It started doing
that when I added the code in--that is If Me.Dirty.... I change the error
message to ignore it. Here's the code:

Private Sub cmdOK_Click()

On Error GoTo ErrorHandler

If Me.Dirty Then Me.Dirty = False ' Save record if dirty; raise error
if it can't be saved.

DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt ' User wants
to save and close the form


CleanUpAndExit:
Exit Sub

ErrorHandler:
If Err.Number <> 3021 Then
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
End If
Resume CleanUpAndExit

End Sub

 

[Stuart McCall]

No, I have not been able to figure out what the problem is. It started
doing
that when I added the code in--that is If Me.Dirty.... I change the error
message to ignore it. Here's the code:

Private Sub cmdOK_Click()

On Error GoTo ErrorHandler

If Me.Dirty Then Me.Dirty = False ' Save record if dirty; raise error
if it can't be saved.

DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt ' User wants
to save and close the form


CleanUpAndExit:
Exit Sub

ErrorHandler:
If Err.Number <> 3021 Then
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
End If
Resume CleanUpAndExit

End Sub

This is a guess, but maybe your problem stems from your use of acSavePrompt.
This instructs Access to ask if you want to save *form design changes*, not
to save the record.

 

[Stuart McCall]

No, I have not been able to figure out what the problem is. It started
doing
that when I added the code in--that is If Me.Dirty.... I change the error
message to ignore it. Here's the code:

Private Sub cmdOK_Click()

On Error GoTo ErrorHandler

If Me.Dirty Then Me.Dirty = False ' Save record if dirty; raise error
if it can't be saved.

DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt ' User wants
to save and close the form


CleanUpAndExit:
Exit Sub

ErrorHandler:
If Err.Number <> 3021 Then
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
End If
Resume CleanUpAndExit

End Sub

This is a guess, but maybe your problem stems from your use of acSavePrompt.
This instructs Access to ask if you want to save *form design changes*, not
to save the record.

 

[Dirk Goldgar]

No, I have not been able to figure out what the problem is. It started
doing
that when I added the code in--that is If Me.Dirty.... I change the error
message to ignore it. Here's the code:

Private Sub cmdOK_Click()

On Error GoTo ErrorHandler

If Me.Dirty Then Me.Dirty = False ' Save record if dirty; raise error
if it can't be saved.

DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt ' User wants
to save and close the form


CleanUpAndExit:
Exit Sub

ErrorHandler:
If Err.Number <> 3021 Then
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
End If
Resume CleanUpAndExit

End Sub

But now you are not ignoring the 2101 error that will be raised if the
record can't be saved. If you're going to ignore both errors, do it like
this:

'------ start of code snippet ------
ErrorHandler:
Select Case Err.Number
Case 2101, 3021
' ignore these
Case Else
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf &
_
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
End Select
Resume CleanUpAndExit

'------ end of code snippet ------

That said, I am not sure why you are getting error 3021, and that troubles
me. I don't like to ignore errors, even errors due to bugs in Access, when
I don't know exactly what causes them.

 

[lmcc via AccessMonster.com]

I still can't figure why I get this error when this statement is included:
If Me.Dirty Then Me.Dirty = False. When I remove it, I don't get those
errors.

No, I have not been able to figure out what the problem is. It started
doing

[quoted text clipped - 24 lines]

End Sub

But now you are not ignoring the 2101 error that will be raised if the
record can't be saved. If you're going to ignore both errors, do it like
this:

'------ start of code snippet ------
ErrorHandler:
Select Case Err.Number
Case 2101, 3021
' ignore these
Case Else
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf &
_
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
End Select
Resume CleanUpAndExit

'------ end of code snippet ------

That said, I am not sure why you are getting error 3021, and that troubles
me. I don't like to ignore errors, even errors due to bugs in Access, when
I don't know exactly what causes them.

 

[Dirk Goldgar]

I still can't figure why I get this error when this statement is included:
If Me.Dirty Then Me.Dirty = False. When I remove it, I don't get those
errors.

Do you have any code (or macros) in other events of the form or its
controls?

 

[lmcc via AccessMonster.com]

Hi Dirk,

Yes. I started over again and didn't add the If me.Dirty statement and add
the code on the cmdOK button instead of the Form_Before Update and I think I
got it. Also, I think I over looked how it was written--meaning, in the old
code I had End If where I now have Else and then the DoCmd.Close statement.
Below is the code that I am currently using and it is working the way I want
it to. So far

Private Sub cmdOK_Click()

On Error GoTo ErrorHandler

If (Me.PageAgencyInfo.Visible = True) And (Me.cboAgency & "" = "") Then
MsgBox "Enter the Temp Agency name."
Me.cboAgency.SetFocus
Else
DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt ' User
wants to save and close the form

End If


CleanUpAndExit:
Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
Resume CleanUpAndExit

End Sub


Thank you very much for your help. Some times it helps to talk it over with
someone else.

 

[John Spencer]

This line
DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt
does not do what you expect.

The Save argument (acSavePrompt) means "Do you want to save changes to the
DESIGN of the form?" and not "Do you want to save the DATA entered on the form?".

From Access VBA Help
Argument Save
"Whether to save changes to the object when it's closed. Click Yes (save the
object), No (close the object without saving it), or Prompt (prompt the user
whether or not to save the object). The default is Prompt."

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[lmcc via AccessMonster.com]

Are you suggesting I write this statement differently. If so, how shall I
handle it?

This line
DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt
does not do what you expect.

The Save argument (acSavePrompt) means "Do you want to save changes to the
DESIGN of the form?" and not "Do you want to save the DATA entered on the form?".

From Access VBA Help
Argument Save
"Whether to save changes to the object when it's closed. Click Yes (save the
object), No (close the object without saving it), or Prompt (prompt the user
whether or not to save the object). The default is Prompt."

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi Dirk,

[quoted text clipped - 32 lines]

Thank you very much for your help. Some times it helps to talk it over with
someone else.

 

[John Spencer]

If what you want is to allow the user to decide whether or not to save the
DATA then you can leave that line as is. HOWEVER, it will not have any affect
on saving or not saving the DATA. It will simply close the form.

You will have to use a different technique to control whether or not the data
entered via the form gets saved. So what is your objective? I ask, because I
am not sure what you want to happen.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Are you suggesting I write this statement differently. If so, how shall I
handle it?

This line
DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt
does not do what you expect.

The Save argument (acSavePrompt) means "Do you want to save changes to the
DESIGN of the form?" and not "Do you want to save the DATA entered on the form?".

From Access VBA Help
Argument Save
"Whether to save changes to the object when it's closed. Click Yes (save the
object), No (close the object without saving it), or Prompt (prompt the user
whether or not to save the object). The default is Prompt."

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi Dirk,

[quoted text clipped - 32 lines]

Thank you very much for your help. Some times it helps to talk it over with
someone else.

 

[lmcc via AccessMonster.com]

I have a Cancel button so if they decide to undo changes, you hit the Cancel
button. OK button is there to say yes, everything is okay, go ahead and save
and close. I do not want to lose any of my changes or additions made.

If what you want is to allow the user to decide whether or not to save the
DATA then you can leave that line as is. HOWEVER, it will not have any affect
on saving or not saving the DATA. It will simply close the form.

You will have to use a different technique to control whether or not the data
entered via the form gets saved. So what is your objective? I ask, because I
am not sure what you want to happen.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Are you suggesting I write this statement differently. If so, how shall I
handle it?

[quoted text clipped - 22 lines]

 

[Dirk Goldgar]

Hi Dirk,

Yes. I started over again and didn't add the If me.Dirty statement and
add
the code on the cmdOK button instead of the Form_Before Update and I think
I
got it. Also, I think I over looked how it was written--meaning, in the
old
code I had End If where I now have Else and then the DoCmd.Close
statement.
Below is the code that I am currently using and it is working the way I
want
it to. So far

Private Sub cmdOK_Click()

On Error GoTo ErrorHandler

If (Me.PageAgencyInfo.Visible = True) And (Me.cboAgency & "" = "") Then
MsgBox "Enter the Temp Agency name."
Me.cboAgency.SetFocus
Else
DoCmd.Close acForm, "fdlgAssignmentDetail", acSavePrompt ' User
wants to save and close the form

End If


CleanUpAndExit:
Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered (cmdOK_Click)" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
Resume CleanUpAndExit

End Sub


Thank you very much for your help. Some times it helps to talk it over
with
someone else.

You're welcome, but I don't think you should consider the matter settled
yet. Your code will successfully prevent closing the form and saving the
record if the agency is not chosen, but *only if the form is closed by your
cmdOK button*. What if the user closes the form by clicking the "X" button
in the title bar? What if the user presses Alt+F4, or clicks menu item
File --> Close to close the form? What if the user presses Shift+Enter to
save the record without closing the form, or clicks menu item Records ->
Save Record?

If you want to prevent an incomplete record from being saved, then you
really need to be using the form's BeforeUpdate event to do the validation.
Everything else is secondary. The code we had worked up before was fine, so
far as it went, except that something as yet unidentified was causing error
3021 to be raised. If I were in your position, I would be addressing that
specific error to identify its cause. *Then* you could decide whether to
fix it or ignore it.

To that end, I have asked you repeatedIy to post any other code or macros
behind the form, but you haven't done so. There's no guarantee that having
that information would let me solve the 3021 error, but not having it ties
my hands as far as helping you goes. All I can do at this point is warn you
that the code you posted above doesn't meet your requirements in all cases.