Full Version: Preventing incomplete records
UtterAccess Forums > Microsoft® Access > Access Forms
I am at wit's end with something that should be simple.
My db allowed any of 5 users to edit records on any form.
I am trying to take it to a new level to:
*Control who can edit or add (don't care who views what)
*Make sure required fields are completed
*Allow a user to undo changes (abandon edits before save))
It's been a rocky road of hiding the navigation buttons and close button
I have a rock solid method for quizing the server, seeing who is logged on, looking up privileges in a people table, and making edit and add buttons visible or not visible. No problems here.
Kind folks on UA have helped me with good techniques for making backcolor red if the control is null and exiting the sub to preven saving the record.
I am chasing my tail around and around perhaps trying to over complicate things. I keep getting stuck in positions where I can't save and I don't have a button to allow me to exit. My code is growing and growing. I keep finding interactions that weren't there befor I made my previous change. (I know, welcome to VBA programming)
Stated more simply, all I want to do is prevent incomplete records and allow the user to abondon new records or edits. (I've got privileges nailed) I think this is something that every developer strives for and I am not trying to do antyhing exotic.
I think this will be a complicated reply for anyone, even if I have expressed myself properly. Can anyone point me towards a scheme or sample to model after.
I am trying to do this with the following buttons.
Add Record
Cancel Add
Edit Record
Save Record
Abandon, Dont Save
I think the button names speak about my goals. Posting my event coding would be a bit too much.
Seriously considering taking up basket weaving!
tnx for any comments
Already been there, won the tee-shirt and wore the hat, but my backet broke. It is always amazing to me how fast and how much code it take to make a dbase work right and the more you learn the more code that goes into the dbase. Well, you can use this line anywhere to save a record:
DoCmd.RunCommand acCmdSaveRecord
You can use this line to check for blanks:
If Len(Nz(.Your Name, "")) = 0 Then
Ousually have a msgbox under the Then and then a setfocus to the txtbox, or whatever, that is blank. I use these in the cmdButton, Close, in the OnClick event. With the above line you really don't need a formal Save cmdButton. I do turn off all of the navigation stuff and the max/min stuff and event the close. I like everyone running off the cmdButtons. Scroll bars gone, too. Well I think I may have given you a few ideas to play with, Hopefully it helps you out.
Jack Cowley
AddRecord - DoCmd.GoToRecord, , acNewRec
CancelAdd - DoCmd.GoToRecord, , acFirst (if you have default values you will need to do a Me.Undo)
Edit Record - Me.AllowEdits = True (set Allow Edits in form to No)
Save Record - DoCmd.RunCommand acCmdSaveRecord
Abandon - Me.Undo
o verify that all controls have data use the forms Before Update event to do this. There is sample code in Access help so put the cursor in the Before Update event and press F1.
Hope I understood what it is you want...
I will test the "nuggets" you have given me.
like the idea of one less button. Every time I add something I find myself working around undesirable behaviors. One less to wrestle with.
I am still hoping for other shared experiences.
There will be many more coming. I try to only use cmdAdd, cmdDelete or Cancell, cmdPrevious, cmdNext and cmdClose. Any others aare there to open forms and or reports. Here is a little cutie you can put in your forms OnCurrent event:
Dim recClone As DAO.Recordset
Set recClone = Me.RecordsetClone
If Me.NewRecord Then
Me.cmdNext.Enabled = False
Me.cmdPrevious.Enabled = False
Me!YourField.Value = Nz(DMax("[YourField]", "Your Table"), 0) + 1 'Incremented Numbering System
recClone.Bookmark = Me.Bookmark
Me.cmdPrevious.Enabled = Not (recClone.BOF)
If recClone.BOF Then
Me.cmdPrevious.Visible = False
Me.cmdPrevious.Visible = True
End If
Me.cmdNext.Enabled = Not (recClone.EOF)
If recClone.EOF Then
Me.cmdNext.Visible = False
Me.cmdNext.Visible = True
End If
End If
If you use the cmdNext and cmdPrevious buttons, this will stop your users from creating new blank records when they get to the last record and click next again. Just remember to use my name or you can can the cod eto suit your fancy. Good luck.
I had to leave this for the weekend. Someone told me to get a life and I realized I already had one. Painting, yard work, broken door lock, dog etc.
eoff (my comment your first post)
I am validating with a bunch of these:
If IsNull(Description) Or Description = "" Then
DisplayMessage "Enter a DESCRIPTION of this issue."
Me.Description.BackColor = 10921727
Exit Sub
End If
...works for me so this is OK
Jack (My comment your post)
You have made me realize that by using before update and not various cmdButtons I only need to do the validating If then statements once. I am still getting some funny interactions like using undo and then Access telling me that "You cancelled this in a previous action. I am not done but I am getting there.
Geoff (your second post)
The code you suggest I place in "on current" I have not tried yet. I will test it to see how it prevents new blank records. This is a problem I have not realized yet!!!!!!!
Ken Mac
Ken, you will find out soon the Jack is one of the sharper gurus around here. The procedure I gave you just takes things a little further and a little nicer. One last thing, Jack helped me modify the above procedure to what it is now. Looks like you are on teh right track, so have a good one.
Don't forget that you can also mark the column as 'required' in the table design. Any attempt to save a record with no data in a 'required' field will generate a reasonably-worded error.
Good point, does it SetFocus on the object with the missing data? I have never used required and was curious. Thanks.
That code to control visibility of cmdNext and cmdPrevious is pretty slick. I like it.

I have never understood the concept of clone or bookmark and here they are in use. A step forward. I don't understand things untill I wrestle with a need for them.

One question..... your reference to name "YourField" in the first If...Then. What is this? When I run it I would expect it to crrash on me until I change this name to suit something on my form. Works fine. I will play some more.

Others have claimed it is better to use our own Next, Pervious, first and last buttons and hide the navigation buttons. (not sure I need "first" and "last" in this application) I am going to run with this. Unfortunatly with the NavButtons goes the record count! This is useful.


Ken Mac

Once I thought I was wrong, but I was msitaken!
Edited by: kenmac77 on Tue Oct 19 7:20:08 EDT 2004.
Oh ya
rashed at
Me!YourField.Value = Nz(DMax("[YourField]", "Your Table"), 0) + 1 'Incremented Numbering System
What is triying to be done here. I see "'Incremented Numbering System". What field needs to be examined and why?
you must change YourField to the name of your field on the form and YourTable to the name of your table. The 1 at the end is what you use to increment, it can be 10 or 100 even. HTH.
I am missing something here.
On current is a record level event so I am confused as to which field should have one added to it. I see "on current" as the event when a group of controls (record) becomes current.
My situation is a table called tblIssues (very boring but necessary) my form is called frmIssues
I have a field called IssueID but it is autonumber, back in the table, I can't monkey with this.
Coments gratefully accepted
Is this to be a new field that I create to serve only to support this code?
Dahhha, now I see the light said the blind man. This line of code replaces the autonumber and is much more stable and reliable. Try this:
Me!IssueID .Value = Nz(DMax("[IssueID ]", "tblIssues "), 0) + 1
Then in the table, tblIssues, change IssueID from autonumber to number and it should remain as the PKey. Change the cmdButton to the above name and this should work very nce for you. Remember most reasons thing error or don't work is mistakes, especially in typing. Remember this goes in the form's OnCurrent event. When you open the form, watch the Next and Previous cmdButtons and there visiblity. HTH
I will try this. I have never ventured out of the box of Autonumber and PKey, ever!!!
I am afraid. Heavan knows what is lurking out there. Please tell me it is OK!
It appears that this will generate the next highest IssueID number. Will my subform appreciate this?
thanks thanks
It has been two years sense I last used the MS autonumber. The PKey is still the same. And your last statement is correct. Try making a copy of the dbase and then put the code in into the dbase. That way if any thing goes wrong, you can go back to the original. There are many way and methods of replaceing the old Autonumber, it is very unstable, I left it because of the trouble it caused me and I have never regreted it for a minute. Good luck, and enjoy the brave new world you are about to embark on, Autonumber free. HTH.
ong post!
I am out of the woods now.
I've added cmdFirst and cmdLast buttons to your scheme and I am enjoying the stability of avoiding the MS Autonumber. I chose to use "enable" rather than "visible" on the buttons.
I need to leave this corner of the project alone. I have been hammering it too hard. I will test it in about a week when my head clears!!!!!!!
Ken Mac
Thanks Geoff
and Cammi and Jack
Glad I could help. Good Luck. acclaim.gif
In my experience, there is absolutely no need and you should not use something other than autonumber for a PK. Why would you ever want to? My advice is to stick to autonumber for your PK since it should have no meaning to anyone but the Access program itself.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.