recordsetclone mis-usage?

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

Please read through some of my VB code below, to get an
idea of some of the functionality invovled with
my "scenario".


The whole "issue" began when I got an error message:
along the lines of "the database engine expects .ADDNEW
or .EDIT when Update or Cancel event is used."

When I did not include the .edit aspect of
the code in the sub routine, I got that error message.
I don't get an error now that I included this
rs=recordset and .Edit piece of code. But I am not
sure of what I am really doing and not sure if I am doing
something to my tables that I don't really want.

What I want is for certain critical fields on the form to
have a red backcolor if the field ISNULL, otherwise
white. So I am not sure why I need a .EDIT or .ADDNEW
method since I am not changing table values.

I am having my LOOKUPCOMBOBUTTON_AFTERUPDATE function
check the backcolors after it does eveerything else it
does so that the colors are all correct once I move
forward to a different record. That is part of the reason
I was getting that error. But this combobutton has
a .EDIT method. And I put the .EDIT into the called
subroutine to eliminate the error.

You can see I have only enough knowledge to be dangerous.

Can you clear the fog for me?



Sub NEWSCREENCHANGECTLCOLOR()
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs

If ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
End Sub

Function Chkcolor()
' critical fields are red background; this fnc changes
them to white once no longer NULL
With Me.ActiveControl
If IsNull(.Value) Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With
End Function

Private Sub commnewscrn_Click()
On Error GoTo Err_commnewscrn_Click
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
if ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
Exit_commnewscrn_Click:
Exit Sub

Err_commnewscrn_Click:
MsgBox Err.description
Resume Exit_commnewscrn_Click

End Sub

Private Sub combo39_AfterUpdate()
Rem Go TO Select records ONLY PATIENTS THAT Presented
Today
Rem Order: order presented in clinic today

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.FindFirst "ID = " & Me.Combo39
.Edit 'Mode
!combodate = Date
.Update
Me.Bookmark = .Bookmark
End With
Me.Combo39.Requery
Me.Combo43.Requery
Me.List_todaysrecs.Requery
Me.List_HX_all.Requery
Me.Combo47.Requery
Call NEWSCREENCHANGECTLCOLOR
Rem Me.Comboselectdate.Requery
End Sub
..
 
There are two routines that reference recordsets, that
don't use them. I would change them to:


Sub NEWSCREENCHANGECTLCOLOR()

For Each ctl In Me.Controls

If ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
Next ctl
End Sub

Private Sub commnewscrn_Click()
On Error GoTo Err_commnewscrn_Click

For Each ctl In Me.Controls
if ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
Next ctl

Exit_commnewscrn_Click:
Exit Sub

Err_commnewscrn_Click:
MsgBox Err.description
Resume Exit_commnewscrn_Click

End Sub
 
Mark,

The .Edit method places the recordset in Edit Mode. This tells Access that
you may be modifying data in the recordset, as opposed to creating new
records. If you were to create new records, you would issue the .AddNew
method instead. The error message is telling you that Acess can't execute a
..Update or .Cancel method without having first encountered a .Edit or
..AddNew method. That's why, when you add the .Edit, the message box no
longer appears.

As for changing the control colours, I recommend ditching all that
formatting code in favour of conditional formatting:
1. In form design view, select all the controls you want to format.
2. Select [Conditional Formatting] from the [Format] menu.
3. In the [Default Formatting] group, set the BackColor to red.
4. In the [Condition 1] group, set the fields as follows: Field Value Is
not equal to ""
5. Click OK.

This will set each fields' BackColor to red if it is empty.

Can you clarify exactly what you're trying to do with the rest of the code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Firstly , I have a question regarding your instructions.
You said:
1. In form design view, select all the controls you want
to format.
Do you mean select one, each at a time, set
the “conditional format”, then move on to the next? Or is
there a way to select 30 at one time with “selecting
All” ?

You are a rare example of a responder that can figure out
the “level of expertise” the questioner is on, answer at
the level the questioner can understand, answer
completely, and seems to give a hoot.

Thanks, your answer was fantastic. I learned so much. I
didn’t realize what “conditional formatting” really
was……….several responders have been talking about it, but
it went over my head………I thought it referred to “IIF”
placed in a property value. Your answer was specific
enough, I tried it out and I get it now.

Meanwhile, I did manage to get my form to behave like I
wanted. The question now is “which one will be more
efficient? Your way/conditional formatting or the way the
form is at the moment, which I now am able to better
describe, below:



Goal – of the 100 fields on the form, perhaps 30 are so
important that the user needs a visual reminder to fill
them in, even though they may allow for null value,
because data is not always available at the moment or the
data will be filled in at a later time, after the record
is originally created and closed.

So for those very important fields, their textboxes have:

Tag = checkred
On Lost Focus = chkcolor()

VBCODE:

Function chkcolor()
With Me.Activecontrol
If Isnull(.value) Then
.backcolor = vbred
else
.backcolor = vbwhite
end if
End with
End function

Sub Newscreenchangectlcolor()
For each ctl in me.controls
If ctl.tag = “checkred” then
If isnull(ctl.value) then
Ctl.backcolor = vbred
Else
Ctl.backcolor = vbwhite
Endif
Endif
Next ctl
End sub


Private Sub Form_CURRENT()
Call Newscreenchangectlcolor
End sub

For some reason, if I don’t call Newscreenchangectlcolor
In this next sub, comboGOTOotherrecord_afterupdate(),
sometimes I get an error message: Update or CancelUpdate
without AddNew or Edit. It seems to occur if I place a
value into a field that was originally null, but I am not
sure. However, if I leave that “call” in place, I never
get the error message, so I leave it in!


Private sub comboGOTOotherrecord_afterupdate()
Dim rs as DAO.recordset
Set rs = me.recordsetclone

With rs
.findfirst “ID = “ & Me.COmboGOTOotherrecord
.edit
!combodate = Date
.update
Me.bookmark = .bookmark
Endwith

Call Newscreenchangectlcolor
Me. Combogotootherrecord.requery
Endsub





-----Original Message-----
Mark,

The .Edit method places the recordset in Edit Mode. This tells Access that
you may be modifying data in the recordset, as opposed to creating new
records. If you were to create new records, you would issue the .AddNew
method instead. The error message is telling you that Acess can't execute a
..Update or .Cancel method without having first encountered a .Edit or
..AddNew method. That's why, when you add the .Edit, the message box no
longer appears.

As for changing the control colours, I recommend ditching all that
formatting code in favour of conditional formatting:
1. In form design view, select all the controls you want to format.
2. Select [Conditional Formatting] from the [Format] menu.
3. In the [Default Formatting] group, set the BackColor to red.
4. In the [Condition 1] group, set the fields as follows: Field Value Is
not equal to ""
5. Click OK.

This will set each fields' BackColor to red if it is empty.

Can you clarify exactly what you're trying to do with the rest of the code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Please read through some of my VB code below, to get an
idea of some of the functionality invovled with
my "scenario".


The whole "issue" began when I got an error message:
along the lines of "the database engine expects .ADDNEW
or .EDIT when Update or Cancel event is used."

When I did not include the .edit aspect of
the code in the sub routine, I got that error message.
I don't get an error now that I included this
rs=recordset and .Edit piece of code. But I am not
sure of what I am really doing and not sure if I am doing
something to my tables that I don't really want.

What I want is for certain critical fields on the form to
have a red backcolor if the field ISNULL, otherwise
white. So I am not sure why I need a .EDIT or .ADDNEW
method since I am not changing table values.

I am having my LOOKUPCOMBOBUTTON_AFTERUPDATE function
check the backcolors after it does eveerything else it
does so that the colors are all correct once I move
forward to a different record. That is part of the reason
I was getting that error. But this combobutton has
a .EDIT method. And I put the .EDIT into the called
subroutine to eliminate the error.

You can see I have only enough knowledge to be dangerous.

Can you clear the fog for me?



Sub NEWSCREENCHANGECTLCOLOR()
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs

If ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
End Sub

Function Chkcolor()
' critical fields are red background; this fnc changes
them to white once no longer NULL
With Me.ActiveControl
If IsNull(.Value) Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With
End Function

Private Sub commnewscrn_Click()
On Error GoTo Err_commnewscrn_Click
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
if ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
Exit_commnewscrn_Click:
Exit Sub

Err_commnewscrn_Click:
MsgBox Err.description
Resume Exit_commnewscrn_Click

End Sub

Private Sub combo39_AfterUpdate()
Rem Go TO Select records ONLY PATIENTS THAT Presented
Today
Rem Order: order presented in clinic today

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.FindFirst "ID = " & Me.Combo39
.Edit 'Mode
!combodate = Date
.Update
Me.Bookmark = .Bookmark
End With
Me.Combo39.Requery
Me.Combo43.Requery
Me.List_todaysrecs.Requery
Me.List_HX_all.Requery
Me.Combo47.Requery
Call NEWSCREENCHANGECTLCOLOR
Rem Me.Comboselectdate.Requery
End Sub
.
Click to expand...


.
Click to expand...
 
Mark,

1. When I say select all the controls, I literally mean "select ALL the
controls". Select ONE control, then (while holding down the SHIFT button)
select another, then another, and so on.

2. In comboGOTOotherrecord_afterupdate(), you still need the .Edit, because
you're changing the value of !combodate, after which, you issue a .Update.

As I said in the first paragraph of my last post, "the .Edit method places
the recordset in Edit Mode. This tells Access that you may be modifying data
in the recordset, as opposed to creating new records. If you were to create
new records, you would issue the .AddNew method instead. The error message
is telling you that Acess can't execute a .Update or .Cancel method without
having first encountered a .Edit or .AddNew method." For example,
Set rs = db.OpenRecordset("tblSomeTable")
'Edit an existing record
rs.Edit
rs!somefield = "abc"
rs.Update

'Add a new record
rs.AddNew
rs!somefield="def"
rs.Update

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Firstly , I have a question regarding your instructions.
You said:
1. In form design view, select all the controls you want
to format.
Do you mean select one, each at a time, set
the “conditional format”, then move on to the next? Or is
there a way to select 30 at one time with “selecting
All” ?

You are a rare example of a responder that can figure out
the “level of expertise” the questioner is on, answer at
the level the questioner can understand, answer
completely, and seems to give a hoot.

Thanks, your answer was fantastic. I learned so much. I
didn’t realize what “conditional formatting” really
was……….several responders have been talking about it, but
it went over my head………I thought it referred to
“IIF”
placed in a property value. Your answer was specific
enough, I tried it out and I get it now.

Meanwhile, I did manage to get my form to behave like I
wanted. The question now is “which one will be more
efficient? Your way/conditional formatting or the way the
form is at the moment, which I now am able to better
describe, below:



Goal – of the 100 fields on the form, perhaps 30 are so
important that the user needs a visual reminder to fill
them in, even though they may allow for null value,
because data is not always available at the moment or the
data will be filled in at a later time, after the record
is originally created and closed.

So for those very important fields, their textboxes have:

Tag = checkred
On Lost Focus = chkcolor()

VBCODE:

Function chkcolor()
With Me.Activecontrol
If Isnull(.value) Then
..backcolor = vbred
else
..backcolor = vbwhite
end if
End with
End function

Sub Newscreenchangectlcolor()
For each ctl in me.controls
If ctl.tag = “checkred” then
If isnull(ctl.value) then
Ctl.backcolor = vbred
Else
Ctl.backcolor = vbwhite
Endif
Endif
Next ctl
End sub


Private Sub Form_CURRENT()
Call Newscreenchangectlcolor
End sub

For some reason, if I don’t call Newscreenchangectlcolor
In this next sub, comboGOTOotherrecord_afterupdate(),
sometimes I get an error message: Update or CancelUpdate
without AddNew or Edit. It seems to occur if I place a
value into a field that was originally null, but I am not
sure. However, if I leave that “call” in place, I never
get the error message, so I leave it in!


Private sub comboGOTOotherrecord_afterupdate()
Dim rs as DAO.recordset
Set rs = me.recordsetclone

With rs
..findfirst “ID = “ & Me.COmboGOTOotherrecord
..edit
!combodate = Date
.update
Me.bookmark = .bookmark
Endwith

Call Newscreenchangectlcolor
Me. Combogotootherrecord.requery
Endsub





-----Original Message-----
Mark,

The .Edit method places the recordset in Edit Mode. This tells Access that
you may be modifying data in the recordset, as opposed to creating new
records. If you were to create new records, you would issue the .AddNew
method instead. The error message is telling you that Acess can't execute a
..Update or .Cancel method without having first encountered a .Edit or
..AddNew method. That's why, when you add the .Edit, the message box no
longer appears.

As for changing the control colours, I recommend ditching all that
formatting code in favour of conditional formatting:
1. In form design view, select all the controls you want to format.
2. Select [Conditional Formatting] from the [Format] menu.
3. In the [Default Formatting] group, set the BackColor to red.
4. In the [Condition 1] group, set the fields as follows: Field Value Is
not equal to ""
5. Click OK.

This will set each fields' BackColor to red if it is empty.

Can you clarify exactly what you're trying to do with the rest of the code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Please read through some of my VB code below, to get an
idea of some of the functionality invovled with
my "scenario".


The whole "issue" began when I got an error message:
along the lines of "the database engine expects .ADDNEW
or .EDIT when Update or Cancel event is used."

When I did not include the .edit aspect of
the code in the sub routine, I got that error message.
I don't get an error now that I included this
rs=recordset and .Edit piece of code. But I am not
sure of what I am really doing and not sure if I am doing
something to my tables that I don't really want.

What I want is for certain critical fields on the form to
have a red backcolor if the field ISNULL, otherwise
white. So I am not sure why I need a .EDIT or .ADDNEW
method since I am not changing table values.

I am having my LOOKUPCOMBOBUTTON_AFTERUPDATE function
check the backcolors after it does eveerything else it
does so that the colors are all correct once I move
forward to a different record. That is part of the reason
I was getting that error. But this combobutton has
a .EDIT method. And I put the .EDIT into the called
subroutine to eliminate the error.

You can see I have only enough knowledge to be dangerous.

Can you clear the fog for me?



Sub NEWSCREENCHANGECTLCOLOR()
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs

If ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
End Sub

Function Chkcolor()
' critical fields are red background; this fnc changes
them to white once no longer NULL
With Me.ActiveControl
If IsNull(.Value) Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With
End Function

Private Sub commnewscrn_Click()
On Error GoTo Err_commnewscrn_Click
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
if ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
Exit_commnewscrn_Click:
Exit Sub

Err_commnewscrn_Click:
MsgBox Err.description
Resume Exit_commnewscrn_Click

End Sub

Private Sub combo39_AfterUpdate()
Rem Go TO Select records ONLY PATIENTS THAT Presented
Today
Rem Order: order presented in clinic today

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.FindFirst "ID = " & Me.Combo39
.Edit 'Mode
!combodate = Date
.Update
Me.Bookmark = .Bookmark
End With
Me.Combo39.Requery
Me.Combo43.Requery
Me.List_todaysrecs.Requery
Me.List_HX_all.Requery
Me.Combo47.Requery
Call NEWSCREENCHANGECTLCOLOR
Rem Me.Comboselectdate.Requery
End Sub
.
Click to expand...


.
Click to expand...
 
Back
Top