Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

changing value on recordset gives error 1009

Posted on 2013-05-22
4
Medium Priority
?
338 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
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
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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…
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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