Access Form Null Checking with Linked SQL / ODBC Table

R

Rich

I have a access form that is connected to linked sql table via odbc.
I have some fields that I dont want to allow nulls when data is
entered via the form. I can set the null checking on the SQL server
database but the ODBC error tha tis returned is not very polite to the
user since it mentions the sql field not the nice label.

Is there a way for me to modify this error or put the check in access?
 
V

Van T. Dinh

Try using the Form_BeforeUpdate Event to validate the data before actual
saving to the SQL Server.
 
R

Rich

This leads me to another question. What is the syntax frequired for
the before event? I searched around and found some validation code.
It has an It statment and msgbox command to post a message if the
validation fales however access doesnt like that code. It says that
the msgbox command has bad syntax.

What is the proper syntax for the validation event?
 
V

Van T. Dinh

If you are talking about the BeforeUpdate Event, it is an Event Procedure
normally written in VBA code. Check Access VB Help on the BeforeUpdate
Event / Event Procedure. I think there are sample code in the Help topic.

If you are asking about the MsgBox statement, see Access VB Help on MsgBox.

In fact for syntax questions, you should always check Access Help first and
try rather than asking the newsgroups. If you can't get it to work after
checking Help, post the codes of your attempts with your question.
 
A

Albert D. Kallal

You might as well just code the solution....

I put in the forms before update:



Cancel = MyVerify


And, my code for MyVerify is:


Private Function MyVerify() As Boolean

Dim colFields As New Collection

MyVerify = False

colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"
colFields.Add "AccountNo,Account number"

MyVerify = vfields(colFields)


End Function

Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim i As Integer

vfields = False

For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i


End Function

Eg:
colFields.Add "AccountNo,Account number"

The first part is the control on the screen that we want to validate, and
the 2nd part of the custom "text" message that the user will get:

I paste the above into most screens, and thus I don't have to write a bunch
of code for each control that I want as requited. If the control is null,
then you get a error message, the cursor is also placed on the actual
control that is empty....
 
Top