.edit in UPDATE event error

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
 
A

Al Borges

Hi Mark:

You can't make differential background colors for fields. What you CAN do,
though, is to make differential forecolor fields. To do this you will need
to overlap 3 fields:

1) first one- make it invisible; this field will have its controlsource
property tied to the real underlying field
2) second field- make its control source property:
IIF(isnull(firstrecord), "", [firstrecord]))
Set this one to a transparent backstyle.
3) third field- make its control source property:
IIF(isnull(firstrecord), [firstrecord],""))
Set this one to a transparent backstyle.

This may work for you...

Regards,
Al
 
M

mark r

I will give it a try

But While I am experimenting with it, could you shed some
light on the more general questions?

1. What do you mean by "differential"? different
textboxes can have different backcolors AND my "VBcode
logic" is largely working, I have only one "functional bug"

2. Do I need to be using RecordsetClone :


3. It seems NEWSCREENCHANGECTLCOLOR is working fine, but
when I re-open the form later, the first record that is
displayed has only one field that is not staying white
even though it not null, but if if click combo39 and then
come back to this record, that field correctly changes to
white. Can you tell why backcolor does stay red for that
one lone field after I close the form but the other fields
do?
 
M

mark r

1. by "first record do you mean "firstfield" ?
2. why couldn't you do the same thing with backcolor
utilizing backstyle?
3. is there any way to make the color blink on and off?

-----Original Message-----
Hi Mark:

You can't make differential background colors for fields. What you CAN do,
though, is to make differential forecolor fields. To do this you will need
to overlap 3 fields:

1) first one- make it invisible; this field will have its controlsource
property tied to the real underlying field
2) second field- make its control source property:
IIF(isnull(firstrecord), "", [firstrecord]))
Set this one to a transparent backstyle.
3) third field- make its control source property:
IIF(isnull(firstrecord), [firstrecord],""))
Set this one to a transparent backstyle.

This may work for you...

Regards,
Al

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...
 
A

Al Borges

RE: color blinking on and off:

What you would have to do is to place the following in the form's timer
event:

Private Sub Form_Timer()
If Me![Text42].ForeColor = 8388608 Then
Me![Text42].ForeColor = 65535
Else
Me![Text42].ForeColor = 8388608
End If
End Sub

Now set the timer interval to 400... now you have a blinking forecolor
field! You can, of course, experiment with the colors and timer intervals.

Regards,
Al

mark r said:
1. by "first record do you mean "firstfield" ?
2. why couldn't you do the same thing with backcolor
utilizing backstyle?
3. is there any way to make the color blink on and off?

-----Original Message-----
Hi Mark:

You can't make differential background colors for fields. What you CAN do,
though, is to make differential forecolor fields. To do this you will need
to overlap 3 fields:

1) first one- make it invisible; this field will have its controlsource
property tied to the real underlying field
2) second field- make its control source property:
IIF(isnull(firstrecord), "", [firstrecord]))
Set this one to a transparent backstyle.
3) third field- make its control source property:
IIF(isnull(firstrecord), [firstrecord],""))
Set this one to a transparent backstyle.

This may work for you...

Regards,
Al

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...
Click to expand...
 
A

Al Borges

1. What do you mean by "differential"? different
textboxes can have different backcolors AND my "VBcode
logic" is largely working, I have only one "functional bug"

I meant "different"... at least through Access 2000 if you change the
backcolor to one textbox on a table listing, ALL associated textboxes for
the other various records will change.

No... when the textbox shows "", it'll essentially look foreground
invisible, allowing the other textbox to show the color.

Regards,
AL
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

recordsetclone mis-usage? 5
IsNull problem runtime 2447 3
When to use .Edit & .Update 11
lost focus...? 3
Want to avoid the possibilty to edit field in listbox 6
Cancelling an update on a continuous form based on another value 2
Combo to find record does not auto expand 6
Combo box based on a query to navigate to a specific record 6

Top