MSAccess as frontend :) sorry, you're right.
So, I defined column without default value, but nothing
changed... I still can enter empty values into field :(
Now, actualy I want MySQL to rise run-time error, because there
are many forms in access and DB structure is changing ... I mean it is
better (I think) to capture MySQL run-time errors (I have library writen
for this) than edit every form trigger's after changing NOT NULL columns
in DB especially if there is more than one form allowing to enter data
into such tables...
Thanks, for thought about form self validation ;)
Regards,
Arunas
-----Original Message-----
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:09 PM
To: Arūnas Milašauskas
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL not null vs MSAccess required
Arunas,
Actually, I think you mean MySQL is the backend for MS Access.
Don't assume columns that are NOT NULL must have a default value. You
are
welcome to define columns as NOT NULL without a default value. Doing
this
will force the client (the Access form's) to require a value before
inserting the record.
CREATE TABLE customers (
ID INT(9) NOT NULL,
CustName VARCHAR(30) NOT NULL,
...
) Type = MyISAM;
Since you're using an MS Access front-end to allow users to enter data.
Put
logic in your form to require the users to populate a value for the
customer
name. Something like this:
** Assuming you have a form with customer name field named "tbCustName".
Private Sub Form_BeforeUpdate()
Dim strMsg as String
On Error Resume Next
If (Not ValidateSelf()) Then
'** Notify the user that the form failed validation.
Let strMsg = "Please ensure all required fields are populated."
Call MsgBox(strMsg, vbOkOnly)
'** Cancel's the save event.
Call DoCmd.CancelEvent
EndIf
End Sub
Private Function ValidateSelf()
Dim blnIsValid As Boolean
'** Determine if the field has a value.
If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True
'** Return the results of the validation process.
Let ValidateSelf = blnIsValid
End Function
Its been a while since I've used Access for soemthing like this. My VBA
might be a little rusty. The point is you want to enforce user
population of
the customer name field in the client. If you do it from MySQL, the odbc
driver will generate a runtime error, which Access will then need to
capture
- you should capture errors anyway.
Regards,
Adam
-----Original Message-----
From: Arunas Mila?auskas [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:23 PM
To: [EMAIL PROTECTED]
Subject: MySQL not null vs MSAccess required
Hello,
I'm Using MSAccess as backend for my MySQL database. Tables are
linked with MyODBC 3.51, so all data user enters through MSAccess forms.
I need that some fields (CustName) in MySQL table (CUSTOMERS)
would be required, it means, that user must fill a value into that field
(CustName) displayed on MSAccess form (Customers).
MySQL table create statement is:
create table CUSTOMERS (ID `ID` int(9) NOT NULL,
`CustName` Varchar(30) NOT NULL DEFAULT '');
Default value in table declaration is requred as you know for
NOT NULL columns.
The problem is:
When user enters a new record in MSAccess form and does
not even touch field "CustName", new record is created with empty string
as default value!!!!
If user enters for example "1" and then deletes the
value in MSAccess form, Access reports, that value must not be NULL. But
what can I do if user does not even go into that field and leave empty
field? User Must Enter A Value!!!! How to do this without writing many
code in every form with required (NOT NULL) fields?
Thanks for any help, It is needed very very much because for now
any user can not fill required fields!
It would be very very appreciated
Regards, Arunas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
