Conditional Format Based On Previous And Current Record

Feb 4, 2005

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 Replies


ADVERTISEMENT

Dynamically Update Field Of A Current Record Based On Previous Record

Apr 30, 2007

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 1 Replies View Related

Reports :: Conditional Format Date Field For Current Month

May 20, 2014

I have a short date field 5/20/2014

I would like to set up a conditional format to format those fields in the current month. For example, this month is would format all fields with "5".

Next month all fields with "6"

In the month space.

I can't seem to figure out the expression, I know I'm close.

View 4 Replies View Related

Conditional Format Based On 2 Fields

Jan 24, 2008

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 6 Replies View Related

Conditional Format Form Based On Text In Txtfield

Sep 14, 2006

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 2 Replies View Related

Queries :: Evaluating Current Record To Previous Record To Get Counts

Aug 8, 2014

I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.

During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.

Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.

I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.

My attempted query: DLookUp("[Team]","[Team_qry]","[Team_qry]![Date]=#" & CStr(DMax("[Date]","[Team_qry]","[Team_qry]![Date] <#" & CStr([Date]) & "#")) & "#")

View 3 Replies View Related

Refer To Previous Record In Current Record Query

Sep 15, 2005

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 6 Replies View Related

Compare Current To Previous Record In Query

Aug 14, 2007

I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?

View 2 Replies View Related

Queries :: Datediff From Previous To Current Record

Oct 10, 2013

I want to get the datediff of the last returned date from vacation and the current vacation start date. But how to get difference from the previous record's date to this current record's date. I want to show date difference in an unbound textbox. I have a contiuous form which shows all vacation list of employees. It shows how many times he went vacations and when he went and when he returned.

View 14 Replies View Related

Conditional Format All Fields In ACTIVE Record

Feb 1, 2005

: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 5 Replies View Related

Forms :: Return To Previous Current Record After Requery

Jul 30, 2013

I have a Main form that has button that loads a new Pop Up form for entering new data that will display in the Main form. When all the data is entered I click on a button that Saves the data and exits the form and then runs Re-query on the main form returning to the record that was current before the requery.

I have the following code:

Private Sub cmdSaveTradeAndExit_Click()
DoCmd.RunCommand acCmdSaveRecord 'Save the current record
DoCmd.Close 'Close the current form
Dim CrId As Integer
CrId = Forms!frmTransactionMainActivePopUp.CurrentRecord
Forms!frmTransactionMainActivePopUp.Requery
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId
End Sub

But I am getting the following error:

Run-time error '2498':

An expression you entered is the wrong data type for one of the arguments

And the following is in yellow in the debug:

Code:
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId

View 4 Replies View Related

Modules & VBA :: Subtracting Value On Current Record From The Value Of Previous Records?

Jun 5, 2013

I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))

This is how I generate the sysBaseNumber

sysBaseNumber = Nz(DMax("sysBaseNumber", "tblSystemConfiguration", "sysAccountID=" & Forms!frmTempestCoordination!accAccountID), 0) + 1

I am using DMax so I can keep the subform records with the parent form record.

My results are inconsistant:

This is what I want the subform to do when subtracking from the previous record

syshop1 sysHopSpacing
5
20 15
30 10

Instead I get a result that does not make sense like this:

sysHop1 sysHopSpacing
5
20 15
30 4

I cant trace it down.

View 14 Replies View Related

Auto Increment A Number Based On Record Value From Previous Record

Nov 17, 2010

create a macro that automatically fills in the next invoice number in sequence",,I'm assuming this macro would look at the value of the previous record and add a one to itI don't want to use a AutoNumber field as I might need

(a) to modify value or
(b) need to skip numbers.

View 8 Replies View Related

Forms :: Email Current Record In PDF Format

May 23, 2013

I am new to Access. I am using Access 2010. I have searched and found several articles on emailing the current record shown on a form but haven't been able to get any of them to work.

My goal is to have a user input information onto the form and then have one button the user will click that will save the current record, email the current record in pdf, and then close the form.

The form name is: frmCorrectiveActions. The unique identifier for a record is: Corrective Action ID.

The last coding I tried is below. I know something is wrong but have copied this code from another post about emailing forms so I'm not even sure what half of it means.

I am getting the message Run-time error '3075': Syntax error (missing operator) in query expression "Corrective Actions ID = 23' and when I hit debug this is highlighted: qry.SQL = strSQL

form Private Sub Email_Click()
Dim qry As DAO.QueryDef
Dim strSQL As String
Dim ReportQueryName As String
ReportQueryName = "qryCorrectiveActions"
Set qry = CurrentDb.QueryDefs(ReportQueryName)

[Code] ....

View 5 Replies View Related

Updating A Field Based On Previous Record

Apr 3, 2008

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 1 Replies View Related

Forms :: Conditional Formatting Based Upon Presence Of A Record

Mar 13, 2014

I have a subform (called NAMES) on a main form (called MAIN) that lists names. Elsewhere on MAIN, I have a second subform (called ASSIGNED) that lists people that have been assigned.

When I double click a name on NAMES, it automatically adds them to the ASSIGNED list (who's record source is a table). What I want the NAMES form to do is conditionally format the NAMES form so that the record that's been added turns green. That way I can see who I've added and who I haven't yet added. Once I've added them all, the NAMES form is a simple list of all-green names. If there's a black name in the list, it's because I haven't yet added them to ASSIGNED.

I've tried a DLookup function, and I've tried a Value= conditional formatting rule, and neither one are working... I already got the 'double click to add' thing working, now I just need the name to turn green when I've added them.

View 11 Replies View Related

Modules & VBA :: Autofill Cell Based On Previous Record Value

Aug 26, 2014

I am trying to write some vba code to auto fill a cell's value based on the value of the cell in the previous record (In a datasheet form) i.e. if the value in record one was '1' and the down-arrow key was pressed then the value '2' should be entered into the new ext record

I have written some pseudo-code to show what I am trying to accomplish:

if keydown = down-arrow and current cell contents isnumeric then
Cval = current cell contents
if current record = last record then
create new record
move down 1 record
set cell value of new record to cval+1
else
move down 1 record
if cell value = null then set cell value of record to cval+1
end if
end if

View 14 Replies View Related

Forms :: Autoupdate Textbox Based On Previous Record

Jun 17, 2013

I am creating a database for an annual golf tournament. Part of this is to allocate players to play off together at certain times.

I have a form bound to a table called tblteeofftimes. This form has five fields (continuous form) - four of which are comboboxes whereby user selects each player for the record (who will play together). The other textbox has the tee off time. the first tee off time will be allocated by user eg 7:00 am.

My problem is I need to update each ensuing record with a new time slot at 7 minute intervals. Rather than have the user populate the form themselves I would prefer this be done authomatically as the user moves onto the next record.

View 12 Replies View Related

Modules & VBA :: Conditional Reporting Based On Value Of A Field In Selected Record

Mar 27, 2014

I have the following code that I need to modify:

Code:

Private Sub ChargeReport_Click()
On Error GoTo Err_ChargeReport_Click
Dim stDocName As String
stDocName = "Charges_Report"
DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , "Charge Sheet"

[Code] ....

Currently, this code opens an input box that accepts the ID number for a particular record. Then it attaches a specific report for the selected record to an email. It works fine for this purpose.

I want to modify it so that it attaches one of several different reports depending upon the value of a [Staff_ID] field in the selected record.

I've tried a number of different solutions using an InputBox to get the record ID along with an If/ElseIf/Else construct that evaluates the [Staff_ID] field in order to determine which report to attach to the email, but I cannot find my error.

View 4 Replies View Related

Forms :: Display Data Based On Previous Record But Not All Fields

Jan 22, 2015

There is a form which the user fills in 8 fields and saves it. Once saved, a new record opens.

However, I want to modify the save button to save the previous record and display a new record with only a limited no of fields to display from previous record, say 4 fields display from previous record, and the user fills the remaining 4 and saves it. Thus, the save cmd should continue to display 4 fields from the previous record.Already tried below code for save button but it displays all data of previous record as it is, doesn't display blank space for combo.118 and text2

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
Combo118.Text = ""
text2.Text=""

View 1 Replies View Related

Queries :: How To Create Auto-grouping Based On Change In Previous Record

Jun 29, 2015

I have a column "CAT" each time that CAT switches between 0 and 1, I would like my query to auto-create a "grouping" and increment the group by 1. What I am looking for is the output as shown below.

ID CAT GROUP
67 0 1
68 0 1
69 0 1
70 1 2
71 1 2
72 0 3
73 0 3
74 0 3
75 0 3
76 1 4
77 1 4

View 6 Replies View Related

General :: Meter Reading Table - Calculation Based On Previous Record

Aug 2, 2013

I have a table full of meter readings. I want to have a field called consumption which looks at the reading just entered for that month and then subtracts the previous months reading for that meter which leaves the consumption.

Can I get a formula that can work this out automatically?

View 3 Replies View Related

Query Based On Current Record

Aug 23, 2007

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 3 Replies View Related

Combo List Based On Current Record

Aug 23, 2007

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 4 Replies View Related

Query Based On Current Record Selection

Jan 8, 2006

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 1 Replies View Related

Printing A Report Based On The Current (new) Record

Nov 23, 2006

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 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved