By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,816 Members | 2,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,816 IT Pros & Developers. It's quick & easy.

Coditional Formatting in Form

P: 33
I have a datasheet form attached to a query that is searching for Expiration dates in four fields. The user inputs a date (query has [Enter Expiration Date] on different rows for each field so the user only inputs one date). On the form I need to be able to say if the field date is > Date() and <= the user inputted date return a specific color. My issue is writing the expression for the user inputed date since it is not a field stored to my form. Is this possible?

Thanks,
Aug 22 '06 #1
Share this Question
Share on Google+
19 Replies


100+
P: 179
Where is the "field date" you are referring to? Is it tied to the form as a textbox?

comteck
Aug 22 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Because you have the user entering this date based on conditional formatting in the query it is not being stored as a separate entity.

The only thing I can suggest is that you make the datasheet a subform of another form on which you have the user enter a date into an unbound field. you can then set the value of this field as the conditional criteria instead of the Entry Prompts.

You can then reference this value as it is separately stored.

I have a datasheet form attached to a query that is searching for Expiration dates in four fields. The user inputs a date (query has [Enter Expiration Date] on different rows for each field so the user only inputs one date). On the form I need to be able to say if the field date is > Date() and <= the user inputted date return a specific color. My issue is writing the expression for the user inputed date since it is not a field stored to my form. Is this possible?

Thanks,
Aug 22 '06 #3

P: 33
Because you have the user entering this date based on conditional formatting in the query it is not being stored as a separate entity.

The only thing I can suggest is that you make the datasheet a subform of another form on which you have the user enter a date into an unbound field. you can then set the value of this field as the conditional criteria instead of the Entry Prompts.

You can then reference this value as it is separately stored.
Thank You!

I did that and I think it will work. I'm guessing I need an Update After event or something like that to have the subform update once the date is entered. I'm new at this and have no idea how to begin. Should I use the property After Update [Event Procedure] or do I need to write it in VBA? How do I do either?

Thanks!
Aug 23 '06 #4

P: 33
Where is the "field date" you are referring to? Is it tied to the form as a textbox?

comteck
No, it wasn't. It was an EXP1 field added to the underlying table. Since then I have added a form to catpure the date and then made my original form a subform. Only problem is I can't the the subform to update when the new form is changed.

Thanks!
Aug 23 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly, have you set the RecordSource of the new form to the table or query containing your Exp1 date field.

If so, then make sure the Exp1 field is on the form as well as the user input field.

Then in the After Update event of the user input field put a code something like this:

Private Sub UserInputDate_AfterUpdate()

If Me.Exp1.Value > Date And Me.Exp1.Value <= Me.UserInputDate.Value Then

Me.Exp1.BackColor = 16711680 ' Blue

End If

End Sub

Thank You!

I did that and I think it will work. I'm guessing I need an Update After event or something like that to have the subform update once the date is entered. I'm new at this and have no idea how to begin. Should I use the property After Update [Event Procedure] or do I need to write it in VBA? How do I do either?

Thanks!
Aug 23 '06 #6

100+
P: 179
Are you sure it's "Cod-itional" formatting, and not "Con-ditional"? Sounds fishy to me.

comteck
Aug 24 '06 #7

P: 33
HeHeHe. Oops!
Aug 25 '06 #8

P: 33
Thanks so much for the reply!

Since I am using a form to capture the EXP1 (expiration date) I am no longer having the pop-up for the user to input the date. The user will input directly to EXP1. This is stored on form Expiration Date Field. The subform on this is Expired Numbers and both forms RecordSource is Expired Numbers query. Now that I have changed this I need to write it so that IF field LEXP is > DATE() and <= EXP1 then BackColor is blue.

I tried making modifications to what you gave me to make this work and it still is not. Would I store this on EXP1's or on LEXP's AfterUpdate()?
Aug 25 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Since the user is entering EXP1 that is the field being updated. Therefore the After Update event should be on EXP1 as follows:

Private Sub EXP1_AfterUpdate()

If Me.LEXP.Value > Date And Me.LEXP.Value <= Me.EXP1.Value Then
Me.LEXP.BackColor = 16711680 ' Blue
End If

End Sub


Thanks so much for the reply!

Since I am using a form to capture the EXP1 (expiration date) I am no longer having the pop-up for the user to input the date. The user will input directly to EXP1. This is stored on form Expiration Date Field. The subform on this is Expired Numbers and both forms RecordSource is Expired Numbers query. Now that I have changed this I need to write it so that IF field LEXP is > DATE() and <= EXP1 then BackColor is blue.

I tried making modifications to what you gave me to make this work and it still is not. Would I store this on EXP1's or on LEXP's AfterUpdate()?
Aug 27 '06 #10

P: 33
I get a Compile Error: Method or data member not found when updating the EXP1 field. The .BackColor is bieng highlighted.
Aug 29 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Is LEXP a textbox or some other type of control.

When you type

Me.LEXP.

do you get a dropdown list including BackColor?



I get a Compile Error: Method or data member not found when updating the EXP1 field. The .BackColor is bieng highlighted.
Aug 29 '06 #12

P: 33
Yes it is a text box and the dropdown list only has Value on it. Is that a problem?

Is LEXP a textbox or some other type of control.

When you type

Me.LEXP.

do you get a dropdown list including BackColor?
Sep 7 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
It sounds like you're missing a library reference. Go to Tools - References and let me know what libraries are ticked there.



Yes it is a text box and the dropdown list only has Value on it. Is that a problem?
Sep 7 '06 #14

P: 33
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3 Microsoft Offixe XP Web Components

That's it.

It sounds like you're missing a library reference. Go to Tools - References and let me know what libraries are ticked there.
Sep 7 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Try adding Microsoft ActiveX Data Object 2.x Library but I don't think that's your problem.

Where exactly are you trying to enter this code?

Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3 Microsoft Offixe XP Web Components

That's it.
Sep 7 '06 #16

P: 33
I have the form in Design Mode and am going to the Code The date field is EXP1 so I have

Private Sub EXP1_AfterUpdate()

If Me.LEXP.Value > Date And Me.LEXP.Value <= Me.EXP1.Value Then
Me.LEXP.BackColor = 16711680 ' Blue
End If

End Sub

However, when the EXP1 field is entered the form itself does not update until the user clicks the update button, this is what populates the records and the LEXP field that would need to be colored blue. Could this be the issue, that when the AfterUpdate is being called there are no records to evaluate until the user updates the form?

Try adding Microsoft ActiveX Data Object 2.x Library but I don't think that's your problem.

Where exactly are you trying to enter this code?
Sep 8 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
That shouldn't be a problem as you are accessing the values currently on the form. Add the following line of code to the form. It will tell you
a) If the IF statement is being entered into at all
b) What value is in LEXP at that time

Private Sub EXP1_AfterUpdate()

If Me.LEXP.Value > Date And Me.LEXP.Value <= Me.EXP1.Value Then
Me.LEXP.BackColor = 16711680 ' Blue
Msgbox Me.LEXP
End If

End Sub


I have the form in Design Mode and am going to the Code The date field is EXP1 so I have

Private Sub EXP1_AfterUpdate()

If Me.LEXP.Value > Date And Me.LEXP.Value <= Me.EXP1.Value Then
Me.LEXP.BackColor = 16711680 ' Blue
Msgbox Me.LEXP
End If

End Sub

However, when the EXP1 field is entered the form itself does not update until the user clicks the update button, this is what populates the records and the LEXP field that would need to be colored blue. Could this be the issue, that when the AfterUpdate is being called there are no records to evaluate until the user updates the form?
Sep 8 '06 #18

P: 33
It didn't change anything, I'm still getting the Compile Error: Method or data member not found, it's taking me to the debugger and highlighting ".BackColor ="
That shouldn't be a problem as you are accessing the values currently on the form. Add the following line of code to the form. It will tell you
a) If the IF statement is being entered into at all
b) What value is in LEXP at that time

Private Sub EXP1_AfterUpdate()

If Me.LEXP.Value > Date And Me.LEXP.Value <= Me.EXP1.Value Then
Me.LEXP.BackColor = 16711680 ' Blue
Msgbox Me.LEXP
End If

End Sub
Sep 8 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm just trying to see what value is in LEXP

comment out the line
Me.LEXP.BackColor = 16711680 ' Blue

The value in LEXP should show in the msgbox.

It didn't change anything, I'm still getting the Compile Error: Method or data member not found, it's taking me to the debugger and highlighting ".BackColor ="
Sep 8 '06 #20

Post your reply

Sign in to post your reply or Sign up for a free account.