Save button on bounded Form (1 Viewer)

[GPGeorge]

What The_Doc_Man and LarryE said....

And "...No there's none of that to consider"

Until we get a clear picture of the tables, there's little point in pursuing the "Save" aspect. The underlying problem probably stems from the design as currently implemented, rather than the form.

Actually, it's not uncommon to try to implement coding solutions to compensate for irregular table designs, which means we should have considered that in the beginning. It's time to sort that out.

 

[GaP42]

The

And when in practice - please tell me if this makes sense - I will have the two tables in a centralised Access DB alone( called it Base), with the main access dB, with the forms, linked to base via linked tables.. So that multiple people can use the mdb with a " first come, first save" functionality

No - I do not think so: you have a simple workflow ... someone submits a record/proposal, another reviews and approves the record. If your workflow is never to be extended to more levels then you may structure it as one core table with fields for capturing the needed details and with columns for submitted by and date of submission, and another for approved by and approval date (and an approver notes field). You can control who can edit what depending on role. if you want more flexibility consider Docs suggestion.
The database can be shared, using a split db - the back end data component is shared by multiple front end clients with the same set of queries, forms, reports.

 

[GPGeorge]

The

No - I do not think so: you have a simple workflow ... someone submits a record/proposal, another reviews and approves the record. If your workflow is never to be extended to more levels then you may structure it as one core table with fields for capturing the needed details and with columns for submitted by and date of submission, and another for approved by and approval date (and an approver notes field). You can control who can edit what depending on role. if you want more flexibility consider Docs suggestion.
The database can be shared, using a split db - the back end data component is shared by multiple front end clients with the same set of queries, forms, reports.

The key here is whether there will ever and always be two and only two approvers, not three, four, five, or more approvers for specific situations. The long-term headaches of a repeating column design usually appear after some time, but from then on they persist until the application is abandoned or overhauled. Only if you can gain a commitment that there will never be a third approver for any reason, can you sleep at night.

I'm cognizant that there is supposed to be a second table with two additional approvals, which really calls for additional review and explanation.

 

[Galaxiom]

When both columns are filled with two different usernames, then the dataset is inserted into another table, which will be exported at some point.

The status of a record should not be determined by coping records from one table to another. The tables are the same so they should be one table.

If you can be sure that only two approvers would even be needed then you could add two columns. The "approved" data are the records where both those columns are not Null.

Personally I would use a child table for the approvals so that any number of approvals and further information about them could be recorded.

 

[Pat Hartman]

@Kingz You say you are a beginner. then as the others have said, let Access be Access. You are using a RAD (Rapid Application Development) tool. It does things for you. You don't want to prevent the record from being saved, you want to make sure that only a "correct" record gets saved.

As the first reply told you, the form's BeforeUpdate event is your friend. Think of this event as the flapper at the bottom of a funnel. If the flapper is open, the record gets saved. If the flapper is closed, the record does not get saved.

Generally, your validation will report errors to the user and than use "Cancel = True" to tell Access to not save the record at this time. Setting this property leaves the form dirty so Access is always going to want to save it and every time the user does something that makes Access want to automatically save the record, the form's BeforeUpdate event will be executed and your validation code will run. If the errors have not been corrected, the record will NOT be saved and the user will not be allowed to close the form.

If you want to give the user the option to abandon his changes, then you can do that and you would then use "me.Undo" to backout all the changes. Access will then allow you to close the form and the changes are discarded.

You keep telling us you are getting errors and don't understand, well, we're good but we cannot help if you cannot show us your code and tell us the error you get or what is happening or not happening.

You seem to be confused regarding events. The database that goes with the video should help you to understand events and the order in which they fire and what triggers them. Once you understand what "triggers" an event, you will understand what type of code makes sense in each event. Code that you would put in the form's Current event (runs each time a form moves to a different record) wouldn't make sense in the form's Dirty event (runs as soon as you type a single character into any control on a form but only runs once).

Once a record is "dirty" (someone has made a change to at least one control), Access takes it as a personal mission to ensure that the record gets saved. You have no reason to fight this. You just need to put validation code in the form's BeforeUpdate event to ensure that all required fields are filled in and that data values that you can validate make sense. For example, you should ALWAYS validate dates for sanity. 1/1/204 is a valid date as far as Access is concerned but it is almost certainly a typo. Unless you have a form where you are entering historical data, most dates should be within a short time in the past or default to today. Some dates will be in the future. For example, if you have an expiration date, it is probably 1 month, 1 year, etc in the future.

 

[Kingz]

The key here is whether there will ever and always be two and only two approvers, not three, four, five, or more approvers for specific situations. The long-term headaches of a repeating column design usually appear after some time, but from then on they persist until the application is abandoned or overhauled. Only if you can gain a commitment that there will never be a third approver for any reason, can you sleep at night.

I'm cognizant that there is supposed to be a second table with two additional approvals, which really calls for additional review and explanation.

Well, to answer your question, with regards to the approval. I have two columns "1_Val" and "2_Val". When the 1st person edits something, his pers_ID is entered into column 1_Val, then with the help of the form, I can see which dataset has something in 1_Val, so that when I show that and I want to save, then the 4 eyes concept is fulfilled and I fill 2_Val as well as inserting the dataset into a final master table. Note, if the validator notices a mistake and can't approve but wants to amend, he has to press a button " approval declined", which merely deleted the entry in 1_Val for that dataset, and then he can amend the dataset.. When he saves, his persID will be in 1_Val, waiting for someone to approve it.

Which is why I want/need control of the saving.

 

[GPGeorge]

Well, to answer your question, with regards to the approval. I have two columns "1_Val" and "2_Val". When the 1st person edits something, his pers_ID is entered into column 1_Val, then with the help of the form, I can see which dataset has something in 1_Val, so that when I show that and I want to save, then the 4 eyes concept is fulfilled and I fill 2_Val as well as inserting the dataset into a final master table. Note, if the validator notices a mistake and can't approve but wants to amend, he has to press a button " approval declined", which merely deleted the entry in 1_Val for that dataset, and then he can amend the dataset.. When he saves, his persID will be in 1_Val, waiting for someone to approve it.

Which is why I want/need control of the saving.

None of which actually addresses the issue raised. You are quoting current policy, not the possibility that current policy can be changed at some point.

Is it possible that there will ever be more than 2 approvers required for the initial phase of the process?

Could a future change of policy require 3 initial approvers followed by 2 final approvers?

Can you guarantee that there will never, ever, be a change in policy that requires "five eyes" or "six eyes"?

That's the crux of database design in many similar situations. When you elect a non-standard table design, you lock your process into that non-standard approach in perpetuity. This table design is often referred to as a "spreadsheet style" which means it has two or more columns for the same type of data (approver 1, approver 2). In a spreadsheet you can add a third column, and a fourth column as things change.

In a relational database, which is what you create with Access, that is a non-standard and highly restrictive design approach that usually ends up causing extra work that could have been avoided.

I guess pounding on this point may seem like obsession if you don't understand the problem. It's just that over 25 plus years, this same conundrum has appeared again and again in questions like this one, and the resolution could be so easily implemented.

In the end, though, it's your project and you decide when and how to invest your time and efforts.

 

[Kingz]

No, it will always be two people.. It's called the four eyes principle..I made the two columns extra for that. What I'm asking is for a solution to this problem.. I'm open to ideas

 

[GPGeorge]

No, it will always be two people.. It's called the four eyes principle..I made the two columns extra for that. What I'm asking is for a solution to this problem.. I'm open to ideas

Okay. You know your business.

 

[Pat Hartman]

@Kingz Did you ever try using the form's BeforeUpdate event? You might want to read #25 again and try to understand what I am telling you about the form's BeforeUpdate event and WHY it is the solution to your problem?

We've been down the rabbit hole with your incorrect table design. No one likes the two field solution. They've told you why. You, the novice, have overruled the experts. OK. Move on to solving the original question and the answer which came to you in post #2 has been provided. You just need to understand the recommendation and implement it.