Solved

update sql server linked table using dao for null fields

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

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

808 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