Make a required field conditional.

[Rmaster2022]

I have a form in which I have a check box to indicate whether a member is a ruling elder. Another field is a combo box with the names of churches. If the ruling elder box is checked, I want the church field to be required. I know very little VBA, so I copied one that I hoped would work:

Private Sub txtAuthorized_BeforeUpdate(Cancel As Integer)
If Len(txtAuthorized & vbNullString) > 0 And Len(txtChurchCombo & vbNullString) = 0 Then
MsgBox "You need to choose a church!"
Cancel = True
txtChurchCombo.SetFocus
End If
End Sub

When I try it, I get the following message: Run-time error 2108: You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.

What do I need to change?

To complicate matters, I have another before update event for the same form (There are other fields on the form) as follows (this does work!):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True Then
If MsgBox("Do you want to save the changes for this record?", _
vbYesNo + vbQuestion, "Save Changes?") = vbNo Then
Me.Undo
End If
End If
End Sub

How do I code both of these together to work on the same form.

 

[Ranman256]

I usu put all required field checks in a IsValidForm(),
then test for it if they try to close the form ,

Private Sub Form_Unload(Cancel As Integer)
if Not IsValidForm() then Cancel = true
End Sub

Public Function IsValidForm()
Dim vMsg
vMsg = ""
Select Case True
Case IsNull(txtAddr)
       vMsg = "Addr is missing"
   Case IsNull(txtPhone)
vMsg = "Phone is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, kREQD
IsValidForm = vMsg = ""
End Function

 

[theDBguy]

Hi. You could try something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If MsgBox("Do you want to save the changes for this record?", _
vbYesNo + vbQuestion, "Save Changes?") = vbNo Then
    Cancel = True
    Me.Undo
ElseIf Me.CheckboxName = True AND Is Null(Me.ComboboxName) Then
    Cancel = True
    MsgBox "Please select a church.", vbInformation, "Info"
End If

End Sub

Hope that helps...

 

[Rmaster2022]

I get an error message: Update or CancelUpdate without AddNew or Edit

 

[theDBguy]

I get an error message: Update or CancelUpdate without AddNew or Edit

Did you remove your other code and just used the one I gave you? It sounds like you may have multiple code conflicting with each other.

 

[Rmaster2022]

I removed the other code for the before update event

 

[Rmaster2022]

I deleted the beforeupdate event, and typed the code back in. I tried the the form, and checked the authorized box. It did ask me if I wanted to save the changes. I responded yes. Then it informed me that I needed to choose a congregation, which I did. That's when I get the erro4r message. When I clicked help, it took me to a page that headlined: "

You tried to call Update or CancelUpdate or attempted to update a Field in a recordset without first calling AddNew or Edit.​

 

[theDBguy]

I deleted the beforeupdate event, and typed the code back in. I tried the the form, and checked the authorized box. It did ask me if I wanted to save the changes. I responded yes. Then it informed me that I needed to choose a congregation, which I did. That's when I get the erro4r message. When I clicked help, it took me to a page that headlined: "

You tried to call Update or CancelUpdate or attempted to update a Field in a recordset without first calling AddNew or Edit.​

Try creating a simple form with minimum controls and just that one code and see if the problem persist. If so, you might consider posting a sample db with test data.

 

[Rmaster2022]

I created a new simpler form. This is the code in BeforeUpdate:

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)

If MsgBox("Do you want to save the changes for this record?", _
vbYesNo + vbQuestion, "Save Changes?") = vbNo Then
Cancel = True
Me.Undo
ElseIf Me.ElderAuthorizedPreside = True And IsNull(Me.ComboChurch) Then
Cancel = True
MsgBox "Please select a church.", vbInformation, "Info"
End If

End Sub

If I do check the "authorized" check box, AND select a church, it seems to work fine. However. If I check the checkbox, but do not select a church, I am asked if I want to make the change, then tells me to select a church, which I do, but when I leave that record, I get the error message: "Update or CancelUpdate without AddNew or Edit." When I click on help, it takes me to: "You tried to call Update or CancelUpdate or attempted to update a Field in a recordset without first calling AddNew or Edit."

I have a combobox in the form header I use to select a member's (person's) record, and in the detail section the two fields: a checkbox "Authorized," and a combo box to select a church. The only other event procedure is for the combobox to select a members record.

 

[theDBguy]

I created a new simpler form. This is the code in BeforeUpdate:

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)

If MsgBox("Do you want to save the changes for this record?", _
vbYesNo + vbQuestion, "Save Changes?") = vbNo Then
Cancel = True
Me.Undo
ElseIf Me.ElderAuthorizedPreside = True And IsNull(Me.ComboChurch) Then
Cancel = True
MsgBox "Please select a church.", vbInformation, "Info"
End If

End Sub

If I do check the "authorized" check box, AND select a church, it seems to work fine. However. If I check the checkbox, but do not select a church, I am asked if I want to make the change, then tells me to select a church, which I do, but when I leave that record, I get the error message: "Update or CancelUpdate without AddNew or Edit." When I click on help, it takes me to: "You tried to call Update or CancelUpdate or attempted to update a Field in a recordset without first calling AddNew or Edit."

I have a combobox in the form header I use to select a member's (person's) record, and in the detail section the two fields: a checkbox "Authorized," and a combo box to select a church. The only other event procedure is for the combobox to select a members record.

Perhaps it's time to upload a test db?

 

[Rmaster2022]

So, what is to be included in a test db? I'm warning you, my dbs aren't pretty!

 

[Rmaster2022]

I have attached a test db that has the form and I added some records. However, in this db when I check the checkbox "Authorized to Preside..." and do not choose a church, I don't get the message that I must choose a church.

 

[Rmaster2022]

You are checking the wrong field. Shouldn't you be checking the Ruling Elder value?

What I want is that if the "Authorized to Preside..." checkbox (txt authorized) is checked (true), then I want the church field (txtChurchCombo) to not be null, to become a required field. The church field to be conditionally required, if the "Authorized to Preside..." is checked.

 

[theDBguy]

I have attached a test db that has the form and I added some records. However, in this db when I check the checkbox "Authorized to Preside..." and do not choose a church, I don't get the message that I must choose a church.

Hi. Sorry for the delay. Try your db now...

 

[theDBguy]

@theDBguy , you're getting like arnel Use your words What did you change?

Can I PM it to you? I want to wait until the OP had a chance to take a look at their database file.

 

[theDBguy]

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]

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 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]

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.

 

[Rmaster2022]

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.