Access Validation Rule Help

[Starbird711]

Question: Access Validation Rule Help

Hello everyone. So I am new to Access and self taught. The depth of my knowledge comes from forums, youtube, and Microsoft help pages. I am in no way a programer, but am somewhat tech savvy.

With that in mind, I have created a database that captures information of students who participate in certain programs and their involvement in those programs. I have a student information table that is used to capture student information and a form that is used for the entry of that student information and to look up students. I have run into an issue with the LastName field of the student. This field is connected to the combo box that is used to look up the student and pull up all of the student's information if they are in the database.

The issue comes in when I look up a student, verify all of their information and then click the New Record button which creates a new record likewise a blank form for entry. In the table, I have set this last name field as required, so that blank records are not created and stored every time the new record button is clicked and no information is added (this was happening before I set these parameters). Unfortunately that did not solve the problem.

What Access has decided to do now, is take the primary key from the last student that was searched in the combo search box, and store that primary key number in the last name field of a new record that is created. It does not show the primary key filled into the last name field on the form, but when I view the table there are stored records with nothing but a primary key in the last name field. This is obviously not information that I want or need and I want to stop it from happening.

I though that if I set a Validation Rule for the last name field that the problem would be solved. This is where I need your help. I have searched for validation rules and found a few but none that meet my exact needs, and I am not sure how to write one myself. I need a validation rule that would all all characters but numbers. Some individuals have - or spaces in their last names as well as periods and such and I want these characters to be valid. If I have a validation rule that allows everything but numbers I think this would solve the problem while leaving my table reliable and valid, but I do not know how to write a validation rule like this or even if it is possible.

Sorry for such a long post, but I feel as though it is necessary to understand where the problem is coming from. Maybe someone will have a fix to the underlying problem instead of simply the cover-up.

Thank you so much!!!!!!!

 

[spikepl]

Access hardly ever does what you did not tell it to do. Your issue is here:

... This field is connected to the combo box

so you told Access to grab the value of the bound column of the combobox and stuff it into your last name field.

I smell some conceptual mixup here.

What are the field names and types in your table? And what is the row source in your combo -show the sql of the query for the combo

 

[Starbird711]

The control source of the combo box is the LastName field. When I created the combo box which is basically the search box, I clicked on the option "find a record on my form based on the value I selected in my combo box" and selected the LastName field.

The row source for the combo box is:
SELECT [Student Information].[Student ID], [Student Information].[FirstName] & " " & [LastName] AS Expr1 FROM [Student Information] ORDER BY [Student Information].[FirstName] & " " & [LastName];

The Field Names are:
Student ID - AutoNumber (The primary Key)
LastName - Text
FirstName - Text
FSUID - Text
FSUE-Mail - Text
AlternativeE-Mail - Text
PhoneNumber - Text
Graduation Year - Number
Gender - Number
OneFSU - Yes/No
Expected Degree - Text

 

[spikepl]

The control source of the combo should be Student ID. If you told Access that it is Last Name then that is where the bound value of the combo - Student Id - goes, for no purpose.

Do not use spaces or non alpha characters in table/query/field control name - that leads to eternal problems.

 

[Starbird711]

It worked great! Thank you so much for your help!!!

I re-created the combo boxes and left them unbound, and changed all of the Field names eliminating the spaces and all non-alpha characters.

Thanks again!