Solved Link between forms broken

[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.

 

[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.

 

[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.

 

[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