Database validation rule

[sun_3]

I want to create a validation rule so that in my database table/form, in the 'Given by' field only correct teachers names can be entered in the form Mr/Ms/Miss/Mrs Patel

I would like the rule to ensure Mr/Ms/Miss/Mrs is to be entered followed by a surname.

Thank you in advance!

 

[plog]

Looking at the table in Design view, click on the 'Given by' field. Down below on the 'General' tab set the 'Required' property to Yes. Switch to the 'Lookup' tab, change the 'Display Control' to 'Combo Box'; 'Row Source Type' to 'Value List', enter the values that are acceptable into the 'Row Source' and then change the 'Limit To List' value to Yes.

 

[sun_3]

Looking at the table in Design view, click on the 'Given by' field. Down below on the 'General' tab set the 'Required' property to Yes. Switch to the 'Lookup' tab, change the 'Display Control' to 'Combo Box'; 'Row Source Type' to 'Value List', enter the values that are acceptable into the 'Row Source' and then change the 'Limit To List' value to Yes.

Thank you very much.. However, it could be a range of teacher's name that I do not know. Will this still work? I just want to make sure that the user must type in Mr/Mrs/Ms/Miss and then a name?

 

[plog]

Now this is starting to sound like a structural issue. Do you have the valid teacher's names in a seperate table?

 

[sun_3]

Now this is starting to sound like a structural issue. Do you have the valid teacher's names in a seperate table?

No, there is no such thing as valid teacher's name. The teacher's names are not anywhere in the database. I would just like to ensure that in this field on the database table/field the names are entered in the form I stated.. not allowing them to input say just 'Patel' but ensuring they input 'Mr Patel'. Every time a different teacher's name will probably be entered.

 

[plog]

Then lets back up a post:

However, it could be a range of teacher's name that I do not know

What does that mean then? And how do you think it affects the solution I proposed?

 

[nanscombe]

Normally you would have a separate field for title.

You would then use a combo box with the list of valid titles, Mr / Mr/ Miss etc, to populate it on the form.

If one value was "Pick a title" you could check for where the title was "Pick a title" and the name was not empty and flag it up as a problem, something like.

Private Sub Form_BeforeUpdate(Cancel as Integer)
  If (nz(Me.Title,"") = "Pick a title") AND (nz(Me.TeacherName,"") <> "") Then
    MsgBox "You need to add a title", vbInformation + vbOkOnly
    Me.Title.Setfocus
    Cancel = TRUE
  End If
End Sub