Conditional Format Based On Previous And Current Record
I'm having trouble with a tricky conditional format in a continuous subform.
The criteria is based on field [GroupNo] when it changes (increases) which are always listed in numerical order.
Basically all I want to is for the text box colour to alterante (eg blue & yellow)when GroupNo changes value
So it would look something like this....
Group NoID (highlight colour)
1 (yellow)
1 (yellow)
1 (yellow)
2 (blue)
2 (blue)
2 (blue)
3 (yellow)
3 (yellow)
etc etc
btw, although values will always be in order they may not all be output, so it's not just case of odd, even, odd, even (that would be too easy!)
I can retrieve the Previous Record's GroupNo value (via a function) in another text box and compare current GroupNoID with this in the Format Expressions and manage to change the colour, but it won't sustain this new colour for the subsequent same GroupNos, obviously as the values have changed.
So how can I....
Change the colour when the value changes.
Keep this new colour until it the value changes (increases) again.
Many Thanks
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Dynamically Update Field Of A Current Record Based On Previous Record
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1". So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record. I have a report that will use these results to count only the number of records that have a "WWW" in "field_2". Is this doable, maybe in a query somehow? I should add that whatever the solution, it needs to be compatible with Access 2000.
View Replies !
View Related
Conditional Format Based On 2 Fields
Hello All, I have searched the forum but I don't quite get what I am looking for. I have a subform that has these 2 fields: - Status - LastFollowup What I want is if the Status shows "Pending" and the date entered on the LastFollowup is more than 7 days. Then it should turn Yellow. I tried this code in the expression box but it doesn't seem to work [Status]="Pending" and [LastFollowup]>7 Could anyone please assist? I know this is simple but it's not working for me. Thank you
View Replies !
View Related
Refer To Previous Record In Current Record Query
In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e. Name Value Previous Record 1 1 Record 2 2 1 Record 3 7 2 Record 4 1 7 Is it possible to create some kind of simple expression to refer to data in another record? Cheers, Ben
View Replies !
View Related
Conditional Format Form Based On Text In Txtfield
Hi All, I'd lile to show/hide selected subforms based on the the text in a txtfield. The text in the selection field (txtSelect) I'd like to use for that may be either one of the following: book-01, book-02, book-03 etc OR, folder-01, folder-02, folder-03 etc OR, record-01, record-02 record-03 etc. OR it can be just a 4 digit number. If the txtSelect contains "book" I'd like the subfrmBookdetails to be visible. If the txtSelect contains "folder" obviously the subfrmFolderdetails etc. What I'm struggling with is the If-Then statement for selecting the partial text in txtselect. I've tried things like this: On current me.txtSelect.setfocus if me.txtSelect.text="book*" then me.subfrmBookdetails.visible=true etc etc. No joy. I know how to get this to work in a query with a like-statement but I have not been able to find anything on this in relation to conditional formatting of a form. Any one and good ideas? Thanks, Walter
View Replies !
View Related
Updating A Field Based On Previous Record
I'm trying to update an imported table from an excel spreadsheet with missing details. The table's records are in order so I just need to fill in a blank field with data based on the previous one as shown. ID Name Location 1 Bob London 2 Larry 3 Harry 4 Jerry Glasgow 5 Paul 6 John Southampton I need to fill in the location blanks simply with the last location details, so records 2 and 3 with London and 5 with Glasgow. Is this easily done or would I need to pull all the data into an array and work on it there? I've tried searching for an answer but haven't had any luck.
View Replies !
View Related
Conditional Format All Fields In ACTIVE Record
:D I am using a continuous form, and I cannot figure out what conditional formatting expression to use to change controls in the ACTIVE RECORD. For example, continuous form may have: RECORD1: Field 1 Field 2 Field 3 Field 4 RECORD2: Field 1 Field 2 Field 3 Field 4 RECORD3: Field 1 Field 2 Field 3 Field 4 When I am in Field 1,2,3, or 4 in RECORD2, I want Fields 1,2,3 AND 4 to change color in RECORD2 PLEASE HELP!
View Replies !
View Related
Query Based On Current Record
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way. I have three tables; Contacts, Companies, Company_Addresses Contacts: ContactID (P) CompanyID OfficeName Companies: CompanyID (P) Company_Addresses: CompanyID OfficeName (P) What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field. I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated. Sorry if this is in the wrong forum. Thanks. James
View Replies !
View Related
Combo List Based On Current Record
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way. I have three tables; Contacts, Companies, Company_Addresses Contacts: ContactID (P) CompanyID OfficeName Companies: CompanyID (P) Company_Addresses: CompanyID OfficeName (P) What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field. I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated. Sorry if this is in the wrong forum. Thanks. James
View Replies !
View Related
Query Based On Current Record Selection
right, struggling here. Basically I have continuous form listing all the standard letters sent out when I double click on a letter, I need to to run a query for me. The code I have put behind the button is: If ([StandardLetterType] = "Homework") Then DoCmd.OpenQuery ("qryHomeworkClearMailMerge") DoCmd.OpenQuery ("MergeHomework"), , , , , acDialog, Me.[StandardLetterID] DoCmd.OpenReport ("rptHomework"), acViewPreview Else ' end if and in the query I am first clearing the mailmerge yes/no field out which works fine and then running the merge to put the yes back in the current record so the query looks like this Its an update query with update to yes for mailmerge and under the standard letter ID i have the criteria [Me].[OpenArgs] is this completely wrong tried the [Forms!][frmFullStudentReport].[standardletterID] but it kept popping the box up asking for the number. On the form I have the standardletterID and its hidden but there. Any help would be greatly appreciated
View Replies !
View Related
Printing A Report Based On The Current (new) Record
Hi All, Having a problem with a report, I have a form that i use to enter a new record to my database, what I want to do is put a button at the bottom of the form to print the record (via a report) If I base this on a record that already exists, it works fine. However, If I have just entered the record the query the report runs off doesn't see the new record. To print that record I have to exit the completed record, then go back in to it and print. My aim is to remove this additional part of the process, I'm sure it's something really simple, but can anyone help? Many Thanks Paul
View Replies !
View Related
Filter A Listbox Based On Current Record
Hi I have a form which displays records of our products, on that form I have now put a list box which displays the ancillaries to the products. both the form and the list box run off queries. I have the same unique identifiers in both queries. I need to filter the listbox by the unique identifier every time I move record. At the moment the list box stays populated with all the ancillaries, how do I filter it programatically to list only the ancillaries with the same unique identifiers as in the current record? Thanks a million....
View Replies !
View Related
Print Single Record Based Upon Current Form
I need to Print a single record from a form but using a report layout. I have created the form and created a report. A button on the Form kciks off the print but I get all records rather than just the one shown on the Form. Whats a really simple way of just selecting the Current record. Where do I put an instruction saying print only this record:confused:
View Replies !
View Related
Getting Data From One Table Based On Current Viewing Record
Hey all, I am working on converting someones Paradox Database to Access and making some modifications. I have a table with people in it and they are linked to a number of interests. But i am struggling to create a query so that on each page of the form for the person i can call the interests and display them on the form as a list... If anyone could point me in the right direction that woul dbe appreciated! Give me mysql and a bit of php anyday!! hehe I am using Access 2007 but i have used access before so i should be able to work my way through instructions for 2003. thanks RF
View Replies !
View Related
Values Older Than Current & Previous
Hi, I'm new to using access SQL , I'll appreciate if someone can provide the where clause of the statement below: Provide the count of assets with values older than current and previous versions. This is needed to determine if we can upload these assets manually. thanks b2bmike
View Replies !
View Related
Problem With Harvesting Records By Date Spaning The Previous And Current Years
Im working on a make-table query that harvest records for calculation that span a "rolling" 13 week history... my problem is that the query will work if it doesn't have to retrieve work weeks that are prior to the new year. For example, right now is the tenth week of this year so i need to grab the previous 9 weeks of history plus 4 weeks of the prior year. then next week when the query is ran we will be in the 11th work week of the year therefor i will need to harvest the past 10 week plus 3 week of the preivious year. the query works if you don't have to span accross calendar years. here is the SQL of the query below the problem area is in underlined in bold type: SELECT PORVPHIS.VENDOR_ID, PORVPHIS.VEND_NAME, [Expr2] AS [Fiscal Year], DatePart("yyyy",[DATE_RECV]) AS Expr2, DatePart("ww",[DATE_RECV]) AS Expr1, PORVPHIS.QTY_RECVD, PORVPHIS.PO_UNT_PRC, [PO_UNT_PRC]*[qty_recvd] AS [Total Price], [Expr1] AS [Fiscal Week], IIf([fiscal week]=[LowWeek] And [Expr3]<>0,[total price],0) AS 1, IIf([fiscal Week]=[LowWeek]+1 And [Expr3]<>0,[total price],0) AS 2, IIf([fiscal Week]=[LowWeek]+2 And [Expr3]<>0,[total price],0) AS 3, IIf([fiscal Week]=[LowWeek]+3 And [Expr3]<>0,[total price],0) AS 4, IIf([fiscal Week]=[LowWeek]+4 And [Expr3]<>0,[total price],0) AS 5, IIf([fiscal Week]=[LowWeek]+5 And [Expr3]<>0,[total price],0) AS 6, IIf([fiscal Week]=[LowWeek]+6 And [Expr3]<>0,[total price],0) AS 7, IIf([fiscal Week]=[LowWeek]+7 And [Expr3]<>0,[total price],0) AS 8, IIf([fiscal Week]=[LowWeek]+8 And [Expr3]<>0,[total price],0) AS 9, IIf([fiscal Week]=[LowWeek]+9 And [Expr3]<>0,[total price],0) AS 10, IIf([fiscal Week]=[LowWeek]+10 And [Expr3]<>0,[total price],0) AS 11, IIf([fiscal Week]=[LowWeek]+11 And [Expr3]<>0,[total price],0) AS 12, IIf([fiscal Week]=[LowWeek]+12 And [Expr3]<>0,[total price],0) AS 13, DatePart("ww",Now())-13 AS LowWeek, IIf([fiscal Week]>=[LowWeek] And [fiscal year]<>DatePart("yyyy",Now()),0,[LowWeek]) AS Expr3, [LowWeek] AS week1, [LowWeek]+1 AS week2, [LowWeek]+2 AS week3, [LowWeek]+3 AS week4, [LowWeek]+4 AS week5, [LowWeek]+5 AS week6, [LowWeek]+6 AS week7, [LowWeek]+7 AS week8, [LowWeek]+8 AS week9, [LowWeek]+9 AS week10, [LowWeek]+10 AS week11, [LowWeek]+11 AS week12, [LowWeek]+12 AS week13, PORVPHIS.DATE_RECV, IIf([Expr4]="AS8819" Or [Expr4]="CO1870" Or [Expr4]="GV2510" Or [Expr4]="MC3105",[VEND_NAME],Null) AS ID, PORVPHIS.VENDOR_ID AS Expr4 INTO Tporv FROM PORVPHIS WHERE (((PORVPHIS.VENDOR_ID)="AS8819" Or (PORVPHIS.VENDOR_ID)="CO1870" Or (PORVPHIS.VENDOR_ID)="GV2510" Or (PORVPHIS.VENDOR_ID)="MC3105" Or (PORVPHIS.VENDOR_ID)="IM2680") AND ((DatePart("yyyy",[DATE_RECV])) Between DatePart("yyyy",Now())-1 And (DatePart("yyyy",Now()))) AND ((DatePart("ww",[DATE_RECV])) Between DatePart("ww",Now())-1 And (DatePart("ww",Now()))-13)); I hope some one can help me......you can email me by markphillipsii@bellsouth.net or mphillips@dccabinetry.com thanks Mark
View Replies !
View Related
Unhide Based On Previous Value
*Disclaimer-I am a self-taught Access developer, so please bear with me* I have been handed the task of developing a database to keep track of active and terminated security badges. I have a form that displays certain data for each employee. I have a check box on the form that signifies if the employee is active or terminated. The 'Term Date' box is on the form, but is currently disabled. I have been trying to find a way to have the 'Term Date' field enabled if the 'Active' checkbox is unchecked so that a termination date can be entered. Any suggestions? Please help!! I don't know enough about Access to determine if this is possible, but I also was wondering if it was possible to reverse the action if needed (re-check the 'Active' checkbox and re-disable the 'Term Date' field) I'd greatly appreciate any help that anyone can provide!!
View Replies !
View Related
Conditional Formatting Format
Hi, I am trying to place some code in the "expression is" section of conditional formatting. Need some help converting from code to conditional formatting format. I have set up a check box "Check83" next to a standard control box "Title(O)". Below is the code I am currently using. This code is in the after update section of the check box, and in the oncurrent section of the form. This code turns the box blue when I click the check box. Private Sub Check83_AfterUpdate() If Me.Check83 Then Me.[Title(O)].BackColor = vbBlue Else Me.[Title(O)].BackColor = vbWhite End If End Sub My form has 46 records and all have check boxes linked to the control box with the code above. When the box is blue in record 1, I don't want there to be a blue box in record 2. Can't figure out how to do this. Tried to do in with code, but failed. Thought it may be possible using conditional formatting. Thanks in advance for your help Jcwishart
View Replies !
View Related
Conditional Column Format
I wish to be able to change a subforms column format from the Currency type to Fixed type depending upon the value of a command button. Does anyone know how to change a subforms column format at runtime using VBA? Thanks Guido;)
View Replies !
View Related
Conditional Format Screen Flicker
I have continuous subforms having controls with conditional formatting which flicker for several seconds. I stopped the flicker by cycling through each record in the subform using a recordsetclone and settng focus to each control in the record. This stopped the flickering. I found this solution here (http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/4e64690fbd213b3e/197849ec8aa33dfe%23197849ec8aa33dfe?sa=X&oi=groupsr&start=0&num=3).
View Replies !
View Related
Conditional Format A Comman Button?
Quick question. I have a continuous form in Access 2003 listing products for return for a customers. I am in the process of creating a completion form which will just have the product number, model number and then a command button called complete. What I'm doing is have it so when the person clicks the button it adds a value to a field saying Complete. OK. But, what I then want it to do is for any record thats associated with this customer where the field = complete to have the command button gray out. Is this possible? I know I can conditional format a text box to do this but can't see a way to do it with a command button? Any ideas? I have though of layering a transparent button and having a textbox behind this with complete in and having this change colour but not sure if thats the best way?
View Replies !
View Related
Conditional Format Of Currency Field
Hello All, I would like to place a list of countries in Field A so that when I select say USA the currency amount in field B should show a "$" sign and when I select South Africa in Field A the currency amount in field B should display a "R" currency symbol etc. How does one do that. I have tried using the format command without success.
View Replies !
View Related
|
TRACKER
