Refresh record and screen (1 Viewer)

I have a combo field that, when changed will update the background color on itself and two other fields. The issue is that the colors dont change until I have gone to another record and then come back to the record I am working with.

While this generally works, I would rather have the screen update while the user is on the record they are working on.

Initially, I was using "requery", and it was working like a champ (or so I thought) until someone pointed out that the requery caused the pointer to back to the first record. (I did not realize it, but I was always testing with record one. Yeah, I know....)

Any way, I have tried a few things to no avail. So that's why I am here.

Thoughts, Suggestions?

Instead of Me.Requery use Me.Recalc

I used me.Recalc and the screen did not refresh.

Normally requery/recalc are used after a calculation or record change
Recalc updates the screen without moving to the first record
It won't work if nothing has been calculated

You may be able to use Me.Repaint instead.

If not, then:
- turn off screen updating using Application.Echo False
- bookmark the current record
- Then run requery
- Then use DoCmd.GoTo ... saved bookmark
- turn on screen updating again using Application.Echo True
The Echo code will prevent unwanted screen flicker whilst running the code

Where is your code located? It should be in the AfterUpdate event of the Combobox and in the Form_Current event.

Linq ;0)>

missinglinq said:

Where is your code located? It should be in the AfterUpdate event of the Combobox and in the Form_Current event.

Linq ;0)>

Click to expand...

I have been putting this in OnChange

OnChange is the wrong event. It fires once for each character you type. So, unless you need to do character by character validation, you should use an event that runs only once when the control is changed.

Use the AfterUpdate event of the control. This event typically doesn't fire until the control looses focus because you tabbed into another control or moved to a new record when the control is the last on on the form.

missinglinq said:

Where is your code located? It should be in the AfterUpdate event of the Combobox and in the Form_Current event.

Linq ;0)>

Click to expand...

I agree with AfterUpdate
BUT why Form_Current as well? I see no need for that

I have tried the "Me....." suggestions and nothing seems to work.

Perhaps this tidbit will help. If I make the change to the field, change to a new record and then back to the record I change, the fields have the new backgroun colors I need.

I even put the If Then lines that are in the Public Sub_Form Current into the AfterUpdate() section before "Me.Repaint" and nothing changed.

dmyoungsal said:

I would rather have the screen update while the user is on the record they are working on.

Click to expand...

AfterUpdate doesn't fire until the record is updated so OnChange is the correct event.

The crux of the problem is, since the record has not yet been saved, you must use the Text property of the control in your comparisons.

I would use ConditionalFormatting to set the colours. Me.Repaint is required.

here is my Sub Form programming where fields are checked and colors changed in the fields I use when the new record is displayed

the last four lines are the AfterUPdate for the specific combo field.

+++++++++++++++++++++++++++++++++


Private Sub Form_AfterUpdate()
Me.Repaint
End Sub
Public Sub Form_Current()
If InStr(1, RentalType, "Sold", 1) Then
LastHourUpdate.BackColor = vbGreen
RentedOutTo.BackColor = vbGreen
RentalType.BackColor = vbGreen
ElseIf InStr(1, RentalType, "Purchase", 1) Then
LastHourUpdate.BackColor = vbBlue
LastHourUpdate.ForeColor = vbWhite
RentedOutTo.BackColor = vbBlue
RentedOutTo.ForeColor = vbWhite
RentalType.BackColor = vbBlue
RentalType.ForeColor = vbWhite
ElseIf InStr(1, RentalType, "PARtner", 1) Then
LastHourUpdate.BackColor = vbCyan
LastHourUpdate.ForeColor = vbBlack
RentedOutTo.BackColor = vbCyan
RentedOutTo.ForeColor = vbBlack
RentalType.BackColor = vbCyan
RentalType.ForeColor = vbBlack

Else
LastHourUpdate.BackColor = vbWhite
LastHourUpdate.ForeColor = vbBlack
RentedOutTo.BackColor = vbWhite
RentedOutTo.ForeColor = vbBlack
RentalType.BackColor = vbWhite
RentalType.ForeColor = vbBlack

End If
If IsNull([Purchase Price]) Then
[Purchase Price].BackColor = vbYellow
Else
[Purchase Price].BackColor = vbWhite
End If

End Sub
Private Sub RentalType_AfterUpdate()
Me.Repaint
End Sub

This doesn't affect your problem but it is a matter of efficiency -- When you use "Me." to qualify your control names, Access knows where the fields are defined - Me = the form's class module. When you don't use "Me.", you force Access to search all loaded libraries until it finds the code where the variable is defined. Also, using "Me." gives you intellisense which saves a lot of typing.

Somehow I doubt that repainting the form BEFORE you change the properties of controls will actually do anything. Try moving the repaint to the end of the routine. We also told you to use the AfterUpdate event of the control that is controlling the color.

I got my problem solved. After searching other resources someone mentioned calling the sub routine that performed the desired action and then repaint.

so the resulting code in AfterUpdate was:
Call Function_Current()
Me.Repaint

Voila! the text boxes I needed to change colors, changed colors and done.

Please post the code for Function_Current()

scroll up the thread and find my Jan 16 post:

Look for "Public Sub Form_Current()"

Its all the lines between that and ends and the first "End Sub"