Access If statement to sort through all records on datasheet view form

[Ekhart]

I have the following code 'On Open' and it works just fine, but it only seems to work for the first record when looking at Datasheet view. Is there any way to get it to go through all of the records on the sheet?
There are many of these criteria. I have tried adding these to the query as a Field value but you can only have 2 expressions in each



Example is in testing I have 3 records with a blank CIDDate, only the top record shows "Test" in the Alert field.

If IsNull([CIDDate]) Then
    [Alert] = "Test"
    Else
    [Alert] = ""
End If

[CJ_London]

the open event fires before any data is loaded (which happens in the load event), the current event fires when the focus is received for a record.

So a) your code is only apparently working and b) if Alert is an unbound field, it will show the same value for every record.

You need to add this to your from recordsource or make use of conditional formatting in some way, but I do not understand you comment ' have tried adding these to the query as a Field value but you can only have 2 expressions in each'. You can have up to 255 columns.

[orange]

Where exactly are you using this? Can you tell us a little more of your environment and database?

[Ekhart]

I am using this on the On Open event for a form. I want a spreadsheet type view that is editable which is why I am not using a report.
I have many strings in VBA that filter out the data I want shown. Example:

LastAction = 'Other' And IsNull(OtherDate) Or LastAction = 'Other' And OtherDate <" & Format(Dateval1day, "\#m/d/yyyy\#")

I have about 12 of these on this form. What I want in essense is a way to identify in the Alert field (an unbound field) is a simple quote as to why that particular record is showing. For the example above something like 'Other Date Error'. I know I could just have many different forms/queries so it is obvious what they are all on there for, but I would rather it all be on one. I can conditional format for each reason which colors the Alert column a different color for each reason, but it would be clearer to user if it just said a reason rather than giving them a color to link to a reason.

When I have tried adding columns to the query, one for each reason, I don't seem to have luck with something as above. It seems after 2 Or's or And's it loses it's mind.

[CJ_London]

where does your [Alert] come into it?

[orange]

Again not knowing the details of your set up, it sounds like you are validating fields in a record or input (proposed record) against a know list of criteria, and when an unacceptable value is found you are displaying a corresponding message/string.

Typically If then elseif else end if; or Select Case construct but we really need to hear more detail.

[Ekhart]

I am unsure what additional information you wish to have.

Information is entered and a record is added. When the report (in this case a form) is run it opens to Datasheet view and displays all records that require action at that time. Action can be 1 of many things ranging from being open for too long (CreateDate is more than 90 days ago and it is not Closed) to Tracking down records for an appointment (Appointment was scheduled, appointment date has passed, records have not yet been received). This part it does through a series of filters. I also have conditional formatting which applies colors to the unbound field 'Alerts', each color coordinating with a reason each record needs action taken.
I would simply like a word or small sentence in the Alert field vs. the colors from conditional formatting.

There is a 'list of criteria' if you will but not currently in Access. In a previous database that ran off of queries and reports, there was a query created for each 'alert' and then they were all merged together. I had to use separate queries for each because I could not figure out a way to add a separate 'Alert' for each set of criteria on the same query.

[orange]

I would simply like a word or small sentence in the Alert field

OK. If you have a tblAlert

AlertID autonumber PK
AlertShortDesc text
AlertLongDesc text

and I'll offer a few "made up" examples of records for this table to show the intent

1,"Address","The city provided in the address does not exist in the State City table"
2,"Address","The zip code provided is not valid"
3,"DeliveryDate","The DeliveryDate predates the transaction date"
4,"Product","The Product code has been superseded and is no longer valid"

You could design something specific based on your criteria list, and the area of validation involved.


In validation

'Validate Address"

If not Isnull(CustomerAddressCity) then
If DCount("CityName","tblStateCity","CityName ='" & tblCustomer.CustomerAddressCity) = 0 then
'Alert entry 1 applies, so Display data from TblAlert where AlertID = 1
.....
.......