Coditional Formatting in Form

[tymperance]

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,

[comteck]

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

comteck

[MMcCarthy]

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,

[tymperance]

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!

[tymperance]

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!

[MMcCarthy]

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_A fterUpdate()

If Me.Exp1.Value > Date And Me.Exp1.Value <= Me.UserInputDat e.Value Then

Me.Exp1.BackCol or = 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!

[comteck]

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

comteck

[tymperance]

HeHeHe. Oops!

[tymperance]

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()?

[MMcCarthy]

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_AfterUpdat e()

If Me.LEXP.Value > Date And Me.LEXP.Value <= Me.EXP1.Value Then
Me.LEXP.BackCol or = 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()?

[tymperance]

I get a Compile Error: Method or data member not found when updating the EXP1 field. The .BackColor is bieng highlighted.

[MMcCarthy]

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.

[tymperance]

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?

[MMcCarthy]

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?

[tymperance]

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.