Solved Link between forms broken

[MajP]

You have code that really locks this form down. So you have to make allow additions to true for the subform. Your code sets it to false.
I added this in the edit button
Me.Generic_Training_Record.Form.AllowAdditions = True

You will have to figure out where and when to allow additions and edits. I think the subform will inherent the main form properties so you have to work around that. In other words I do not think you can set the main form to allow additions to false without it doing the same to the subform.

 

[Pat Hartman]

I'm not going to try to fix the database. Way too much needs changing. I'll start with the most important.
1. You are not using form level events correctly. The form's AfterUpdate event runs AFTER a record is saved so putting validation logic there is akin to closing the barn door after the horses have escaped. It is too late. The bad data has already been saved. Validation belongs in the form's BeforeUpdate event so you can prevent the data from being saved rather than just give a meaningless warning.
2. Unless your users are dumb as rocks, it is probably better to "catch" errors rather than attempt to lock down everything up front and then try to figure out what to unlock at what point. You are still not validating anything so all your locking is for naught. You are allowing bad data to be saved.
3. I don't see any way to add rows to the subforms.
4. Buttons don't work.
5. The simplest way to control subforms, is to Add code to the subform's BeforeInsert event. If the EmployeeID is not null, then it is OK to continue. If it is null, then you undo the change and cancel the update with a message that tells the user to add the employee first.
6. You are using multi-value fields - add a many-side table and manage the list yourself.
7. You are using table level lookups - get rid of them.

 

[MajP]

Sorry, I am with Pat on this one. This is a self-inflicted wound. You are struggling with some of the basics of good form design, but at the same time you have added a whole bunch of unnecessary complexity by adding complicated and superfluous features. I have never figured out why people feel the need for coding a Save feature in Access. This would be the same as having a teenager and adding to them a "Get Bored", "Ask for Money", or "Complain Incessantly" button. These things are going to happen automatically by multiple paths, and you have to go out of your way not to make it happen. You do not have to create those features.
You have bigger problems than worrying about locking a form down for edits and additions.

f instead you do what Pat says and check everything in the form's before update this design would get so much easier. In the before update you can ask them to continue or cancel an do data validation. If they did not mean to add or edit something then give them a final chance to opt out.

@Pat Hartman don't you have a thread with good examples of this?

For example this just adds complexity.

Private Sub Position_AfterUpdate()
    
    Dim c As Long
    c = DCount("*", "PositionT", "Position='" & Forms!jobtitlef.Form.Position & "'")
    If c > 0 Then
        If MsgBox("WARNING! A course with same name already exists. Enter a unique value.", , "Duplicate record alert!") Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
        Exit Sub
        End If
    End If
    
End Sub

So instead of checking before the record is saved (before update) and canceling, you allow a bad record to get saved, turn on and off warnings, then run code to remove it.

 

[Pat Hartman]

There are two videos that are worth watching. They talk about using the form's BeforeUpdate event and rarely, the control's BeforeUpdate event to validate data and prevent bad data from being saved. As Maj mentioned, Access takes it as a personal mission to always save every record you change. If you don't stop it, it will also save bad data. So it is up to you to recognize "bad" data and prevent that from being saved. You can never prevent them from entering bad data, but you can do a reasonable job of preventing them from accidentally saving it

Your app locks everything up but does not actually prevent anything bad from being saved so we're suggesting a change in philosophy that will be easier to understand if you take the time to watch the videos. There is also a sample database that lets you modify the code in the sample forms or even add your own custom forms so you can log the events as they happen so you get a better understanding of what trggers them and what they should be used for.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Your objective, to repeat myself, is to keep bad data from being saved, not to try to clean up after the fact.

 

[GPGeorge]

Sorry, I am with Pat on this one. This is a self-inflicted wound. You are struggling with some of the basics of good form design, but at the same time you have added a whole bunch of unnecessary complexity by adding complicated and superfluous features.

Spoiler: etc

I have never figured out why people feel the need for coding a Save feature in Access. This would be the same as having a teenager and adding to them a "Get Bored", "Ask for Money", or "Complain Incessantly" button. These things are going to happen automatically by multiple paths, and you have to go out of your way not to make it happen. You do not have to create those features.
You have bigger problems than worrying about locking a form down for edits and additions.

f instead you do what Pat says and check everything in the form's before update this design would get so much easier. In the before update you can ask them to continue or cancel an do data validation. If they did not mean to add or edit something then give them a final chance to opt out.

@Pat Hartman don't you have a thread with good examples of this?

For example this just adds complexity.

Private Sub Position_AfterUpdate()
   
    Dim c As Long
    c = DCount("*", "PositionT", "Position='" & Forms!jobtitlef.Form.Position & "'")
    If c > 0 Then
        If MsgBox("WARNING! A course with same name already exists. Enter a unique value.", , "Duplicate record alert!") Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
        Exit Sub
        End If
    End If
   
End Sub

So instead of checking before the record is saved (before update) and canceling, you allow a bad record to get saved, turn on and off warnings, then run code to remove it.

Early on in my Access career, I discovered recordsets as a way to append and update records to tables. For a brief period of time I set about implementing that technique everywhere in lieu of append and update queries. I had this new super power, you see.

Then I realized there is nothing heroic about adding wads of complex code to accomplish simple tasks, just because I had learned how to do it.

In other words, sometimes we fall in love with the idea of being Masters of VBA Coding™, to the detriment of just getting the job done in the most direct way possible. Creating simple, effective solutions which, perhaps not coincidentally save our clients a few hours of billed time, compensates for losing an opportunity to impress ourselves with our code.

 

[Pat Hartman]

The most dangerous new Access developers are those who are competent in a different development environment. They are not afraid of code and VBA is trivial as far as programming languages go so they just jump right into code rather than taking the time to understand the Access ecosystem. Access forms and to some extent Reports along with their controls have various event procedures and properties.

But, once you actually understand the "Access way", code will be your last option rather than your first. Integral functions, property settings, and queries are the starting points. I still write lots of code but it is concentrated around validation and very little is written to try to coerce Access into working the way I think the perfect application should work. Sometimes I have to make it behave: ) but usually I'm OK with how it works so I just take advantage of the options it offers me.

For newcomers, I strongly recommend downloading a list of Access VBA functions by category. An alpha list is next to useless if you don't know the name of what you want. With the category list, you go to the date section or the finance section and you have a small list of options so it is pretty easy to find what you need.

The larger task is understanding the event model. Access provides some documentation but I have never found it to be particularly helpful. As a framework, it is helpful to understand that all the events are actually "hooks" provided by the form or report's mainline. We never see this code. We cannot change it. It works the way it works and if you don't like it, you won't like Access at all. What it does is provide entry points in the event procedures were when certain things happen, we are allowed to add our opinion to what processing should take place. Do you want to highlight a control in a form to make it stand out as the data entry point? Use the GotFocus event of a control. Do you want to create a log record to recode an add/change/delete event, use the form's AfterUpdate event (with a little preprocessing in other events). Do you want to prevent bad data from being saved? Use the form's BeforeUpdate event. You can't stop a user from entering "red" when he meant "blue" but you can keep him from entering "brown" if that isn't in the list.

 

[Gasman]

Oh, you want to meet a member over on AF, he does nothing but complain that Access does not work the way he expects it too, after all his years in development.
Guy is a complete PITA

 

[nashaz]

Hi all

I have taken some thrashing from the experienced db programmers over the weekend In my defense, this is the first database I am developing andI'm building it as I learn from various resources. Reason I have not used BeforeUpdate anywhere in the db is because I have not seen anyone use it, either here or other resources. Although I do agree it is a neater solution. I am going to try and see how I can improve my db based on everyone's comments and get back. As always, any feedback is welcome and appreciated!

 

[GPGeorge]

Hi all

I have taken some thrashing from the experienced db programmers over the weekend In my defense, this is the first database I am developing andI'm building it as I learn from various resources. Reason I have not used BeforeUpdate anywhere in the db is because I have not seen anyone use it, either here or other resources. Although I do agree it is a neater solution. I am going to try and see how I can improve my db based on everyone's comments and get back. As always, any feedback is welcome and appreciated!

Please don't interpret feedback as "thrashing". We all started at the same place, and we've all made most of the same mistakes at least once. So if someone suggests there is another, and better, way to get the job done, that comes from knowing why that is important and wanting to help you learn from our mistakes. It can come across as negative to someone is totally invested in "doing this my way and don't try to tell me otherwise". Fortunately, most people get what is going on and take the information as given.

 

[Pat Hartman]

I have taken some thrashing from the experienced db programmers over the weekend

Please don't look at this as thrashing. No one thinks you did things incorrectly on purpose. Several of us spent quite a while with your app and sometimes we will tweak things and make it work but with your app, it was going to take way too much work to do something no one asked us to do. So, I gave you a list of things to look at and Maj prompted me to post a link to the videos I made about the BeforeUpdate event.

Your heart is in the right place. You KNOW you need to prevent bad data, you just were going about it the hard way

 

[nashaz]

Oh, I am not offended at the feedback at all. Apologies if I came across that way. It was meant to be a light-hearted comment I am fully aware that my db was not upto the mark, hence my admission that I was reluctant to share it in the first place. However, every single reply and suggestion from every one here is highly appreciated by myself since I know people helping me are seasoned and experienced db programmers, and taking time out from their lives to help me out. I am immensely grateful to you all!

 

[nashaz]

I'm not going to try to fix the database. Way too much needs changing. I'll start with the most important.
1. You are not using form level events correctly. The form's AfterUpdate event runs AFTER a record is saved so putting validation logic there is akin to closing the barn door after the horses have escaped. It is too late. The bad data has already been saved. Validation belongs in the form's BeforeUpdate event so you can prevent the data from being saved rather than just give a meaningless warning.
2. Unless your users are dumb as rocks, it is probably better to "catch" errors rather than attempt to lock down everything up front and then try to figure out what to unlock at what point. You are still not validating anything so all your locking is for naught. You are allowing bad data to be saved.
3. I don't see any way to add rows to the subforms.
4. Buttons don't work.
5. The simplest way to control subforms, is to Add code to the subform's BeforeInsert event. If the EmployeeID is not null, then it is OK to continue. If it is null, then you undo the change and cancel the update with a message that tells the user to add the employee first.
6. You are using multi-value fields - add a many-side table and manage the list yourself.
7. You are using table level lookups - get rid of them.

Re points:

7. I only put them in place to initially help me understand the links I was making better, however, I have removed the table level lookups completely now
6. Same for MVF. I have replaced it with a subform
5. I saw the Bad Data video 1 (couldn't find the second part) and I am going to implement that strategy in my db wherever I can
4. Yep. I will fix the db functionality first and then setup buttons. Based on all feedback, I now see some of them really are not necessary
3. I have removed all code setting "Allow..." properties on and off and form is working much better
2. Same comment as point 5. I would appreciate if you could please point me towards part 2
1. Probably the best example I have gotten. Made things much clearer in my mind. Thank you

 

[Pat Hartman]

The second video is in post #30 and the sample db is in #33