Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

MS Access VBA -- Error in Code

Hi Everyone,

I am getting an error message when I try to execute a small amount of code. The code is just suppose to close the current form, but I am receiving error messages.

Below is the code.  (Also visible in attached file.)
--------------------------------------------------------------------------------------------------------------
Private Sub EventName_LostFocus()
'LOB_ProgramID.Value = "CO" & "-" & EventID_Outreach
 
 

 If (IsNull(Me.event_name) Or (Len(txt_SearchField_fname) = 0)) Then
   ' Msgbox "You need to provide", vbYesNo
   
   Dim LResponse As Integer
   
   LResponse = Msgbox("You first need to provide an Event Name first!" & vbCr & "Do you wish to continue", vbYesNo, "Continue Event Input")
   
    If LResponse = vbYes Then
    Text220.SetFocus
    EventName.SetFocus
    Else
        If LResponse = vbNo Then
        DoCmd.Close acForm, "frm_EventDetails", acSaveNo
        End If
    End If
   
   
   
   End If

  End Sub

-------------------------------------------------------------------------------------------------------------

The error is highlighted on the line containing :       DoCmd.close  ErrorDoCmd.docx
0
jonsuns7
Asked:
jonsuns7
  • 11
  • 10
  • 8
  • +1
4 Solutions
 
jonsuns7Author Commented:
Error message and Code highlighted in attached file.
0
 
Rey Obrero (Capricorn1)Commented:
try this revision

        If LResponse = vbNo Then

        me.undo

        DoCmd.Close acForm, "frm_EventDetails", acSaveNo
        End If


if you are validating data, might as well use the beforeupdate event of the control or form
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I would suggest moving all of your validation code to the Form BeforeUpdate event ....

mx
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
mbizupCommented:
I agree with mx.  This type of validation check is usually done in the forms Before Update event, with all controls being validated together.  This is more pleasant from a user interface standpoint.

However if there is a reason that you must do it at the control's level, use the control's before update event instead.

The code would change slightly:


Private Sub EventName_BeforeUpdate()
'LOB_ProgramID.Value = "CO" & "-" & EventID_Outreach
 
 

 If (IsNull(Me.event_name) Or (Len(txt_SearchField_fname) = 0)) Then
   ' Msgbox "You need to provide", vbYesNo
   
   Dim LResponse As Integer
   
   LResponse = Msgbox("You first need to provide an Event Name first!" & vbCr & "Do you wish to continue", vbYesNo, "Continue Event Input")
   
    If LResponse = vbYes Then
    'Text220.SetFocus
    'EventName.SetFocus
    Else
        If LResponse = vbNo Then
        me.undo
        cancel = true
        DoCmd.Close acForm, "frm_EventDetails"
        End If
    End If
   
   
   
   End If

  End Sub



0
 
Rey Obrero (Capricorn1)Commented:
do you also agree on my post?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
:-)
0
 
mbizupCommented:
cap - I'm very sorry about that.  :-(

I completely missed the last line of your post.

jonsuns7,

Please skip over my post entirely.

0
 
jonsuns7Author Commented:
I see what you are all saying about putting the code into the before update event.

But I am not getting the same result. I want the user to first enter data in the first field which is entitled "Event Name". If they do not, I want them to either go back to that field to enter that data or close out.

When I use this code with the before update event, it just moves them to another field.

Am I doing something wrong?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
At the table level, you need to set the Required property for that Field to Yes.

mx
0
 
Rey Obrero (Capricorn1)Commented:
<When I use this code with the before update event, it just moves them to another field. >

if the validation is not correct, set cancel=true, this will keep the focus to the control.
0
 
Rey Obrero (Capricorn1)Commented:
jonsuns7,

are you still getting the error when you added me.undo to your codes ?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I want the user to first enter data in the first field which is entitled "Event Name". If they do not, I want them to either go back to that field to enter that data or close out. "

Setting the Required property to Yes will force this to happen if they try to leave the Form where an edit is started ...

mx
0
 
jonsuns7Author Commented:
Sorry, Capricorn1  it's still doesn't do what you say.

It just goes onto the next field.

The way I am testing this, is that the field has a tab Index of 0, so it is the first field that the cursor is in when the form is opened.

I just want it to stay in place and to prompt the user put in valid input if they do not enter anything or to close out completely.
0
 
jonsuns7Author Commented:
Also, the field is required. The tables are in an SQL Server 2008 database. Still the prompt moves on to the next field.
0
 
Rey Obrero (Capricorn1)Commented:
<Sorry, Capricorn1  it's still doesn't do what you say >

post the codes that you are using..


also, you did not answer this

   are you still getting the error when you added me.undo to your codes ?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
If a new record is started (record is now Dirty) ... and user attempts to leave form w/o enter a value in that field, then an error should occur - trapped in the Form error event ...
0
 
jonsuns7Author Commented:
Here is the code as it stands:
-----------------------------------------------------------------------------------------------------------------

Private Sub EventName_BeforeUpdate(Cancel As Integer)
 
  If (IsNull(Me.event_name) Or (Len(txt_SearchField_fname) = 0)) Then
   
   Dim LResponse As Integer
   
   LResponse = Msgbox("You first need to provide an Event Name first!" & vbCr & "Do you wish to continue", vbYesNo, "Continue Event Input")
   
    If LResponse = vbYes Then
    Text220.SetFocus
    EventName.SetFocus
    Else
        If LResponse = vbNo Then
        Me.Undo
        Cancel = True
        DoCmd.Close acForm, "frm_EventDetails", acSaveNo
        End If
    End If
     
   End If
 
End Sub
-------------------------------------------------------------------------------------------------------------
Finally, the field is required as set in the initial table design on SQL Server
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
  If (IsNull(Me.event_name) Or (Len(txt_SearchField_fname) = 0)) Then
   
   Dim LResponse As Integer
   
   LResponse = Msgbox("You first need to provide an Event Name first!" & vbCr & "Do you wish to continue", vbYesNo, "Continue Event Input")
   
    If LResponse = vbYes Then
    Cancel = True
    Text220.SetFocus
    EventName.SetFocus
   
Else
        If LResponse = vbNo Then
        Me.Undo
        Cancel = True
        DoCmd.Close acForm, "frm_EventDetails", acSaveNo
        End If
    End If
     
   End If
 
End Sub
0
 
jonsuns7Author Commented:
Nope, still a nogo

What if I put it in the after update event ? I think I may try it at this point.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"after update event "
No .... because that is literally 'after the fact' ...

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Lets summarize ... exactly what is happening now ... relative to the last code I posted ... and exactly what you are trying to do ?
0
 
jonsuns7Author Commented:
DatabaseMX:

Here is a step-by-step  account of what is happening:

1) I open the form
2) The cursor is located in the Event_Name field (where it should be) ready for the user to enter data

-- Now if the user moves to a different field without entering data, it should go back to the original field Event_Name and prompt them with two choices: A) Enter an Event Name or B) Quit the application (close the form)

3) Currently, using the before update event, If I click on another field without entering anything, it just moves to that field.

When i used the lost focus event the first part worked.(of returning the user to the original filed on the form, but the second part (of closing the form does not.).

<Funny, it seems like this should be fairly straightforward but it still remains elusive.>
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is that the only bound field on this form ?

mx
0
 
jonsuns7Author Commented:
No there are a number of bound fields on the form.

If you have ever used ASP .net, they have validation controls to make sure that a field is not null before the user moves off that field. Unfortunatly, I do not see an equivalent solution in Access VBA.
0
 
Rey Obrero (Capricorn1)Commented:
jonsuns7,

try using the On exit event of the control Event Name

Private Sub EventName_exit(Cancel As Integer)
 
if me.eventname & ""="" then
  msgbox "enter event"
  cancel=true
end if
End sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, I really don't like using the Lost Focus etc ... it's just messy.

What I'm saying is ... IF ... the field is required (as it is), and the user enters data in one or more of the other fields - first - then if they try to leave the form and/or save the record ... the BU event should trigger ... or, more likely the Error (required field) will be rendered in the Form Error event.  That's how it should work.

mx
0
 
jonsuns7Author Commented:
Almost, but not yet.

It produces the same result I had when I used the Lost focus event. It puts the prompt back in place, but does not allow for a clean exit of the application.

(Note: If the user tries to exit the application by clicking the close button in the corner they get the same message box,if there is nothing in the Event_Name text box).

Sorry Everybody, Thanks for your help, but this one looks like it got the best of us.  I guess I will have to look at another solution.
0
 
Rey Obrero (Capricorn1)Commented:
jonsuns7,

are you using the exit event ?


post the codes that you have..
0
 
Rey Obrero (Capricorn1)Commented:
<(Note: If the user tries to exit the application by clicking the close button in the corner they get the same message box,if there is nothing in the Event_Name text box).
>

this can be handled separately.. post another question for this
see this for ref

http://access.mvps.org/access/general/gen0005.htm

0
 
jonsuns7Author Commented:
Private Sub EventName_Exit(Cancel As Integer)
If Me.EventName & "" = "" Then
  MsgBox "enter event"
  Cancel = True
End If

End Sub

-----------------------------------------------------------------------------------------------------
This code is tied to the control's exit event
0
 
jonsuns7Author Commented:
Used an alternative approach of checking all necessary fields prior to the record being saved. Not exactly what I had desired, but it still works well functinally. Thanks to everyone for their input.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 10
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now