asked on

Regarding error Message in Access 2007.

This question is regarding Access 2007.
I'm using SQL Server 2008 as my database and
I created all my table's/Procedures/Functions/Cursor's in SQL Server 2008.

I'm designing my screen's in Access-2007 by linking them to the SQL SERVER 2008 DATABASE.

Now the Problem is,I have a Address Form where user can enter a new address.

I also wrote a VB code to check each and every field in my form so that user does not
leave any field empty and also to dispaly Error message when a field is left blank.

Since my Address fields are created and set to NOT NULL in sql server.
When a field is left blank instead of Displaying my message The Access is displaying the following message.

You tried to assign the null value to a variable that is not a variant data type.

If i leave a field blank im my form,It is checking with sql server.

Now,I want to display my Error,For example if Address city field is left blank,I want to display my message"Please enter A Valid City" Insted of this

"You tried to assign the null value to a variable that is not a variant data type".

Please Help me!!!!!!!!!!!!!
Thank you

HainKurt

doing it on you access form is easy... check all fileds before submitting the data to sql server and do not let user submit data without filling out all required fields... the message is coming from sql server exception...

HainKurt

sorry, I got it wrong I guess... you already did what I mentioned...
if this is the only application to enter data, you can set the columns nullable (which I do not recommend)...

HainKurt

is there any setting in access (design mode) for that column to bet to "allow nulls"? access will think null is ok, and when you click save your message will appear...

Stoical85

ASKER

All my fields are set to NOT NULL in SQl server 2008 Databse....When ever a User leaves a Blank field it is checking with the sql serverand dispalying this Message "You tried to assign the null value to a variable that is not a variant data type". instead of displaying my VB code If Is null(Address_City).
I want it to check my VB code instead of SQL SERVER.

Stoical85

ASKER

No it is not allowing me make any modifications in access......................

Stoical85

ASKER

All my fields are set to NOT NULL in SQl server 2008 Databse....When ever a User leaves a Blank field it is checking with the sql serverand dispalying this Message "You tried to assign the null value to a variable that is not a variant data type". instead of displaying That message I want to display my VB code Message like If Is null(Address_City) Then Msgbox "Enter Address City".
I want it to check my VB code instead of SQL SERVER.

HainKurt

i did the same thing here
access 2007 is connecting to my sql express
created a linked table
created a form for this table
and I can anter leave blank any data
it is not checking anything on server, it will post all data to server when I click save/next record...

I am not sure what are you trying to get those messages...

Stoical85

ASKER

all my fields are set to NOT NULL in sql.

So when i leave a field Blank or empty it is giving this message

"You tried to assign the null value to a variable that is not a variant data type".

It is checking with the sql server.

How can i display my message,

If user leaves any thing empty like for example If the address city is left blank it should display Please enter Address City.

savethetiger

you should perform the control before updating the data and cancelling the event if data are incorrect.

If you select the "form" and see the associated event you will find the BeforeUpdate event.
double click on the event to add the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If IsNull(myfield) Then
   MsgBox "My error messase"
   Cancel = 1
 End If
End Sub

Open in new window

Stoical85

ASKER

Hello SaveTheTiger,

I tried that on both the form and the field in the form but still it is giving me the same message.If I leave a field Blank or empty this message is popping up "You tried to assign the null value to a variable that is not a variant data type".

Here is my code for a field in the form :

Private Sub Addr_Line1_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Addr_Line1) Then
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = 1
End If
End Sub

Here is my code for the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Addr_Line1) Then
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = 1
End If
End Sub

But still giving me the same access nasty message.

savethetiger

Is it possibile that the condition is wrong ? May be the value is not null but "" ?

I did the same mistake writing my example my originally.

Try inserting a break point on form_before update to see if the event is fired before the error message and chek if the condition is wrong.
Other solution is to check how you are doing the update so if you are pressing a button to update the record insert a brak point there etc.
I did the same test with ms access 2003 and mysql 2005 (sorry I have not access 2007) and with an outo generate form the event before_update is fired when
I press the add new record button.

Helen Feddema

You might need to make the form unbound, and then (from a Save button) check that each control has an appropriate value, and if all controls pass this test, then write a new record to the table, using values from the controls saved to variables of the appropriate data type.

Helen Feddema

And if a control has no value, or an inappropriate value, put up an informative message.

Stoical85

ASKER

Hello Helen Fedemma,

Thank You,I'm new to Access,Can you please give me an example to insert into the table.I got struck there.
I dont know hoe to declare the variables in access.I dont know the syntax.
Thank you once again.

HainKurt

try this

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Addr_Line1) Then
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = 1
End If
End Sub

-->

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Addr_Line1 = "" Then
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = 1
End If
End Sub

HainKurt

also try this instead of IsNull(Me.Addr_Line1)

IsNull(Me.Addr_Line1.Text)
IsNull(Me.Addr_Line1.Value)
Me.Addr_Line1.Text = ""
Me.Addr_Line1.Value = ""

since Addr_Line1 should be an object (TextBox) and you want to check the value not the object...

Stoical85

ASKER

Hello HainKurt,

Thank you,I tried all the methods but 'im getting the same error.

HainKurt

just to test... try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = True
End Sub

you should not get any message from sql server and you should not insert any record with this code...

Stoical85

ASKER

Hi HaniKurt,

SAme Error.

Stoical85

ASKER

Hello HaniKurt,

It is working,I mean it is not checking with SQL Server.

If i leave it empty.

It is displaying the message.

This is working.

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = True
End Sub

What is the next step.

Stoical85

ASKER

Hello HaniKurt,

It is working,I mean it is not checking with SQL Server.

If i leave it empty.

It is displaying the message.Enter Address Line1.

This is working.

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = True
End Sub

What is the next step.

savethetiger

I think your filed is not null but empty.
try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Trim(Addr_line1))=0 then
MsgBox "Enter Address Line1.", vbInformation, "Error"
Addr_Line1.SetFocus
Cancel = True
end if
end sub

ASKER CERTIFIED SOLUTION

Stoical85

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stoical85

ASKER

Thank you