problem w/ update method using bound control

[Bebe]

when i run my program and update a field called Date_Closed the program will update it the first time. If I go back and try to update the record again or erase the Date_Closed field I get an error: "Runtime 3426 This action was cancelled by an associated object"

here is the code:

Private Sub cmdUpdate_Click()
'getting error code here!!!
Data2.Recordset.Edit
Data2.Recordset.Update
Data2.Recordset.Edit
Data2.Recordset.AddNew
'Data2.Refresh


why does it work once and then fail? anyone have any ideas again this is databound so I know their are bugs anyone know a fix?

[Nias]

I don't think you want to end your click event with ADDNEW. An ADDNEW starts an edit on a new record. To finish ADDNEW you should use an UPDATE (to complete the transaction). Perhaps the second time through your database still thinks you are trying to add the first record. There is only a portion of the code down there to look at so I only know part of the story. I am also confused as to why you EDIT, UPDATE, EDIT again and then ADDNEW. If you are adding a new record you would do:
.ADDNEW
set fields here
.UPDATE

If you are Editing a record you would use .EDIT instead of .ADDNEW.

[Bebe]

your right you didn't have the rest of the code; i do need to have an addnew and it works find because i need to save the changes and then i need to have a new record begin immediately; they are then brought to an input screen where then have to options to cancel the update or to save so it works fine.

I did notice that I can edit all the records but the date fields. It is on the date fields that i receive the error message. something with them being date/time fields in the database is causing the error the text fields update just fine with no error.

[Nias]

This is beginning to sound familiar. I don't have the code I used in front of me but as I recall when I used dates, I had to put "#" around the date I was using. Again, I am flying by the seat of my pants but it was something like:
datefld = "#03/27/2000#".

If you are using a date type field instead of a string type then you can set it like this:
datefld = "03/27/2000 08:30" or datefld = date & " " & time

Again I don't know what you are doing (I barely know what I am doing) so this may or may not help. What I did was write my update statement in ACCESS (my db engine at the time) and have it create the SQL statement. Even though you are not using the SQL itself it will still format the date field for you. I can submit my code for you if you want. Just shoot me an e-mail and when I get home I will return it. It sounds like you are doing something very similar to what I was doing a few weeks back.

[Bebe]

where are you changing the properties of the date in the program or in the actual database
so i would make it:

txtDateComplete=#3/27/00#

im not sure what you mean?

email is: [email protected]

i would love to see your code; thanks

[Nias]

I don't change any of the properties. I was just thinking back a few weeks when I was having problems with dates in Access. It was picky and to figure out what the dern thing wanted I had to go into Access and create my own query (update query) and see how it formatted the date. When I did the same thing in code I found it worked perfectly. I assume the field in your database is of Date type and not of type string?!?! What DB engine are you using?

Private Sub cmdUpdate_Click()
'getting error code here!!!
Data2.Recordset.Edit
Data2.Recordset.Update
Data2.Recordset.Edit
Data2.Recordset.AddNew
'Data2.Refresh

I still think having a EDIT and ADDNEW together might be a problem. If you don't commit the data and try to edit again then you might get an error. Also between the EDIT and Update you should be populating the db fields.
.EDIT
field1 = bla
field2 = da
.UPDATE

When I get home I will e-mail my code which I got to work and you can apply it to yours. I am by far not the expert but I learn a lot reading through these forums.

[Bebe]

well everything will update just not time/date fields.

i don't have to populate the db fields because i am using the databound control and each txt or cbo field is connected to its specific field in the database by properties. so once they are connected you only have to use the update property.

any other ideas if you think of them let me know thanks

[pardede]

You could leave out the second 'edit' method (the one before 'addnew'). That line doesn't do anything and is just being redundant. The 'addnew' method is enough to 'open' a new row for entering record.

However, that might not be the cause of your problem. I have posted a reply in your other question about the error message so please read that and maybe it helps...

[Nias]

You know what? I forgot about that bound luxury. I am using Access 2000 so I can't use that (at least not as far as I have found). Sorry about that.
If all you are trying to do is clear out a date field try setting it to zero or default it to a date 20 years ago 1/1/80. I think zero will work.