recordset UPDATE error

[Mark Kubicki]

I have a continuous form with 2 controls for each record: a checkbox, and a
textbox
and have written, behind a command button on the form footer, the below code
to update all of the checkboxes to be true

however,
I am getting either of 2 errors
1 - if I have just clicked a checkbox (to false), all of the records
update to true except the record that just had the focus; on a second click,
it alos updates, and they are all true
2 - if I have just clicked a checkbox, then edited the textbox of that
record, I get a warning message that I have conflicting edits.... (only
editing the textbox, without clicking the checkbox does not seem to cause
this error)

I suspect that it's "something" to do with updating the record set, but at
am at a loss, and any suggestions would be much appreciated (thanks in
advance, mark)

Private Sub cmdPrintAll_Click()
Dim sSQL As String
sSQL = "update tblInstallationNotes" & _
" Set PrintInstallationNote = True;"
Dim Db As DAO.Database
Set Db = CurrentDb

Dim Rs As DAO.Recordset
Set Rs = Db.OpenRecordset("tblInstallationNotes", dbOpenDynaset)
Rs.Edit
Rs.Update

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
Me.Requery

End Sub

 

[Stuart McCall]

I have a continuous form with 2 controls for each record: a checkbox, and a
textbox
and have written, behind a command button on the form footer, the below
code to update all of the checkboxes to be true

however,
I am getting either of 2 errors
1 - if I have just clicked a checkbox (to false), all of the records
update to true except the record that just had the focus; on a second
click, it alos updates, and they are all true
2 - if I have just clicked a checkbox, then edited the textbox of that
record, I get a warning message that I have conflicting edits.... (only
editing the textbox, without clicking the checkbox does not seem to cause
this error)

I suspect that it's "something" to do with updating the record set, but at
am at a loss, and any suggestions would be much appreciated (thanks in
advance, mark)

Private Sub cmdPrintAll_Click()
Dim sSQL As String
sSQL = "update tblInstallationNotes" & _
" Set PrintInstallationNote = True;"
Dim Db As DAO.Database
Set Db = CurrentDb

Dim Rs As DAO.Recordset
Set Rs = Db.OpenRecordset("tblInstallationNotes", dbOpenDynaset)
Rs.Edit
Rs.Update

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
Me.Requery

End Sub

All you really need is:

Private Sub cmdPrintAll_Click()
Dim sSQL As String
sSQL = "update tblInstallationNotes" & _
" Set PrintInstallationNote = True;"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
Me.Requery

End Sub

 

[Mark Kubicki]

that's what I started with... which resulted in the first error I list
below (not all the records check tru on the first pass); I then added the
additional code as a possible fix. It did not work.

-mark

 

[Stuart McCall]

that's what I started with... which resulted in the first error I list
below (not all the records check tru on the first pass); I then added the
additional code as a possible fix. It did not work.

-mark

It sounds like you're calling cmdPrintAll_Click from the Click event of the
checkbox. If so, move it to the checkbox's AfterUpdate and test to see if
it's checked. Something like this:

If Me.CheckboxName = True Then
cmdPrintAll_Click
End If

 

[Dirk Goldgar]

that's what I started with... which resulted in the first error I list
below (not all the records check tru on the first pass); I then added the
additional code as a possible fix. It did not work.

If the current record is "dirty" -- modified by you but not yet saved, as
for example if you clicked its check box and then immediately went to click
the command button in the form's footer -- then that record will be saved
*after* whatever your code does, or else will cause an error later when you
try to save it.

Add a line to the code to save the current record before updating all
records:

'----- modified version of Stuart McCall's code -----
Private Sub cmdPrintAll_Click()

Dim sSQL As String

sSQL = "update tblInstallationNotes" & _
" Set PrintInstallationNote = True;"

' Save current record if it has been modified.
If Me.Dirty Then Me.Dirty = False

CurrentDb.Execute sSQL, dbFailOnError

Me.Requery

End Sub

'----- end code -----

Note: I think using CurrentDb.Execute is better than DoCmd.RunSQL whenever
it can conveniently be used, because you don't have to SetWarnings off and
on.

 

[Mark Kubicki]

perfect! thanks

If the current record is "dirty" -- modified by you but not yet saved, as
for example if you clicked its check box and then immediately went to
click the command button in the form's footer -- then that record will be
saved *after* whatever your code does, or else will cause an error later
when you try to save it.

Add a line to the code to save the current record before updating all
records:

'----- modified version of Stuart McCall's code -----
Private Sub cmdPrintAll_Click()

Dim sSQL As String

sSQL = "update tblInstallationNotes" & _
" Set PrintInstallationNote = True;"

' Save current record if it has been modified.
If Me.Dirty Then Me.Dirty = False

CurrentDb.Execute sSQL, dbFailOnError

Me.Requery

End Sub

'----- end code -----

Note: I think using CurrentDb.Execute is better than DoCmd.RunSQL
whenever it can conveniently be used, because you don't have to
SetWarnings off and on.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)