470,602 Members | 1,562 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,602 developers. It's quick & easy.

Access Form Null Checking with Linked SQL / ODBC Table

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?
Nov 13 '05 #1
4 2379
Try using the Form_BeforeUpdate Event to validate the data before actual
saving to the SQL Server.

--
HTH
Van T. Dinh
MVP (Access)


"Rich" <td*******@yahoo.com> wrote in message
news:22**************************@posting.google.c om...
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?

Nov 13 '05 #2
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?
"Van T. Dinh" <Va***********@discussions.microsoft.com> wrote in message news:<e9*************@TK2MSFTNGP14.phx.gbl>...
Try using the Form_BeforeUpdate Event to validate the data before actual
saving to the SQL Server.

--
HTH
Van T. Dinh
MVP (Access)


"Rich" <td*******@yahoo.com> wrote in message
news:22**************************@posting.google.c om...
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?

Nov 13 '05 #3
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.

--
HTH
Van T. Dinh
MVP (Access)


"Rich" <td*******@yahoo.com> wrote in message
news:22**************************@posting.google.c om...
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?

Nov 13 '05 #4
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....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by E. Zorn | last post: by
49 posts views Thread by Yannick Turgeon | last post: by
52 posts views Thread by Neil | last post: by
2 posts views Thread by egoldthwait | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.