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,
19 2522
Where is the "field date" you are referring to? Is it tied to the form as a textbox?
comteck
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,
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!
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!
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!
Are you sure it's "Cod-itional" formatting, and not "Con-ditional"? Sounds fishy to me.
comteck
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()?
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()?
I get a Compile Error: Method or data member not found when updating the EXP1 field. The .BackColor is bieng highlighted.
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.
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?
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?
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.
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.
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?
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?
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
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 ="
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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
|
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...
|
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...
| |