?
Solved

Regarding error Message in Access 2007.

Posted on 2009-12-25
24
Medium Priority
?
281 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:Stoical85
  • 12
  • 7
  • 3
  • +1
24 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 26123627
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26123630
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26123632
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Stoical85
ID: 26123635
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
 

Author Comment

by:Stoical85
ID: 26123636
No it is not allowing me make any modifications in access......................
0
 

Author Comment

by:Stoical85
ID: 26123644
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26123686
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
 

Author Comment

by:Stoical85
ID: 26123690
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
 
LVL 4

Expert Comment

by:savethetiger
ID: 26124181
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
 

Author Comment

by:Stoical85
ID: 26124528
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
 
LVL 4

Expert Comment

by:savethetiger
ID: 26124986
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26127814
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26127815
And if a control has no value, or an inappropriate value, put up an informative message.
0
 

Author Comment

by:Stoical85
ID: 26128905
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129075
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129079
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
 

Author Comment

by:Stoical85
ID: 26129183
Hello HainKurt,

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

Expert Comment

by:HainKurt
ID: 26129216
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
 

Author Comment

by:Stoical85
ID: 26129320
Hi HaniKurt,

SAme Error.
0
 

Author Comment

by:Stoical85
ID: 26129410
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
 

Author Comment

by:Stoical85
ID: 26129418
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
 
LVL 4

Expert Comment

by:savethetiger
ID: 26130490
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
 

Accepted Solution

by:
Stoical85 earned 0 total points
ID: 26142874
Thank u
0
 

Author Comment

by:Stoical85
ID: 26327889
Thank you
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question