• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

update sql server linked table using dao for null fields

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
Fordraiders
Asked:
Fordraiders
2 Solutions
 
PatHartmanCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
additionally,
if Not IsNull(Me!QUOTE_GP) then {assign value}
0
 
FordraidersAuthor Commented:
thanks !!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now