Requiring a text field with certain conditions

[tm1274]

I am trying to make a text field "RMargin" required if the value of the Option Group "ROption" is equal to "1" but blank if "ROption" is either option "2" or has no value selected. This is what I have so far but it conflicts with the search function, should I change from BeforeUpdate to something else to avoid conflicting with the search? The conflict is that the search doesn't work if the "RMargin" field is empty and the user clicks the search button, then the message box pops up preventing the search from completing.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.ROption = "1" Then
  If IsNull(Me.RMargin) Then
     MsgBox "You must enter a value in the RMargin Field!"
    Cancel = True
    Me.RMargin.SetFocus
  End If
End If
End Sub

Private Sub ROption_AfterUpdate()
Me.RMargin.Visible = Me.ROption = 1
Select Case Me![ROption]
    Case 1
            Me![RValue] = "No"
    Case 2
            Me![RValue] = "Yes"
    
End Select
End Sub


Private Sub SearchItemNumber_Click()
Dim S As String

S = InputBox("Please Enter Part Number", "Part Number", Item)
If S = "" Then Exit Sub

Me.Filter = "Item = """ & S & """"
Me.FilterOn = True

[Welshgasman]

Make your mind up.
Is ROption numeric or text?, you cannot have both.

[tm1274]

I modified my last comment to be less confusing by using numbers. I apologize for the confusion.

[Micron]

I think what was meant is that you have
ROption = "1" in one place (treating it as text) and ROption = 1 in another.

[Micron]

BTW, if you format your textbox as Y/N then all you have to do is pass the frame value to it - as long as you use 0 and -1 (F/T) for the options. So you could cut out some code.
Would help us if you adopted a naming convention so anyone can tell what type of control is coming after Me.
I'd get in the habit of Me. not Me! if I were you.

[tm1274]

Thank you Micron, I understand now. I am a newbie with no formal training just trying to learn on my own and did not know that using the quotation marks around the 1 was treating the number as text. I have removed the quotation marks from my code so it will treat the value as a number. I have also removed the select case afterupdate code because I need to simplify everything to get the code working better before adding to the complexity.

[tm1274]

I am having trouble with the code to make the RMargin textbox visible. There are 2 options in the group labeled yes with an option value of 0 and no with an option value of -1. When yes is selected the RMargin textbox becomes invisible as it should and when no is selected it becomes visible as it should but when I close the form and reopen it the yes option is still selected but the RMargin text box is visible, is there a way to make it stay invisible and clear any value that may be in the textbox as long as yes is selected?
This is my current code:

Private Sub SearchItemNumber_Click()
Dim S As String

S = InputBox("Please Enter Part Number", "Part Number", Item)
If S = "" Then Exit Sub

Me.Filter = "Item = """ & S & """"
Me.FilterOn = True


End Sub

Private Sub ROption_AfterUpdate()
Me.RMargin.Visible = Me.ROption = -1

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.ROption = -1 Then
  If IsNull(Me.RMargin) Then
     MsgBox "You must enter a value in the Margin for Retail Field!"
    Cancel = True
    Me.RMargin.SetFocus
  End If
End If
End Sub

[Micron]

You have the 0 and -1 backwards but that's probably not your problem now.

0 and -1 (F/T)

You also might have to use another event such as form open or current since changing visibility and such doesn't hold unless you do it in design view. What's in design should stick when you open the form unless you run code in one of those events. I'd use current if you're navigating between records in a single form view for sure.

[tm1274]

Hello Micron, Thanks for the advice. I am curious though regarding Me. vs Me! Is there a reason you prefer one over the other? I am trying to lean to code on my own with no formal training but using what I can find on the internet and trying to adapt those examples to use with my project. Is there a good resource you would recommend that I could review that may help me get a better grip on proper etiquette for coding in VBA?

[Minty]

For the Me. vs Me! my preference is to always use the dot unless it won't work, there is a fairly long and sensible discussion here: https://www.access-programmers.co.uk.../#post-1577095

Me. will give you intellisense, and automatically highlight typing errors as you go.
Me! does neither of those as it isn't pre-compiled.

[Micron]

IMO, Utter Access had a better thread on ! vs . but alas, they made a mess of everything when they updated their site and it's virtually impossible to find anything by search. So the link Minty provided would do and should explain it pretty well. I think I'd leave it at that in your research since there's a LOT of info and much of it is bad at one level or another.
About the only time I know of that you must use ! is in reference to the fields of a recordset using this syntax: rs!FieldNameHere, and I think, with TempVars if referring to a member of the collection but not if referring to a method or property.

[Minty]

It's not helped by a lot of sample code on some sites and blogs using the Me! syntax when it doesn't need to.
I find it really annoying.