Full Version: Continous form question
UtterAccess Forums > Microsoft® Access > Access Forms
Ilze
Hello there
This is not really very important but could be quite useful on the odd occasion.
When using a continuous form is it possible to get the value of a field in the previous record (row)? I would like to use this feature to set a conditional format on the first occurance of (let's say) a week number in a form that displays weekly customer sales. So the form is sorted by year, week and customer, and when the week changes I would like to use the Expression Is feature in conditional formatting to make the background of the new week something that stands out - like bright pink! The Excel equivalent would be if(R[-1]Cx <> RCx, True, False) and the conditional format is then set if the expression gives a true result.
Thanks:)
Ilze
niesz
The answer is ... yes. It is possible to do what you want. The method is a little different than with Excel and referencing a "previous" record. But you can do it.
Ilze
Great! Now for the million $ ?: How?
Thanks in advance:)
niesz
OOoooohh. You want to know how, eh? ohyeah.gif
irst a few ground rules.
1. To make this as easy as possible, every record should have a primary key to easily identify which is which.
2. The records should be sorted, so that you can count on them being returned in a specific order. Access does not necessarily store records sequentially, like Excel.
3. A continuous form does not really know anything about any other "instance" of a record.
The idea is not to try to figure out what a previous records value is, but rather to create your SQL to determine and carry forward the previous record's value onto the next.
Then Conditional Formatting can be applied using two fields in the same record.
So if your table has the following fields:
ID Year Week Customer
1 2008 1 23
2 2008 1 56
3 2008 2 24
You want to build a query that will return:
ID Year Week Customer PreviousRecordWeek
1 2008 1 23 Null
2 2008 1 56 1
3 2008 2 24 1
4 2008 3 42 2
This can be accomplished with a subquery or (a simpler but slower alternative) a DLookup().
Ilze
Ah! Sneaky! Will play around with this a bit.
Thanks so much:)
This is the BEST forum ever!
niesz
thumbup.gif uarulez2.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.