Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    Alright, I made a BUNCH of mistakes on the form I was working on, and I decided to basically go back and rebuild the form, just make everything s little more complicated.



    I am trying to build in some functionality that would allow the above validation to occur in the form's Before Update event (or something similar) so that I can include some kind of option to save or not save the new record based on whether or not all of the required fields are filled in.

    I apologize, this was VERY poorly planned on my part, but is it possible to include the above code in the BeforeUpdate event for the form and if there is one or more empty required textboxes, to redirect the flow back to the form with out saving what was input as a new record in the underlying table??

  2. #17
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Add the Cancel = True line after your message box:

    http://www.baldyweb.com/BeforeUpdate.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #18
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    AARRGH!!! for the most part, the code in the before update event works, but I am getting runtime error "3021: no current record". Normal errorhandling doesn't seem to work...

    I have a button on my form called "Submit" and I am just using

    "If Me.Dirty Then
    Me.Dirty = False
    End If"

    to cause the beforeupdate event to fire.

    in the beforeupdate event I am using

    "Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Len(Me.txtLast & vbNullString) = 0 Then
    MsgBox "Please Enter Last Name"
    Cancel = True
    Me.txtLast.SetFocus
    End If
    End Sub"

    the runtime error is being caused at the "Me.Dirty = False" line

    I am sure I am missing something very simple, but I have no idea what it is
    Should I be using some other command withint the "submit" button to trip the beforeupdate event

  4. #19
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you still get the 3021 error if you go to the next record using the navigation buttons (bottom of the form) and don't click on the submit button?

  5. #20
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your code saves the record (.dirty = false) so there is nothing to update anymore. Which means you can't cancel either. Even if not written that way, moving the focus from a record to the button will save the record if the controls in the record are bound to a table or updatable query. So your canceling of the update event (which should be written for the form if the controls are bound) needs to have the code I wrote before. The code can be used in a button click event if the form controls are NOT bound (or if they are bound to a temporary table from which they are moved to the final table(s) assuming all is well).

    HOWEVER there is a piece missing IF there is code afterwards to save a record, but in your case I think there is not (see red below).
    Code:
    Private Sub WhateverClickEvent()    'use your own sub name; e.g. button click event
        Dim ctl As Control
        Dim svList As String
    
        For Each ctl In Me.Controls
            If ctl.ControlType = acTextBox And ctl.Tag = "Reqd" Then
                If IsNullEmpty(ctl) Then
                    svList = svList & ctl.Controls(0).Caption & vbCrLf    'if Reqd and = "" (or is null) then add the label caption to a list)
                End If
            End If
        Next
    
        If svList <> "" Then
            MsgBox "You must supply a value for " & vbCrLf & svList    'if list is not empty, message
            EXIT SUB 'IF there is code afterwards that you use to save the records, like running sql statements or queries
        End If
    End Sub
    You can still use this code in the BeforeUpdate event, but you would swap Cancel = True for EXIT SUB if your controls are bound, AND you cannot use the .dirty on button click and expect to cancel a record update. That's why you got the error message.
    If you're still stuck after this and want to post a zipped copy of your db, I will take a look if I can open it with 2007 version.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #21
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    ssanfu:

    It is a form that I am using to enter new employees only, one-at-a-time. I have the navigation bars disabled as I only want the user to be able to see the data for the person they are currently entering.

    Micron:

    But the record isn't being saved: any data that I input on the form does not get written to the table before the runtime error occurs.
    maybe I am getting confused on the order of operations, but how can the record be saved BEFORE the form is updated?
    seems more "logical" to me (its a relative term) that the event occurs BEFORE the form is updated (ie: record saved) so, any code that happens IN the before update event would STOP the record from actually being saved. So, data validation is attempted, if it fails, the save operation is canceled... no?
    either way, I don't want to get completely lost in the woods on this. I wasn't able to do anything with it today. I will try adding your code to the before update event tomorrow, and if it doesn't work, I will zip a copy up here.

    thanks for the patience

  7. #22
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    OPs.zipHere is the DB I am working on. I have already spent way too much time on this one thing that is starting to drive me a little crazy.
    I appreciate you taking a look at it, let me know if you have any questions or anything
    Thanks

  8. #23
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Playing around, this avoids the error:

    DoCmd.Close acForm, Me.Name

    which will force the save anyway. You've also got a stray "End Sub" in there, plus named the IsNullEmpty module the same as the function, which can't be.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #24
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    I got all of the above fixed, and what is in there seems to work, BUT the code is written so that IF one or more of the fields that should be filled is left empty, the message box with the statement of what data is required does appear, BUT when I click on "OK", the form closes and whatever data was input correctly is saved to the table.

    What I am looking for (and I probably need to apologize for not stating this earlier, if I didn't... I have only been able to dedicate a small amount of time to this each day and there are 100 other things going on) is code that if one of the required fields is left blank, the message box would pop up instructing the user to complete whatever fields are blank, and when "OK" is clicked, focus is returned to the form and the record is NOT saved. I am trying to cancel saving the data to the form until all of the required data has been input.

  10. #25
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    A couple of other things I noticed.

    In table "tblCrewmembers", you have fields "First" and "Last". These are reserved words in Access and shouldn't be used as object names. Plus they are not very descriptive. First and Last What? Better would be "FirstName" or "FName".

    I would remove the look up FIELDS......

    The top two lines in EVERY code module should be:
    Option Compare Database
    Option Explicit



    In the BeforeUpdate event code, you will never validate the Rank and Unit combo box controls.
    Code:
    If ctl.ControlType = acTextBox And ctl.Tag = "Reqd" Then
    The control type index for a text box (acTextBox) is 109. But the control type index for a combo box is 111. So even if the combo boxes are empty/NULL, they will never be checked/validated.

    You could use
    Code:
    If ctl.Tag = "Reqd" Then
    or
    Code:
    If (ctl.ControlType = acTextBox or ctl.ControlType = acComboBox) And ctl.Tag = "Reqd" Then

  11. #26
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe I am getting confused on the order of operations, but how can the record be saved BEFORE the form is updated?
    Not saying that. I'm saying if you move off the record, you save it. Clicking any button will do that. If there is code in an event that prevents saving that record, THEN you can cancel the save, but focus will move back to the record.
    Code:
    If ctl.Tag = "Reqd" Then
    You can't just do this or you will get an error for controls that don't have a tag property. I think there is only one control type that doesn't have the tag property (can't recall which), so you could either exclude that with your If statement or include your used control types in your statement as was shown.
    I have company for the weekend since Wednesday night, so I'm sorry that I have not and can't dedicate more time to your issue until maybe Monday afternoon. Looks like you're in good hands for now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think there is only one control type that doesn't have the tag property
    I vaguely remember hearing about that, but I can't find the control that doesn't have the tag property.

    But this will work:
    Code:
    If (ctl.ControlType = acTextBox or ctl.ControlType = acComboBox) And ctl.Tag = "Reqd" Then

  13. #28
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Had a few minutes to myself, so I came back here to add a bit. Will have to back out as soon as they return!
    Re: control with no tag: neither can I now but I did get an error once when looping through a form controls collection. It probably happened to me in Access 2003, though I cannot find what that missing control could have been back then. I see that a web browser control does not have a tag property, but I've never used one so it could not have been that. Even when I look here, https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx which is supposed to be Office 2003, I don't see anything missing from the Applies To list that it could have been.
    BUT when I click on "OK", the form closes and whatever data was input correctly is saved to the table.
    Then you're not cancelling the BeforeUpdate event. I downloaded and am looking at your db. Right now, there is no call to the BeforeUpdate event, because the way you have it, the only way to attempt to update the record is to close the form. You might get an error message due to the ensuing cancellation after the code encounters Me.Dirty = false in the button click. If so, I'd add an error routine to trap it. Would look something like the following (NOTE I do not know the number you'd get. I used ???? for it. Suggest you comment out the IF error part, add the number and uncomment the block when you need it).
    Code:
    Private Sub btnSubmit_Click()
    On Error GoTo errHandler
    
    Me.Dirty = False
    DoCmd.GoToRecord , , acNewRec
    Exit Sub
    
    errHandler:
    Select Case Err.Number
    Case 3021, 3270
        Exit Sub
    Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
    End Select
    End Sub
    This click code will attempt to save the record but should cancel if req'd is missing. Works for me.
    P.S. - I really dislike the use of the input mask - you have to ensure you click only in the beginning of the field. My personal perference.
    You have phone 2 and state in the required loop but the label is not red to mark it as required. BTW, First/Last is not as descriptive a label as could be.
    Your submit code needs to clear the form and set it up for a new record, or else it is possible to alter the record just saved. The gotoRecord should fix that.
    Gotta run!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #29
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    Thanks for the help on this. I have had ZERO chance to work on this for the past couple days and I am not sure when I will be able to dig into it again. I will hang on to the info, but the whole thing is on the back burner until I get a chance to look at it.

    Thanks Again!!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-23-2015, 09:32 PM
  2. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  3. Replies: 4
    Last Post: 01-22-2015, 10:30 AM
  4. Passing criteria "NULL" or "IS NOT NULL" to a query
    By SgtSaunders69 in forum Forms
    Replies: 1
    Last Post: 12-24-2011, 02:22 AM
  5. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums