Control Source in Forms and Reports

[confusionguy]

Hi there, encountered some problems when I'm dealing with the forms and report.

I have a Form and Report based on a Query. However, I need to add in additional information (not stored in tables and not created in query) to display in the form and report. Such information are the status of the orders and the appropriate actions to it. These are information will be derived from fields in my query.

1 eg: if Aging is more than 4 days, I need the additional field to automatically display as overdue.

I tried to add in an unbound field in the form and report and have an expression built in the control source property of the field. But it displays #Name or #Error.

Is there any way to go around it as I wanna minimise data entry in the forms.

Shd a table or query field be created for that to work? A field in table and query doesn't allow formula or expression to be built.

I appreciate any help that could be given to solve my problems.

Thank you very much.

[schlauberger]

You could add a column to your query to evaluate your data.

SELECT Age, IIF([Age]>4,"Overdue","") AS FieldName FROM TableName

[xordevoreaux]

When I build forms, because what winds up on the forms can be from any given source at any given time, I do not associate a query or table to the recordsource, rowsource, or recordset properties of the form. Instead, I have an unbound form with unbound controls, and I use ADODB recordsets to grab the data I need directly, which is stored in the recordset as I need it (typically one record winds up in the recordset).

I have code in the form module that populates the form's control values from the recordset.

This puts you 100% in control of what winds up on the form, but it also means that you have 100% responsibility for the interactivty of the form; none of the ordinary tasks associated with OnCurrent events, etc., etc., work in the background, because there's never a current record associated to the form.

Using ADODB recordsets on an unbound form frees you to do strange things with content, and if you want to change the what/where/when of data displayed, you can do it. It's a bit rough tho, since you also have to write code to support whatever CRUD (create/read/update/delete) functions that you want the user to perform.

If you're building something small for yourself with a short shelf-life, I wouldn't invest the time in this. However, if you must incorporate a business case model into the interoperability of the database, then I highly recommend going this way. If you suspect that this suggestion might take you down a road that you are not prepared to take, either in terms of time or learning curve, then your second-best option is as schlauberger suggested it -- place a conditionally populated field in the query associated to the form.