Solved

update sql server linked table using dao for null fields

Posted on 2016-10-20
3
23 Views
Last Modified: 2016-10-28
access 2010
sql server 2008 r2 - linked tables

I have some code that i'm using to save data to a linked sql server table.
Dim R As DAO.Recordset
Set R = CurrentDb.OpenRecordset("SELECT * FROM [dbo_t_escalation_master] WHERE [PRICING_ESCALATION_ID] =  " & t & " ", dbOpenDynaset, dbSeeChanges)
r.edit
      R![OPPORTUNITY_VALUE] = Nz(Me("OPPORTUNITY_VALUE"), "")
      R![QUOTE_GP] = Replace(Me!QUOTE_GP, "%", "")

etc...


I do ok until , "OPPORTUNITY_VALUE" is null or "quote_gp" if trying to save data.

In sql server tables / access field types
"OPPORTUNITY_VALUE" =  money  / access =  currency
"quote_gp" = int      /   access =  number


These 2 fields will throw an error if they are null or blank ?

when trying to save a record.


Thanks
fordraiders
0
Comment
Question by:fordraiders
3 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 400 total points
ID: 41852668
That's because '' is a ZLS (Zero Length STRING) and you can't put a string into a numeric field.

Change to:

r.edit
      R![OPPORTUNITY_VALUE] =  Me("OPPORTUNITY_VALUE")    
      R![QUOTE_GP] = Replace(Me!QUOTE_GP, "%", Null)

etc...

You should also consider changing how you reference form fields.  Using Me.Opportunity_Value and Me.Quote_Gp will give you intellisense and you'll be able to see typos immediately.
0
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 100 total points
ID: 41852884
additionally,
if Not IsNull(Me!QUOTE_GP) then {assign value}
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41864510
thanks !!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now