Solved

changing value on recordset gives error 1009

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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