Acc 2000 to 2007 convert inconsistent table writing

[Rx_]

Could someone look this over and make suggestions?
Access message - You Can't save this record at this time
Do you want to close the database anyway?
but, error.number is 0
The last combo box is RegOffices -
"warning you cannot add or change a record because a related record is required in table RegOffices (its just a list of offices with a primary key, it does not get updated) I am guessing that "null" doesn't match.

Access appears to have the service packs installed.
If I open the Database, I might be able ot add 12 new records, then get a failure.
Other times, it failes on the first time (or second time, or third time ...)
The business logic code is not shown. But, if the record writes to the _be, it works perfectlly. If the record fails to create a new record on the _be then things are hosed.
And, I can't seem to recover or trap the error.

Form Level Variables
Dim db                      As dao.Database
Dim rsAddNew             As dao.Recordset 
AddNew Button
If Me.Dirty Then
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  Me.Dirty = False
End If
 
  Set rsAddNew = Nothing      ' insures cleared second time around and on form close
  Set rsAddNew = Me.Recordset 
    'DoCmd.GoToRecord , , acNewRec  ' did not seem to work
  rsAddNew.AddNew                   ' clears all fields in form ready for new
  rsAddNew.Edit
      rsAddNew!ID_Wells = Me.ID_Wells ' Hidden text box with key
  rsAddNew.Update  ' no error here
  me.refresh 
 
  Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Verifies all three required fields are populated if they are change boolean variable to stop checking
    ' move into 4th field  (has focus because we will lock down the three combo boxes)
    ' code in 4th field (enter) calls Form_BeforeUpdate 
    ' now the changed boolean - run the sub Update the recordset
 
 
  Sub Update the Recordset:     ' Pass fail here
      rsAddNew.Edit
 ' _be table has autocounter
        ' Field with Well
        ' three text boxes
         rsAddNew!ID_Wells = Me.ID_Wells
         rsAddNew!txtFedStCo = Me.cboRegulatoryType
         rsAddNew!lngID_APD_Status = Me.lngID_Reg_Office
       rsAddNew.Update
       Me.Refresh      ' the _be shows new record or The form pass / fails here
          If Me.Dirty Then  ' unsuccessful attempt to recover
             DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
             Debug.Print "Me dirty " & Me.Dirty ' failures are dirty
             'Me.Dirty = False
             Me.Refresh
           End If

One thing I noticed is that the Autocounter counts by 2

Debug Code - General, then after rsAddnew Edit, right before Update
The 4th time - (this time it is random) after the update - me.dirty is true
21249 ID_wells field before book mark = 3203
 Before Cbo reg office 1  recordset 1
 Before Cbo reg office 1  recordset 1
 after update Cbo reg office 1  recordset 1
25997 ID_wells field before book mark = 3203
 Before Cbo reg office 27  recordset 6
 Before Cbo reg office 27  recordset 27
 after update Cbo reg office 27  recordset 27
25999 ID_wells field before book mark = 3203
 Before Cbo reg office 4  recordset 2
 Before Cbo reg office 4  recordset 4
 after update Cbo reg office 4  recordset 4
26001 ID_wells field before book mark = 3203
 Before Cbo reg office 34  recordset 2
 Before Cbo reg office 34  recordset 2
 after update Cbo reg office 34  recordset 2
Me dirty True

 

[boblarson]

Don't know about the rest but this code is wrong:

If Me.Dirty Then
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  Me.Dirty = False
End If

If you want to save the record then you use

If Me.Dirty Then Me.Dirty = False

That saves the record. You don't need, nor do you want, the other part.

 

[boblarson]

Also, you cannot use Me.Refresh in the middle of the Before Update event, and you don't want to anyway.

 

[Rx_]

Thanks! - Very appreciated. Your comments at least indicate that my code is not missing some key element. The random error is annoying.

Me.dirty was basically the error trap indicator of failure.
At this point, the update either did or did not write a record to the _be table.
From there, the form logic cascades into anarchy.
Your suggestion is correct, and after applying the codem I must add code drop the recordset and to recover gracefully.

The Me.Refresh, an excellent point. As I am chasing my tail with a random error, it was added in fustration.

Monday morning restarting the computer, the code worked 20 times in a row with out error. Yesterday, only Access was closed and restarted.

Basic Process Flow: the three combo boxes form_updates are cancled to prevent creating a record being created until all three are filled in. It also prevents a list box (not shown above) from updating partial information. The fourth date control can't get the focus until all three combo boxes are completed. Entering the date control locks the three combo boxes, writes a record (autonumber, well_no (like a customer ID), and the three combo box informtion. From there selected controles are enabled and the list box is re-activated and refreshed.
While the three list boxes are being populated - an Esc key can be captured and act like a Cancel button - to return to the previous record.

Question: Lets say that the client's XP workstation has some deficiency with Access 2007 SP2 related to the Combo box fix. If I took the application home to my own Windows 7 Access 2007 (without these problems) and re-imported all the objects into a new database... might that help?

Reason: New combo boxs created in Access 2007 still has the clear background issue (when enabled = false) that require the work-around with the Alternative Color solution. But, the combo boxes migrated up from Access 2000 to Access 2007 do not have that problem.
The error changes from combobox can't update related table (the table that just provides the list) to the last combobox in my code during the update. That part of the random error of failure to write to a record does not seem to be random.

Thanks for all the support.

 

[DCrake]

I got as far as this when reading your code

Dim db                      As dao.Database
[B]Dim rsAddNew             As dao.Recordset[/B] 
AddNew Button
If Me.Dirty Then
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  Me.Dirty = False
End If
 
  [B]Set rsAddNew = Nothing[/B]      ' insures cleared second time around and on form close
[COLOR="Red"]Set rsAddNew = Me.Recordset[/COLOR]

First you declare the rsAddNew recordset
Then before you do anything with it you release it. What's the point of that
The next line does not know whar rsAddNew is

 

[gemma-the-husky]

with regard to this extract

You Can't save this record at this time
Do you want to close the database anyway?
but, error.number is 0

where is this coming from - is this your code in an access on error event procedure

if so, the error is now not err, but dataerr, as indicated in the parameters to the procedure - and the text for this is given by accesserror(dataerr)

 

[Rx_]

accesserror(dataerr)
I did not know that. Thanks! I am going to check this out right away.

I have made a backup version every 20 minutes.
This morning, I was able to add and edit dozens of records at a time.
Tested many conditions and was finishing the formatting.

Then around noon, the same old error.
Rebooting computer, going back two hours version... same thing.
Even tried the compact for both front end and back end.

It was the same thing Sat and Sun. About 4 hours into things.
Just don't get it.

 

[boblarson]

Another question -

How is the part in red supposed to ensure (not insure) that the variable is destroyed on the close of the form? It shouldn't be at the beginning of the code, but at the point of EXIT of the procedure. I think you may be creating your own memory leaks and that is why you can go and go and then hit a wall.

Set rsAddNew = Nothing ' insures cleared second time around and on form close
Set rsAddNew = Me.Recordset
'DoCmd.GoToRecord , , acNewRec ' did not seem to work

 

[Rx_]

Thanks for all great tips - put that to work : All of the set rsAddnew = nothing at end points.
For a while, it seemed that new records could be added, unlocked, edited, added, the wells (customer no) changed... about 40 of them. No problem. Then later... it was better but still happening.
And when it happens... the error won't clear, it does not trigger the forms's Form_Error(dataErr, ...) A couple of weeks ago, I put a message box and debug.print in there - it has never fired.
This is a tab ina parent form. The setwarnings on the ondirty capture don't stop this warning.
Now, this morning - My delete button worked just fine. The only problem was moving off the current record and syncronizing with my list box.
Even after a computer re-start - now when trying to run:
DoCmd.SetWarnings False
'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
50 DoCmd.RunCommand acCmdSelectRecord
'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
60 DoCmd.RunCommand acCmdDeleteRecord
The error comes back
"the command or action Delete Record isn't available now"

 

[Rx_]

The only problem (so far) with Importing All Objects into a new Access 2007 DB is that the form start up settings don't import. Will look into that more.

Remembered doing this only one time in Access 2003. Found it in Access 2007 Help of all places: "If the database file is damaged or has data problems, delete the damaged file and then replace the damaged file with the backup"

The orginal database was created on a workstation where the Help and other issues known to be resolved in SP2 were still a problem.
Running some hot fixes created a need to get a different workstation.
So, maybe I will import all objects into a new Access 2007.

15 minutes later: Well guess what?
The exact same database imported (all objects) works perfectlly with the same delete statement. This may not be the fix, but is is strange how it suddenly works here.

30 minutes later: So far, absolutely no errors. It does not behave anything like the previous version. Why didn't I do this a week ago???

Here is what I think, and would enjoy some feed back.
When I imported Access 2000 into Access 2007 (on the computer with known SP2 Problems) the imported list boxes behaved differently than the ones I created in Access 2007.
Now, with a different PC - the old Access Database had either some internal mis-links or residual bad objects.
Hope that I am right, the schedule is so far behind now.

Thanks for all the input. Hopes this helps somebody else. It should be a "first thing to do" if things act up.