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
Stoical85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
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...
0
HainKurtSr. System AnalystCommented:
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)...
0
HainKurtSr. System AnalystCommented:
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...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Stoical85Author Commented:
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.
0
Stoical85Author Commented:
No it is not allowing me make any modifications in access......................
0
Stoical85Author Commented:
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.
0
HainKurtSr. System AnalystCommented:
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...
0
Stoical85Author Commented:
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.


0
savethetigerCommented:
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

0
Stoical85Author Commented:
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.

0
savethetigerCommented:
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.
0
Helen FeddemaCommented:
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.
0
Helen FeddemaCommented:
And if a control has no value, or an inappropriate value, put up an informative message.
0
Stoical85Author Commented:
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.
0
HainKurtSr. System AnalystCommented:
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
0
HainKurtSr. System AnalystCommented:
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...
0
Stoical85Author Commented:
Hello HainKurt,

Thank you,I tried all the methods but 'im getting the same error.
0
HainKurtSr. System AnalystCommented:
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...
0
Stoical85Author Commented:
Hi HaniKurt,

SAme Error.
0
Stoical85Author Commented:
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.
0
Stoical85Author Commented:
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.
0
savethetigerCommented:
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
0
Stoical85Author Commented:
Thank u
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stoical85Author Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.