Validation Rule Help: Option Group VS Text box

[yhchen]

I have a form that I use to track the imcoming phone calls

I would like to set the field of a textbox "Student ID" as a required field when the value of option group is 1

(when the value of option group is 2 then "Student ID" is not a required field)

I am struggling how to write the validation rule for the textbox of "Student ID".... can anyone help....

Many thanks in advance for your help

 

[SOS]

Just use the form's Before Update event to do something like:

If Me.YourOptionGroupFrameName = 1 And Len(Me.YourStudentIDTextBoxNameHere & "") = 0 Then
    Cancel = True
    MsgBox "You need to fill in the Student ID", vbExclamation, "Entry Error"
End If

 

[missinglinq]

And make sure to note that SOS said in the Form's BeforeUpdate event, not the BeforeUpdate event of one of the control's mentioned.

 

[SOS]

And make sure to note that SOS said in the Form's BeforeUpdate event, not the BeforeUpdate event of one of the control's mentioned.

Thanks Linq for the reinforcement. I probably should highlight that myself when I say it (inevitably someone puts it in the control before update event and then it doesn't work like it should).

 

[yhchen]

Thank you SOS (and missinglinq)

You guys are the best!!

 

[SOS]

Well, out of the two of us, Linq is the best. I'm okay...

Glad it helped.

 

[yhchen]

Hello.... I encountered some problem....

The form has the following code:

Private Sub Form_BeforeUpdate()

If Me.Source = 1 And Len(Me.StudentID & "") = Null Then
    Cancel = True
    MsgBox "You need to fill in the Student ID", vbExclamation, "Entry"
Error ""
End If
End Sub

Private Sub SandN_Click()
On Error GoTo Err_SandN_Click


    DoCmd.GoToRecord , , acNext

Exit_SandN_Click:
    Exit Sub

Err_SandN_Click:
    MsgBox Err.Description
    Resume Exit_SandN_Click
    
End Sub

As the I set student id to be a text field, I used null instead of 0

then it comes up with this error message

The expression On Click you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
There may have been an error evaluating the function, event, or macro.

any idea....??

 

[SOS]

You don't use NULL! You use

Len(Me.StudentID & "") = 0

The LENGTH of something will never be NULL it either has a length or it is ZERO. Change it back.

 

[missinglinq]

In my experience this error usually pops when you have more than one sub/function with the same name. Frequently a developer will have an event coded that isn't working, so they will copy it, paste it and modify the code, meaning to rem out the original code, but forgetting to, which causes the error.

I have seen reports of it being caused by a developer naming a control, such as a command button, something like RecordExit. It's seems that RecordExit is a hidden function within Access and having a duplicate OnClick will pop the error. Since SandN seems an unlikely name for an Access function, I'd go with the first idea and check for duplicates. You should also, from Form Design, select SandN and goto Properties and make sure there's nothing in the Click Property.

Also, what is the line

Error ""

supposed to be doing?

 

[yhchen]

You don't use NULL! You use

Len(Me.StudentID & "") = 0

The LENGTH of something will never be NULL it either has a length or it is ZERO. Change it back.

Thank you SOS..I have done so but still not working...

In my experience this error usually pops when you have more than one sub/function with the same name. Frequently a developer will have an event coded that isn't working, so they will copy it, paste it and modify the code, meaning to rem out the original code, but forgetting to, which causes the error.

I have seen reports of it being caused by a developer naming a control, such as a command button, something like RecordExit. It's seems that RecordExit is a hidden function within Access and having a duplicate OnClick will pop the error. Since SandN seems an unlikely name for an Access function, I'd go with the first idea and check for duplicates. You should also, from Form Design, select SandN and goto Properties and make sure there's nothing in the Click Property.

Also, what is the line

Error ""

supposed to be doing?

It seems that this code is stopping the "SandN" bottom from working. If I removed the IF code, then the bottom works fine. Once I put the IF code back, it pops up with the same warning message....

the Error"" line was an .... errrr. error... I have amended back as the follows:

(you'll see I have event compromised to no warning message and it is still not working...)

Private Sub Form_BeforeUpdate()
If Me.Xource = 1 And Len(Me.MMUID & "") = 0 Then Cancel = True

End If
End Sub

Private Sub Addnew1_Click()
On Error GoTo Err_Addnew1_Click


    DoCmd.GoToRecord , , acNewRec

Exit_Addnew1_Click:
    Exit Sub

Err_Addnew1_Click:
    MsgBox Err.Description
    Resume Exit_Addnew1_Click
    
End Sub

 

[SOS]

What is this part:

If Me.Xource = 1

Is Xource (or Source) actually a number - Is it the actual frame of the option group? If not it needs to be the option group FRAME and not the option within the group.

 

[yhchen]

What is this part:

If Me.Xource = 1

Is Xource (or Source) actually a number - Is it the actual frame of the option group? If not it needs to be the option group FRAME and not the option within the group.

Hello herewith the screen capture... I am sure this will clear things out a little bit...

Thank you SOS...

 

[SOS]

Hello herewith the screen capture... I am sure this will clear things out a little bit...

Thank you SOS...

It might if I could see it. But wherever you uploaded it, it is blocked for me here at work. Just attach it as a file here if possible.

 

[yhchen]

2nd try!! Thank you SOS

 

[SOS]

Now, are you sure that the XOURCE (Enquiry From) is actually returning a 1? Put in a message box to see what comes up when you go to run that code. I'm not 100% sure that is yielding what you think.

 

[yhchen]

Now, are you sure that the XOURCE (Enquiry From) is actually returning a 1? Put in a message box to see what comes up when you go to run that code. I'm not 100% sure that is yielding what you think.

When the code is removed and the new record is added, in the table vew it did return a 1

when the code is added back... it pops up the same message again (would not appear the error message at all)

The expression On Click you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
There may have been an error evaluating the function, event, or macro.

 

[SOS]

Can you just post the database. It will be much faster in getting this sorted.

 

[yhchen]

the file is too big to upload to here....

I uploaded to my skydrive...

http://cid-5edd539792b3921e.skydrive.live.com/self.aspx/Public/db1.mdb

 

[SOS]

I'll probably have to download from home then as I can't access those at work. So, I'll try tonight but might have to do it over the weekend.

 

[yhchen]

thank you, sos

very much appreciated!!