Make a required field conditional. (1 Viewer)

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

 

[Pat Hartman]

Just FYI, the event sequence when you update a record and press the close button is:

beforeupdate
afterupdate
unload
close

Therefore, putting validation code in the Unload event is useless because that horse has already left the barn.

theDBguy's solution should work for you.

 

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

 

[Pat Hartman]

It is difficult enough to debug this way but it is impossible without seeing the code. Please post all the code in the procedure with the error and indicate which line fails --- every time. Don't just say I changed "a", repost. Thanks.

 

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

 

[Pat Hartman]

There must be code in some other event that is causing the problem. The BeforeUpdate code looks OK.

Try posting the entire class module for the form. Otherwise, a db with the form and whatever it takes to run it.

 

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

 

[Pat Hartman]

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

 

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

 

[Pat Hartman]

Is the church field defined as text? If it is, make sure that the AllowZeroLengthStrings property is set to No. The field might be a ZLS which visually is indistinguishable from null.

 

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

 

[Pat Hartman]

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