Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Valadation on a Form (Access 2000)

    I need help in getting a form to check for entries on only two controls (both are combo box lookups one a lookup table the other a value list). If the first control (submitted by) does not have an entry I want it to set focus back to that control. After that is resolved, then check the next control (department) and do the same thing. I want to trigger this before the form closes. I prefer that the validation be on the form and not in the table (field).
    I tried some code at the before update and then at the close event, both were ignored and the form just went ahead and closed. The acyual code looked like this

    If Me![Submitted By] = Null Then
    Msgbox"You need an entry in Opened By field!"
    Me![Submitted By].SetFocus
    EndIf
    If Me![Department] = Null Then
    MsgBox "Please make a Department selection!"
    Me![Department].SetFocus
    EndIf

    Can anyone tell me what I am doing wrong?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Valadation on a Form (Access 2000)

    The BeforeUpdate event of a form has an argument Cancel. You must set it to True if you want to prevent the form from closing:

    If IsNull(Me![Submitted By]) Then
    Msgbox "You need an entry in Opened By field!"
    Me![Submitted By].SetFocus
    Cancel = True
    Exit Sub
    EndIf
    If IsNull(Me![Department]) Then
    MsgBox "Please make a Department selection!"
    Me![Department].SetFocus
    Cancel = True
    Exit Sub
    EndIf

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Valadation on a Form (Access 2000)

    Thanks, I added in your suggestions. Unfortunately with the same result.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Valadation on a Form (Access 2000)

    You'll need code in the Unload event of the form to prevent it from closing. There is no way to cancel the close, you have to use Unload instead.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Valadation on a Form (Access 2000)

    Thanks that worked better. Now the next part, I have two fileds that I want to check. Here is how I have it codded now but it is akward.
    If IsNull([SUBMITTED BY]) Then

    MsgBox "Please make a selection in the opened by field!"

    Me![SUBMITTED BY].SetFocus

    Cancel = True

    End If



    If IsNull([DEPARTMENT]) Then

    MsgBox "Please make a selection in the Department field!"

    Me![DEPARTMENT].SetFocus

    Cancel = True

    End If
    Should I use an else statement between them? And now for the last part of the puzzel. I also have command buttons on the form that run a macro to send an email of the current record in txt format.
    The macro looks like this
    Line 1 Go to Record
    Object Type: Form
    Object Name: <Form Name>
    Record: New
    Line 2Go to Record
    Object Type: Form
    Object Name: <Form Name>
    Record : Last
    Line 3 Send Object

    Object Type: Form
    Object Name: <Form Name>
    Output Format: txt
    Line 4 Close.
    With the new code, when I use them I get an error message that telle the Object is not open.

    I probably did this wrong also.
    What I would like to do is have the app read the department and send an email to the appropriate recipients.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Valadation on a Form (Access 2000)

    Just posting bits of code without any context makes it difficult to know how to answer your question. Where exactly did you put the code you posted? If it is in an event procedure like a BeforeUpdate sub, post the entire sub, from the Private Sub part down to the End Sub. That tells the rest of us exactly what we're looking at instead of forcing us to guess.

    I can't answer your first question because I don't know if the two bits of code are in the same or different routines. I can't answer your second question because I don't understand it. Do you mean when you click the command button to send an email, you get an error message now? It may be because of conflicting events, but it's impossible to tell and it's next to impossible to figure out what your macro is supposed to be doing. It appears to be moving to a new record and then going to the last record in the form. I don't see how that would send the current record anywhere.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Valadation on a Form (Access 2000)

    Yes of course you are correct. The code is in the Unload form event. And it is the only code in the event. The purpose of the code is to check that 2 fields on a form have been filled in. Submitted By and Department. And if they are not then to cancel any other actions (ie email macro) until they are filled in. In the bit of code below, if both fileds are empty then it will run both parts and I get both messages at once and end up at the "department.setfous line". I would rather have it check one do it's thing then proceed to the next.
    Right now the form is designed with command buttons on it to send the record to a department email address in text format. I did not know how to send a record that had not been "saved" yet so I had it close the record on the form go to a new record then come back to the "saved" record then send it , then close the form. Very awkard yes? Each department has it's own command button. A better solution would be to have the app "read" the department field and send the current record through email to the appropriate destination.

    If IsNull([SUBMITTED BY]) Then
    MsgBox "Please make a selection in the opened by field!"
    Me![SUBMITTED BY].SetFocus
    Cancel = True
    End If



    If IsNull([DEPARTMENT]) Then
    MsgBox "Please make a selection in the Department field!"
    Me![DEPARTMENT].SetFocus
    Cancel = True
    End If
    Should I use an else statement between them?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Valadation on a Form (Access 2000)

    You can use something like

    If IsNull([SUBMITTED BY]) Then
    MsgBox "Please make a selection in the opened by field!"
    Me![SUBMITTED BY].SetFocus
    Cancel = True
    ElseIf IsNull([DEPARTMENT]) Then
    MsgBox "Please make a selection in the Department field!"
    Me![DEPARTMENT].SetFocus
    Cancel = True
    End If

    The ElseIf prevents the second part being run if the first condition was met.

    Or use something like

    If IsNull([SUBMITTED BY]) Then
    MsgBox "Please make a selection in the opened by field!"
    Me![SUBMITTED BY].SetFocus
    Cancel = True
    Exit Sub
    End If

    If IsNull([DEPARTMENT]) Then
    MsgBox "Please make a selection in the Department field!"
    Me![DEPARTMENT].SetFocus
    Cancel = True
    Exit Sub
    End If

    Here you exit the event handler after setting focus to the offending field and canceling the event.

    <img src=/w3timages/blueline.gif width=33% height=2>

    BTW, you can save the current record by using RunCommand acCmdSaveRecord. If you only want to save if it has been modified, use

    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •