Solved

changing value on recordset gives error 1009

Posted on 2013-05-22
4
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 48

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 48

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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
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 …

688 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