Conditional mandatory fields (1 Viewer)

[MiniD011]

Hi all!

First and foremost, hi, I am new here but have been having trouble with an issue and was unable to find a solution that I was able to get to work.

Basically I have a form which records primary and secondary reasons for people writing off money, and in the secondary reasons I have an option for 'other', in case any of the other options aren't applicable. There is also a comments section on the form which is optional.

My issue is, I want the comments section to become mandatory if the 'other' option has been selected. I don't want it mandatory all the time, and I don't want it disabled if 'other' isn't selected, but I need to force people to write at least something in there if they have selected other.

Any ideas? I tried variations on code I found from other places but was unable to get this to function, and I am not sure if this can be done with validation or something like that.

Thanks in advance for any help!

 

[pbaldy]

I'd use this method:

http://www.baldyweb.com/BeforeUpdate.htm

In your case testing the first field for "other" first.

 

[MiniD011]

Hi Paul,

Thanks for your response, I attempted to use this code (tailored to my database) and unfortunately didn't have any luck. I checked out a previous thread where you helped somebody with the same issue but I am still struggling. I don't come across any errors, it simply doesn't make the field obligatory.

Does anyone have any further ideas? I will happily post specifics relating to this database if necessary.

Thanks again in advance.

 

[pr2-eugin]

Is it possible to show the code you have placed in the BeforeUpdate event?

 

[MiniD011]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.SecondaryReasonForAdjustment = "Other (Detail in Memo)" Then
    If Len(Me.Comments & vbNullString) = 0 Then
    MsgBox "You must add a memo as you selected 'Other' for Secondary Reason"
    Cancel = True
    Me.Comments.SetFocus
    End If
End If
End Sub

Here is the code I have tried at the moment, I have placed this in the BeforeUpdate of the Form. The ComboBox SecondaryReasonForAdjustment has the option for 'Other (Details in Memo)' and when this is selected the TextBox 'Comments' needs to become mandatory.

Thanks again!

 

[pr2-eugin]

Are you sure that the ComboBox returns that String value? Not a Number?

On another note, To trigger the BeforeUpdate event the Form must be dirtied i.e some changes has to be made to the Form entry..

 

[MiniD011]

Aha! That is fantastic, as you said I had them as numbers so that is absolutely great. Thank you both for your help.

One final related point, when I click the 'New Record' button it displays the correct message, but once I click past that it comes up with a couple of prompts, firstly one saying 'You can't go to the specific record.' and a second, 'Action Failed' prompt.

I understand that this is because I can't navigate away from the page without filling in the comments section, but is there a way to just stop the macro once this has been established? So it just comes up with the MsgBox and nothing more?

Thanks again for the help, really appreciated!

 

[pr2-eugin]

Is this New Record button a custom button or the inbuilt Navigation control.. ?? What do you mean by...

but is there a way to just stop the macro once this has been established? So it just comes up with the MsgBox and nothing more?

 

[MiniD011]

The 'New Record' button is custom-made, but all it does is navigate to a new record so nothing complex.

What I meant was once it has been established that the 'Comments' box is mandatory and that the user hasn't entered anything in, when they try to navigate away can it deliver only the MsgBox, and not the other prompts that normally occur?

I hope that makes sense, and thanks as always.

 

[pr2-eugin]

Could you show the code behind the custom button? You might need to trap the Error which would just ignore this instance..

 

[MiniD011]

There is no code at present, I just have an embedded macro. I know I ought to use code but I am very inexperienced in Access at the moment and the simpler I can keep things the better.

Should I change this to code? I know it is probably a case of one being the easy way and one being the right way, so if it would improve my learning and the functionality then I will try and convert it to code instead.

Thanks!

 

[gemma-the-husky]

i presume what is happening is that the macro is failing, because of the code changes you just instituted,

You need to suppress the error message in the macro, but presumably only in certain cases, depending on the error code.

I would have no problem doing this in code - but I am not sure how to achieve this within a macro.

it might be a standar 2501 error - but I am not sure.

 

[MiniD011]

I have converted the buttons from Macros to VBA, the code for my button now is:

Private Sub Command27_Click()
DoCmd.GoToRecord , "", acNewRec
End Sub

Having done this I obviously get a different error message, Run-time error '2105': You can't go to the specified record.

What I am trying to do is have this error not pop up, and it just remain on the current record after displaying the personalised MsgBox. Does anyone know how I can do this?

Thanks again!

 

[pr2-eugin]

Try adding an Error handling Routine.. Something like..

Private Sub Command27_Click()
On Error GoTo errHandler
    DoCmd.GoToRecord , "", acNewRec
exitOnErr:
    Exit Sub
errHandler:
    If Err.Number <> 2051 Then Call MsgBox("Error (" & Err.Number & ") : " & Err.Description)
    Resume exitOnErr
End Sub

 

[MiniD011]

You, good sir, are a star. That has worked perfectly and I have had it implemented across various buttons throughout my database now.

Thanks again, and thank you for your patience, it is really appreciated!