469,643 Members | 1,341 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

Saving Record with "Conditions"

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.

Apr 29 '09 #1
18 2198
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. On Error GoTo Err_Form_BeforeUpdate
  3. If IsNull(Me![Type]) Then
  4.   MsgBox "You must enter an Incident Type from the Drop Down Menu in order to Save this Incident Report", _
  5.   vbExclamation, "Missing Incident Type"
  6.   Cancel = True: Me![Type].SetFocus: Me![Type].Dropdown
  7. ElseIf IsNull(Me![Incident Date]) Then
  8.   MsgBox "You must enter an Incident Date in order to Save this Incident Report", vbExclamation, _
  9.   "Missing Incident Date"
  10.   Cancel = True: Me![Incident Date].SetFocus
  11. ElseIf IsNull(Me![Incident Time]) Then
  12.   MsgBox "You must enter an Incident Time in order to Save this Incident Report", vbExclamation, _
  13.   "Missing Incident Time"
  14.   Cancel = True: Me![Incident Time].SetFocus
  15. ElseIf IsNull(Me![Report Date]) Then
  16.   MsgBox "You must enter a Report Date in order to Save this Incident Report", vbExclamation, _
  17.   "Missing Report Date"
  18.   Cancel = True: Me![Report Date].SetFocus
  19. ElseIf IsNull(Me![Report Time]) Then
  20.   MsgBox "You must enter a Report Time in order to Save this Incident Report", vbExclamation, _
  21.   "Missing Report Time"
  22.   Cancel = True: Me![Report Time].SetFocus
  23. ElseIf IsNull(Me![Location]) Then
  24.   MsgBox "You must enter a Location in order to Save this Incident Report", vbExclamation, _
  25.   "Missing Location"
  26.   Cancel = True: Me![Location].SetFocus
  27. ElseIf IsNull(Me![Reporting Officer]) Then
  28.   MsgBox "You must enter a Reporting Officer in order to Save this Incident Report", vbExclamation, _
  29.   "Missing Reporting Officer"
  30.   Cancel = True: Me![Reporting Officer].SetFocus: Me![Reporting Officer].Dropdown
  31. ElseIf IsNull(Me![cboSignature]) Then
  32.   MsgBox "You must enter a Signature of a Security Officer in order to Save this Incident Report", vbExclamation, _
  33.   "Missing Signature"
  34.   Cancel = True: Me![cboSignature].SetFocus: Me![cboSignature].Dropdown
  35. ElseIf IsNull(Me![cboInitial]) Then
  36.   MsgBox "You must enter an Initial of a Security Officer from the Drop Down Combo Box in order to Save this Incident Report", _
  37.   vbExclamation, "Missing Initial"
  38.   Cancel = True: Me![cboInitial].SetFocus: Me![cboInitial].Dropdown
  39. ElseIf IsNull(Me![txtDescription]) Then
  40.   MsgBox "You must enter a Description of the Incident in order to Save this Incident Report", vbExclamation, _
  41.   "Missing Description"
  42.   Cancel = True: Me![txtDescription].SetFocus
  43. Else
  44.   'Must be OK at this point!
  45. End If
  47. Exit_Form_BeforeUpdate:
  48.   Exit Sub
  50. Err_Form_BeforeUpdate:
  51.   MsgBox Err.Description, vbExclamation, "Error in Form_BeforeUpdate()"
  52.   Resume Exit_Form_BeforeUpdate
  53. End Sub
Apr 29 '09 #2
489 Expert 256MB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim ctl As Control
  3. For Each ctl In Me.Controls
  4.     If Me(ctl.Name).Tag = "required" And IsNull(Me(ctl.Name)) Then
  5.         MsgBox "Required field has missing data"
  6.         Cancel = True: Me(ctl.Name).SetFocus
  7.     End If
  8. Next ctl
  9. End Sub
Apr 30 '09 #3
489 Expert 256MB
Oops I made a mistake you would need to add another line between lines 6 & 7.
Expand|Select|Wrap|Line Numbers
  1. Exit Sub
Apr 30 '09 #4
8,800 Expert 8TB
Don's approach is much cleaner, more efficient, and contains the basic elements which you are looking for.
Apr 30 '09 #5
Thank you for the responses. I will check them today.
Apr 30 '09 #6
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!!!

Apr 30 '09 #7
32,203 Expert Mod 16PB
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).
Apr 30 '09 #8
32,203 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExit_Click()
  2.     Call DoCmd.Close()
  3. 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.
Apr 30 '09 #9
32,203 Expert Mod 16PB
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?
Apr 30 '09 #10
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!


Attached Files
File Type: txt Complaint DB Code.txt (11.6 KB, 416 views)
File Type: txt Current Database Code.txt (7.7 KB, 270 views)
Apr 30 '09 #11
489 Expert 256MB
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 :)
Apr 30 '09 #12
32,203 Expert Mod 16PB
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?
Apr 30 '09 #13
32,203 Expert Mod 16PB
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.
Apr 30 '09 #14
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!!

May 1 '09 #15
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.
May 1 '09 #16
32,203 Expert Mod 16PB
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).
May 2 '09 #17
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!
May 2 '09 #18
32,203 Expert Mod 16PB
You too Kari (says he belatedly).

Let us know how you get on.
May 5 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

24 posts views Thread by hjbortol | last post: by
9 posts views Thread by Maksim Kasimov | last post: by
3 posts views Thread by Colleyville Alan | last post: by
6 posts views Thread by Jason Huang | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.