Total beginner lost with VBA and Macros (1 Viewer)

Hi All,

I am a total beginner with Access and everything I have done to this point is through reading / watching youtube vidoes and forums.
What I am trying to do is lock certain fields within my database once they have had data input.
I have attached an image of the fields i want to lock, also the vba code with what i have so far relating to the 'Rasied By' field (Opened By) and there is also a section of the database itself.

Username: neal
Password: neal

Also in addition to this, I cannot work out how to implement an auto email function? Once I have added all the relevant users to the database, I want Access to email the person the 'Incident' is Assigned to, to let them know that the have an action to complete.

Finally, I cannot get this to stop auto saving.
Once I exit the database i do not get a prompt asking if I wish to save (like excel), it instead saves anyway. I want the save function to rely on hitting the Save and New button.

Any pointers or anything I have missed please shout.

Nealyc85 said:

I cannot get this to stop auto saving.
Once I exit the database i do not get a prompt asking if I wish to save (like excel), it instead saves anyway. I want the save function to rely on hitting the Save and New button.
.

Click to expand...

This is the default action within a bound form in Access, and is awkward in the extreme to get around if you really can't trust your users to enter data into a form. You maybe need to ask yourself why wouldn't you want to save the data?

What you can do is validate the data before saving the record and give the user the option to finish correctly or abandon the record.

You can lock fields easily based on a tag value or simply the fact that it's no longer a new record - Your image isn't showing us which ones you are interested in locking, again you would probably want some method of allowing corrections in case of errors.

I'm not sure your table design is correct, I would have thought you want to have multiple records for the related actions to a incident, to allow each one to be followed up individually with a record of sign off against each action?

Just thoughts so far.

Thank you Minty, The boxes requiring locking are ALL below the gray line. So the only box left editable after input is the drop down 'Search I.R' box. (Red line now stuck in to make clearer.

As for allowing these to be editable, I would create a transparent box somewhere off to the right to allow admin to edit if something is majorly wrong.

If I can get the above to work, I do not need the auto save function turning off but we have a history of people 'accidentally' clicking and not realising what they have changed. This was to simply negate the boxes i wish to lock from being changed and saved.

Minty: I'm not sure your table design is correct, I would have thought you want to have multiple records for the related actions to a incident, to allow each one to be followed up individually with a record of sign off against each action?
That is the intent and the version I have that i am working on has this however, the file became too large to post with all the extra data
in, so i cut it down so the coding and setup was visible.

Okay - that makes sense. I would simply add a allow edits button that is available to all, but lock the record by default.

To lock those fields go into the properties on each control and add the word Lock to the Tag property.
Then replace the forms embedded macro on current event with this ;

This will lock all controls you tag on an existing record.

You can edit the tag property on multiple controls by simply selecting all the ones you want at once.

another way is to set the form allow edits property to false, then in the controls you want to be editable, in the got focus event set allow edits to true, and the lost focus event set to false again.

You are confused because you see a lot of primitive functions but you don't know how they fit together. That's OK, it happened to all of us. I'll give you an "overview" of how we approach what you described using more advanced methods. I'm NOT suggesting you try this right away, but I want you to know how you approach this when using more of the tools in the Access toolkit.

The way you stop something from saving in an unwanted way is to employ the BeforeUpdate event on a bound form. A useful parameter is built-in to the call linkage for that event. The "CANCEL" parameter is passed in to the event by reference, which means if you change the CANCEL value in the BeforeUpdate code, the changed value is returned to Access when you "Exit Sub" from that event. Here's how it fits in.

You said you wanted "SAVE" buttons and "NEW" buttons. That fits in with this approach.

First, you can use the command button "wizard" which will allow you to build a button to create a new record and another button that will save the current record. (You should also consider the UNDO action if you are going this way - yet another useful wizard.) The wizards will build a scaffold of code for you that will do the absolute minimum required to do the NEW or SAVE (or UNDO) functions. You click the button, the code will set you up with the basics.

In order to remember what is going on, you create software flags (Boolean variables) in the General Declaration area at the top of the form's class module. They will be used to track the states of the form, reminding you what has happened. The down-side of this approach is that you now have flags to be maintained. Not really that hard to do, but for a beginner, it is often hard to remember to do it.

Now you go into the button_Click routines to diddle with the code that the wizards built for you. You add code to set or clear your tracking flags so that you know the states of things. For example, clickling NEW would set a "new record" flag. Clicking SAVE would set a "save requested" flag. Clicking UNDO would also do certain things to the flags based on whatever flags you created for tracking things. If you allow editing of existing records, there is a Me.Dirty flag that remembers if some field on the form has been edited.

You are NOT limited to diddling with your own flags. Forms have these properties called the "Allowxxx" flags - AllowEdit is one example. You can set and clear these flags too. If you don't want folks to modify an extant record, you set AllowEdit to false. Then for a NEW record you would set it true.

Now in the form's BeforeUpdate and AfterUpdate event code, you have some work. You wanted to stop folks from "implied save" actions. To do this, in The BeforeUpdate event, you test the "save requested" flag.

If that flag isn't set, you can pop up a message box and ping your user for not using the SAVE button. At the same time, you set the Cancel variable (which is automatically part of the BeforeUpdate event call sequence) to TRUE. The default is that Cancel is FALSE so will NOT cancel the update.

What happens then is that if the user tried to exit without saving, the update is disallowed so the EXIT is cancelled. If they tried to navigate to a new record without first saving the old one, the implied save is disallowed so the navigation is cancelled. Therefore, without that "gatekeeper" you can't implicitly save the record.

If you have strenuous validation checking, it also goes in the BeforeUpdate routine, and in fact you can put some fairly extensive checks here. It all boils down to whether you are or are not ready to allow the update, and you use the Cancel flag to stop the update if you are not ready.

On the other hand, if the "save requested" flag was set and all validation checks allow it, you DON'T do anything to the Cancel that is part of the call sequence. This will allow the Update to occur. Since the flag is ONLY set by use of the SAVE button, that means that no other method will work.

Then in the AfterUpdate event you can reset the "new record" and "save requested" flags because after an update, neither of them are true any more. You also set or clear AllowEdit if you were using it. As an alternative, you can diddle with the "state" flags and form internal flags in the form_Current routine, since it will closely follow the AfterUpdate event. Might even be better there, since you can use _Current to initialize the flags.

NOTE: There is ONE hole in this method. If for some reason the user decides to shut down the machine and uses the Windows "Force blocking task to exit" option, what happens next depends on how you programmed the form. If you have a network drop or power outage that drops the computer connection, that also might have effects. However, in none of those cases would the form's main record be updated.

I don't expect you to do this right away but this should give you an overview of how it would be done. When you feel ready to tackle this, you can remember it and use it as a rough guideline of how to proceed.

But before you start on any coding adventure, it would be wise to clean up your object names. Do you really know what combo217 and check186 are? Will you remember next month? What do you think someone who takes over this app later will think of these as object names?

Once you change the name of a control, you will orphan any events that currently exist so you will have to locate that code and fix up the names. You can do this by using the replace option in the binoculars tool.

Access is a Rapid Application Development (RAD) tool. It helps you. If you want ultimate control and maximum code complexity, you can always use C. But if you are going to use Access, you should probably understand the "Access way" and go with the flow. In the case of forms, the "Access way" is that data always gets saved. That means that if you don't want a particular record to be saved, you have to stop it. You control the form save by putting your validation code in the FORM's BeforeUpdate event. If an error is found, you set the form's cancel argument to True and Access will not save the record. Here's a small example: