Required Fields!

[slaman]

Ok, the first field in a form I have is called "Name". Now I want to make sure that this Name field is:

-unique
-non-Empty

and I want to verify this BEFORE I move on to the next field. Below is my pathetic attempt at doing this:

Private Sub Corporation_Name_Exit(Cancel As Integer)
Dim corpName As Boolean
corpName = IsNull([Corporation Name])
#If corpName Then
[Corporation Name].SetFocus
#End If
End Sub

What I am doing above is simply checking to see if the field isNull and if it is, SetFocus back to it, so that the user cannot move to the next one. This is inefficient and doesn't work. How can I check to see if the field entry is UNIQUE and NONEMPTY before I move to the next field? If it isn't, I want to display a little message in the status bar or maybe perhaps a little error message window. The reason I want to do this is because I am getting errors when I press "Next Entry" when the old entry doesn have the Name field filled out.

Any help is appreciated.

 

[RickSpr]

This would have worked, if you had left out the "#" characters. "#If" and "#End If" are instructions to the compiler to include or omit pieces of your source code before it compiles. In this case, they probably caused the "[Corporation Name].SetFocus" statement to be left out of the compiled program, which is why it didn't work. What you wanted were "If" and "End If", which work at run time.

To check Name for uniqueness, you're going to have to look it up in the table. You can use the DLookup function for this purpose. It's perfect for this, and used often enough that I recommend you study the help docs about it.

However, let's say that [Corporation Name] is a field in a table called Companies. You could then use this code to test for a unique name:

    If Not IsNull(DLookup("Corporation Name", "Companies", "Corporation Name = '" & [Corporation Name] & "'")) Then
        MsgBox "Duplicate corporation name, please try again"
        [Corporation Name].SetFocus
    End If

This works because DLookup returns the name if it already exists in the table, and returns Null if it doesn't. Rick Sprague

 

[slaman]

The database I want to look up is called "Corporations". The field of this database I want to compare against is called "Corporation Name". The field on the form is also called "Corporation Name". I have the following code to test for uniqueness:

Private Sub Corporation_Name_Exit(Cancel As Integer)
If Not IsNull(DLookup("[Corporation Name]", "Corporations", "Corporation Name = ' " & [Corporation Name] & " ' &quot) Then
MsgBox "Corporation Name required: Record not added", vbInformation
Cancel = True
End If
Me.Refresh
End Sub

I get a syntax error - missing operator in query expression... Please explain this last parameter

"Corporation Name = ' " & [Corporation Name] & " ' "

because I don't really understand what its doing. Plus will that above code also check to see if the field is null as well?

 

[slaman]

I have spent a long time now trying to understand what you were doing... Unfortunately, that code simply does not work.