Make a required field conditional. (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
42,976
That's fine. I'm not really the one who needs to know what you did. It is the other people who might find this thread later. They are going to have to download the original version and then download your corrected version and then try to figure out what you changed. Way too much and so no one would bother.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:40
Joined
Oct 29, 2018
Messages
21,358
That's fine. I'm not really the one who needs to know what you did. It is the other people who might find this thread later. They are going to have to download the original version and then download your corrected version and then try to figure out what you changed. Way too much and so no one would bother.
Ah, I see what you mean. I wasn't planning on being mysterious about the solution. I was just giving the OP a chance to figure out the problem by themself. I plan on coming back later to explain the details. For the benefit of others, there was nothing wrong with the code. So, I actually did not change anything in the code we suggested to use earlier. The problem was in the data. So, if anyone downloads the original file and the one I posted, jut compare the data in the same tables. Cheers!
 

Rmaster2022

Member
Local time
Today, 17:40
Joined
Apr 1, 2022
Messages
32
I still get the error message: Update or CancelUpdate without AddNew or Edit. HOWEVER, it works if I used the navigation button at the bottom--and not the combo box I have in the form header!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:40
Joined
Oct 29, 2018
Messages
21,358
I still get the error message: Update or CancelUpdate without AddNew or Edit. HOWEVER, it works if I used the navigation button at the bottom--and not the combo box I have in the form header!!
On the copy I posted above? Can you please give me a step-by-step instruction on how to duplicate the problem? Thanks!
 

Rmaster2022

Member
Local time
Today, 17:40
Joined
Apr 1, 2022
Messages
32
Hi. Sorry for the delay. Try your db now...
  1. I check the box “Authorized to Preside…”
  2. I go to the select combo box in the form header to select another person.
  3. I am asked if I want to save the changes. I indicate yes.
  4. Then it tells me I need to choose a church. I click OK
  5. I choose a church in the combo box. I then get the error message: Update or CancelUpdate without AddNew or Edit.
HOWEVER. If I use the navigation button on the bottom of the screen to choose another record, instead of doing step 2 above, I do NOT get the error message in step 5 above. Using this method, it all works, and I can go on!

I am willing to PM you the full db, if you want me to.

I appreciate all the time you have put into this. I don.t want to take advantage of all your time, so I understand if you would rather not continue.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
42,976
If the results are different depending on what causes the current record to be saved, the validation code is in the wrong event. It belongs in the form's BeforeUpdate event and you need to Cancel the event in order to prevent the record from being saved. Add a stop in the validation code so you can be sure that it is being executed regardless of what prompts the save.
 

Rmaster2022

Member
Local time
Today, 17:40
Joined
Apr 1, 2022
Messages
32
If the results are different depending on what causes the current record to be saved, the validation code is in the wrong event. It belongs in the form's BeforeUpdate event and you need to Cancel the event in order to prevent the record from being saved. Add a stop in the validation code so you can be sure that it is being executed regardless of what prompts the save.
My problem is I don't know VBA code. It is something I would like to learn, but haven't done it. Thus I don't know how to put a stop in the validation code. The code is in the BeforeUpdate event.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
42,976
Open the code module in design view. In the left margin on the "If" line, just click. You will see a dot. That is the stop. Save and close. Then run the procedure both ways (the way it works and the way it doesn't work) and step through the code. When the intrepretor gets to the line with the dot, execution will stop and you will be looking at the highlighted line of code. Press the next step (debug menu) and step one line at a time to see if the error message appears and if the cancel gets executed. Take notes or a video if you have that capability.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:40
Joined
Oct 29, 2018
Messages
21,358
  1. I check the box “Authorized to Preside…”
  2. I go to the select combo box in the form header to select another person.
  3. I am asked if I want to save the changes. I indicate yes.
  4. Then it tells me I need to choose a church. I click OK
  5. I choose a church in the combo box. I then get the error message: Update or CancelUpdate without AddNew or Edit.
HOWEVER. If I use the navigation button on the bottom of the screen to choose another record, instead of doing step 2 above, I do NOT get the error message in step 5 above. Using this method, it all works, and I can go on!

I am willing to PM you the full db, if you want me to.

I appreciate all the time you have put into this. I don.t want to take advantage of all your time, so I understand if you would rather not continue.
Hi. Thanks for the additional information. Yeah, okay, this time, I had to modify your code. However, it wasn't the code for the BeforeUpdate event that we have been discussing here that I had to modify. Instead, it was the code for your Combobox, which wasn't obvious until you provided the steps to reproduce the error.

Unfortunately, since I don't use macros, I converted your code to VBA. If that's not acceptable, please let me know, and I'll try to redo the same thing using your original macros.

Again, please take a look at the changes I made to the code for your Combobox to see what I did, which was simply to add an error handler.

Hope that helps... Cheers!
 

Attachments

  • testdb (3).zip
    51.8 KB · Views: 119

Rmaster2022

Member
Local time
Today, 17:40
Joined
Apr 1, 2022
Messages
32
Hi. Thanks for the additional information. Yeah, okay, this time, I had to modify your code. However, it wasn't the code for the BeforeUpdate event that we have been discussing here that I had to modify. Instead, it was the code for your Combobox, which wasn't obvious until you provided the steps to reproduce the error.

Unfortunately, since I don't use macros, I converted your code to VBA. If that's not acceptable, please let me know, and I'll try to redo the same thing using your original macros.

Again, please take a look at the changes I made to the code for your Combobox to see what I did, which was simply to add an error handler.

Hope that helps... Cheers!
It worked! I'm glad you did it in VBA. I'll learn something from it! The type in green, are those "notes," or are they part of the VBA. I ask as there are a few other forms I can use this in.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:40
Joined
Oct 29, 2018
Messages
21,358
It worked! I'm glad you did it in VBA. I'll learn something from it! The type in green, are those "notes," or are they part of the VBA. I ask as there are a few other forms I can use this in.
Hi. Glad to hear it worked for you. The text in green are considered "comments." They are ignored by the code but a good way to document what's happening. Good luck with your project.
 

Rmaster2022

Member
Local time
Today, 17:40
Joined
Apr 1, 2022
Messages
32
Hi. Glad to hear it worked for you. The text in green are considered "comments." They are ignored by the code but a good way to document what's happening. Good luck with your project.
Thanks so much!
 

Users who are viewing this thread

Top Bottom