Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

changing value on recordset gives error 1009

Posted on 2013-05-22
4
Medium Priority
?
343 Views
Last Modified: 2013-06-26
i have a form , based on a selection query, on the form i have a total per order  which is the sum of al subrecords .
O n the form (and in the table) i have a field, "niet factureren" , i have made an event behind the field so that all undelying records are updated

If niet_factureren (field name) = Null Or 1 Then
strsql = "Update dbo.printopdrachten Set [niet factureren] = 1 WHERE Opdrachtnr = " & Me![Opdrachtnr]
DoCmd.RunSQL (strsql)
Else
strsql = "Update dbo.printopdrachten Set [niet factureren] = 0 WHERE Opdrachtnr = " & Me![Opdrachtnr]
DoCmd.RunSQL (strsql)
End If

if i run the code then i get the message 1009 and the message that an other person has changed the data, (code) , but i can not accept the changes.

the recordsource is

SELECT DISTINCT Naam, Opdrachtnr, SUM([te factureren]) AS [te factureren], Omschrijving, afgedrukt, gebruiker, datum, gefactureerd, pdf, [niet factureren]
FROM         printopdrachten
GROUP BY Naam, Opdrachtnr, Omschrijving, gebruiker, datum, factuurinorde, afgedrukt, gefactureerd, pdf, [niet factureren]
HAVING      (gefactureerd = 0) OR
                      (gefactureerd IS NULL)
0
Comment
Question by:timohorn
  • 2
  • 2
4 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39188129
start off by changing :

If niet_factureren (field name) = Null Or 1 Then

to:

If NZ([niet_factureren], 1) = 1 Then
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39188153
first off, you cannot compare the value of a field or control to NULL using the equals operator.  You can do:

IF IsNull([niet_factureren]) OR [niet_factureren] = 1 then

or you could do as I indicated above:

IF (NZ([Niet_factureren], 1) = 1 then

But chances are that the problem is that you are trying to update (via SQL) a field that is locked because it is being edited on your current form.  Why are you not just updating the [niet_factureren] control value in the current form:

me.[niet_factureren] = IIF(NZ([niet_factureren]), 1) = 1, 1, 0)
0
 

Accepted Solution

by:
timohorn earned 0 total points
ID: 39188194
@fyed

That won't work because i have 1 order number, but multiple subrecords,
i show the running total, so if i want to update the value then i have to update all underlying record.
example
Naam      Opdrachtnr      OpdrachtRegelID      factuurinorde      te factureren      Omschrijving      afgedrukt      gebruiker      datum      gefactureerd      pdf      niet factureren
IKo Nederland      13024798      63945            € 0,00      IKo FAMILY TRAINING                              Onwaar      Onwaar
IKo Nederland      13024798      63946            € 2,10      IKo FAMILY TRAINING                              Onwaar      Onwaar
IKo Nederland      13024798      63947            € 0,96      IKo FAMILY TRAINING                              Onwaar      Onwaar
IKo Nederland      13024798      63948            € 180,60      IKo FAMILY TRAINING                              Onwaar      Onwaar
IKo Nederland      13024798      63949            € 523,80      IKo FAMILY TRAINING                              Onwaar      Onwaar

so i have to update all underlying records, but then they won't show.
0
 

Author Closing Comment

by:timohorn
ID: 39277372
Solved it by changing the underlying record set
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

810 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