Solved

update sql server linked table using dao for null fields

Posted on 2016-10-20
3
28 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 35

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

785 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