Solved

changing value on recordset gives error 1009

Posted on 2013-05-22
4
309 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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

762 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

23 Experts available now in Live!

Get 1:1 Help Now