Saving Record with "Conditions"

[Marilyth]

I am using Windows XP and Access 2003. I have searched through my HUGE book & the questions here, and found some items that "might" help, but unsure still. I am still getting versed on Access. I know there are expressions, however the order and manner in which they need to be phrased is still a work in progress.

I am looking for help creating some code/expressions. The database is summarized below:

1. The form is open for data entry to about 10-15 people.

2. I want them to enter information in required fields, AND have the database inform them when they have not entered all the information required BEFORE the database closes. (Currently when you try to "Save/Close" the form, it doesn't warn that the information will be lost.)

3. There are 14 fields required. Of those 7 require data entry, the other 7 are auto populated, or are the pull down list.

I have tried several different tactics to show which fields are required, but they hang up and don't allow me to go to the next field, thus having to shut down the database and starting again. I would like this to be able to be a one shot deal. The information is filled out, if a field is Null/Empty (not sure which is appropriate), then the Message Box would pop up indicating a field was left blank (I can put in a custom message telling which fields to look at), and then the adjustment can be made. Once all fields are filled properly, the form will save.

I think it would also be helpful to indicate a warning that if they try to "X" out of the database without saving, they will lose all data. That can also be placed as an "OK - go back and enter info" or "Close and do not save" option in the Message Box.

I just don't want the form to close and not save, especially when other fields have populated with descriptions, etc. and my co-workers come at me with torches & pitch forks.

I hope this is not as confusing as it appears.

Thanks for all the help.

Kari

[ADezii]

@Marilyth
Hello Kari. Here is some code that I personally use on my Main Form to check for the existence of Data in 10 Required Fields. This code is contained within the BeforeUpdate() Event of the Form. It checks for the existence of Data in each of the Fields in their Logical Sequence. If Data is not found in a Field, a Field specific and descriptive Message Box appears, the BeforeUpdate() Event is Cancelled, Focus shifts to the Field, and if the Field is a Combo Box it is dropped. Should Data exist in all 10 Fields, then the Record is Saved. Hope this helps.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
If IsNull(Me![Type]) Then
  MsgBox "You must enter an Incident Type from the Drop Down Menu in order to Save this Incident Report", _
  vbExclamation, "Missing Incident Type"
  Cancel = True: Me![Type].SetFocus: Me![Type].Dropdown
ElseIf IsNull(Me![Incident Date]) Then
  MsgBox "You must enter an Incident Date in order to Save this Incident Report", vbExclamation, _
  "Missing Incident Date"
  Cancel = True: Me![Incident Date].SetFocus
ElseIf IsNull(Me![Incident Time]) Then
  MsgBox "You must enter an Incident Time in order to Save this Incident Report", vbExclamation, _
  "Missing Incident Time"
  Cancel = True: Me![Incident Time].SetFocus
ElseIf IsNull(Me![Report Date]) Then
  MsgBox "You must enter a Report Date in order to Save this Incident Report", vbExclamation, _
  "Missing Report Date"
  Cancel = True: Me![Report Date].SetFocus
ElseIf IsNull(Me![Report Time]) Then
  MsgBox "You must enter a Report Time in order to Save this Incident Report", vbExclamation, _
  "Missing Report Time"
  Cancel = True: Me![Report Time].SetFocus
ElseIf IsNull(Me![Location]) Then
  MsgBox "You must enter a Location in order to Save this Incident Report", vbExclamation, _
  "Missing Location"
  Cancel = True: Me![Location].SetFocus
ElseIf IsNull(Me![Reporting Officer]) Then
  MsgBox "You must enter a Reporting Officer in order to Save this Incident Report", vbExclamation, _
  "Missing Reporting Officer"
  Cancel = True: Me![Reporting Officer].SetFocus: Me![Reporting Officer].Dropdown
ElseIf IsNull(Me![cboSignature]) Then
  MsgBox "You must enter a Signature of a Security Officer in order to Save this Incident Report", vbExclamation, _
  "Missing Signature"
  Cancel = True: Me![cboSignature].SetFocus: Me![cboSignature].Dropdown
ElseIf IsNull(Me![cboInitial]) Then
  MsgBox "You must enter an Initial of a Security Officer from the Drop Down Combo Box in order to Save this Incident Report", _
  vbExclamation, "Missing Initial"
  Cancel = True: Me![cboInitial].SetFocus: Me![cboInitial].Dropdown
ElseIf IsNull(Me![txtDescription]) Then
  MsgBox "You must enter a Description of the Incident in order to Save this Incident Report", vbExclamation, _
  "Missing Description"
  Cancel = True: Me![txtDescription].SetFocus
Else
  'Must be OK at this point!
End If
 
Exit_Form_BeforeUpdate:
  Exit Sub
 
Err_Form_BeforeUpdate:
  MsgBox Err.Description, vbExclamation, "Error in Form_BeforeUpdate()"
  Resume Exit_Form_BeforeUpdate
End Sub

[DonRayner]

If you didn't need to have a specific text for the message box being displayed you could just set the tag property for each control requiring data and then loop through the controls checking for them. In the following example I would set the tag to "required" without the quotes for each required text control.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
    If Me(ctl.Name).Tag = "required" And IsNull(Me(ctl.Name)) Then
        MsgBox "Required field has missing data"
        Cancel = True: Me(ctl.Name).SetFocus
    End If
Next ctl
End Sub

[DonRayner]

@DonRayner
Oops I made a mistake you would need to add another line between lines 6 & 7.

Exit Sub

[ADezii]

Don's approach is much cleaner, more efficient, and contains the basic elements which you are looking for.

[Marilyth]

Thank you for the responses. I will check them today.

[Marilyth]

I entered the code as indicated, however it selects the first control, pops up the box, and when OK is selected it closes the form. I want it to go back to the control so it can be entered so the info is not lost. When it does this the info is lost.

I'm not sure what's causing it, and I don't know the syntax to resolve the issue.

Any suggestions?

Thanks again!!!

@ADezii

[NeoPa]

@Marilyth
Actually, it seems pretty clear to me.

...but I'm catching up with the thread so give me a while to see what I can see (I don't want to repeat something someone else has already said).

[NeoPa]

Like ADezii, I like Don's approach. I would however, change all occurrences of Me(ctl.Name) to the simpler ctl. They both reference the same control objects.

This is a good use of the .Tag property, but if that's already used, there are other ways of indicating which controls should be so checked. Let us know if this is an issue for you.

For exiting a form, I always have a cmdExit Command Button control, but the code for this is :

Private Sub cmdExit_Click()
    Call DoCmd.Close()
End Sub

This is equivalent to the user clicking on the X.

Any special code I want to run when the user chooses to exit, is then placed in the Form_Close() event procedure. This way it never matters which way they try to close out. It always runs the same code.

[NeoPa]

Right. With all that out of the way I'm intrigued to know how this can behave as you say it does.

What process is the user going through to trigger these various events?

[Marilyth]

The answer to the question is we are entering information into fields as text and in command boxes (pull down boxes) that are required to save the record. The purpose of the action is to have a pop-up box indicate to the user that they have not entered information in a required field, and allow them the opportunity to go back to the form and enter the information.

I have a Save/Close button on the form. After I entered the long format indicated, and changed all the field names to match my fields, I did a test to see if it would pop up and allow me to go back and enter information I missed, however when I chose "OK" to go back and change the fields, the form closed and did not save any changes.

I suspect it is an issue with some other command, possibly relating to the Close/Exit, but I am not able to pin it down. I have attached the text. The "Current DB Code" has code WITHOUT the changes. The "Complaint DB Code" has the changes, plus additional code, of which I feel can be removed. There are several other command codes within the text, but if someone could pinpoint the commands that deal with this situation, that would be great.

Thank you again for the help. I'm still working on the other suggestion. I just don't want to screw that one up!

Kari

@NeoPa

[DonRayner]

@NeoPa
You know, I've been using (Ctl.Name) for sooooo long that I've never even though about shortening it. Learn something new every day :)

[NeoPa]

@DonRayner
It's funny how we do that sort of thing isn't it. It's a bit like saying "The child of the parent of X". It's only when we stop and think about it that we notice the strangeness. And who needs to stop and think about it when they've been doing it that way forever?

[NeoPa]

Kari,
Can I assume then that the operator presses the Close_Exceptions_Form Command Button then, when the form tries to close and notices changes and it prompts you with the option to Save Changes, you select Yes. At this point your Form_BeforeUpdate procedure intervenes and tells you that you have errors in your data. You select OK to continue (no other option available) which cancels the save?

At this point you wonder why the form closes and none of your changes are saved?

If this is the situation then you should understand that the Cancel has aborted the save (update) but the close has not been cancelled.

[Marilyth]

Your assertions are correct.

I think I understand what you're saying. Is it a manner of changing the order of the commands so they run individually/in a different order, so the form errors are identified, the message box identifies the errors, allows user to go back to form or exit without saving & once all information is correct allow the user to save & close the form for later use?

Thanks for the assist!!

@NeoPa

[Marilyth]

Here's the frustration now. I removed the docmd.close, and close using the "X" and it works perfectly! But, with this group of people there HAS to be a "Close & Save" button, or else the world will end (those of you who work with bureaucratic bs will understand).

So, now, how do I get a command button that will mimic the "X"??

So close!!! 2:30pm, 2.5 hrs until 5pm goal.

[NeoPa]

Sorry to miss this. I'm afraid I pulled an all-nighter Thursday over Friday, so I was done with any computers until this morning.

I suggest you look at the Unload event of the form. This can be cancelled (unlike the Close event).

[Marilyth]

It's alright. The goal was 5pm Friday. No big deal, I'm still working on the test database.

I will look at the code on Monday to see what I find regarding the "unload." I'm taking the weekend off to refuel my brain.

I'll see what I can find. I did find that my original database has an error as well, which is odd because I set it to its original parameters, but I'll fix that on Monday too.

Have a great weekend!

[NeoPa]

You too Kari (says he belatedly).

Let us know how you get on.