473,323 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Coditional Formatting in Form

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
19 2522
comteck
179 100+
Where is the "field date" you are referring to? Is it tied to the form as a textbox?

comteck
Aug 22 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
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
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
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
14,534 Expert Mod 8TB
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
comteck
179 100+
Are you sure it's "Cod-itional" formatting, and not "Con-ditional"? Sounds fishy to me.

comteck
Aug 24 '06 #7
HeHeHe. Oops!
Aug 25 '06 #8
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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

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

Similar topics

3
by: Jouke Langhout | last post by:
Hello all! For quite some time now, I've got the following problem: Access won't close properly when a user closes the application. An ACCESS process stays active and that process can only be...
4
by: DBQueen | last post by:
I have a subform which is in Continuous Forms view. I have added a button to the bottom of the page to move to the next record using the button wizard (result: DoCmd.GoToRecord , , acNext). I...
4
by: Bradley | last post by:
I have an A2000 database in which I have a continuous form with a tick box. There is also a text box with a conditional format that is based on the expression , if it's true then change the...
2
by: Von Bailey | last post by:
I have a form where the conditional formatting is set on some fields to bold if certain conditions are met. However, when the conditions are met some of the data that is to bold is either not...
5
by: Andrew Chanter | last post by:
Does anyone know a way you can use conditional formatting to create a banded style view as is commonly seen on the internet. (In othe words the first record appears on a gray background, the 2nd...
12
by: dmieluk | last post by:
Problem: When moving between records, I need to control which combo/text boxes are displayed on the current form, dependent upon data in the current record. More: I building my first...
0
by: GGerard | last post by:
Hello With MSAccess 2000, I have created a program with forms and applied some Conditional Formatting on some of the text box.
8
by: Typehigh | last post by:
I have many text fields with conditional formatting applied, specifically when the condition is "Field Has Focus". Without any events associated with the fields the conditional formatting works...
4
by: midlothian | last post by:
Hello, I have conditional formatting set up on a subform based on a calculated value in the underlying query. For instance, if Sales are >$1000, the query displays "Yes," otherwise it displays...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.