Formatting controls for individual records in continuous forms.

  • Thread starter Thread starter Jamie
  • Start date Start date
J

Jamie

I have a continuous form with two unbound text box controls. I want
to put information in these controls based on values of some of the
fields in the underlying query. I have a procedure that acts on the
form_current event that seems to do the work.

The problem is that all of the records get the same result which is
based on the values in the first record. If I click on another record
in the form, then all of the records take on the result of the
calculation for the record I just clicked on. This makes sense for
the one record since clicking on the record changes the current record
and calls form_current. What doesn't make sense is that all of the
records are affected.

I have tried keying off of other events besides form_current with no
success. I have also tried using conditional formatting for other
textbox controls in this form based on values of the record and get
the same effect. Is there a setting or procedure that I am not aware
of that will get the effect I really want, which is to have each
record formatted independently in the continuous form?
 
Hi Jamie,

As you've discovered, an unbound control can only have one value - even
though it is shown on multiple rows. What you might want to do is create a
calculated field in the RecordSource query for the form. If this won't
work, you might consider going to a paired list/detail form arrangement
where the list (either a listbox or continuous subform) is synchronized with
the subform which shows the details for the currently selected record in the
list.
 
Thank you for the information. I was hoping to avoid storing data
that can be calculated from the other information in the query as that
just doesn't sit well with my computer science instincts.
Unfortunately, I want to use a continuous form since the purpose is to
provide a summary of all of the records in another detail form without
having to use the record selectors to go through them as well as
highlights for certain records that are in a certain state. This way
it makes it easier for the user to skim through the recordset and find
the ones that are relavent to thier purposes.
 
Hi Jamie,

Your instincts are right but you can still have a calculated field in the
query without storing the calculated value:

Select Custid, Firstname & " " & LastName as Fullname from Customers;

In the above, LastName is a calculated field and can be used in your
Rowsource query and unlike an expression used as the ControlSource for a
control, it will have different values for each row since the expression is
evaluated at the query level (before it gets to the form).
 
The problem with this approach is that the data I want to put into the
textbox needs to be calculated by some VB code. This is because I
perform a lookup in a table that contains state information over a
given range. Therefore, I need to find out which range the value lies
in so I can perform the lookup correctly. As far as I can tell
though, you can only write module code for a form or report. Is there
a way to do the VB during the query or an option other than a VB
module?
 
Therefore, I need to find out which range the value lies
in so I can perform the lookup correctly.
Click to expand...

Eh? A Lookup - either a dlookup call or a SQL query - can use a range,
without using any VBA code.
As far as I can tell
though, you can only write module code for a form or report. Is there
a way to do the VB during the query or an option other than a VB
module?
Click to expand...

A Query can use a VBA function in a calculated field; simply type the
name of the function with its parameters in a vacant field cell.
 
Back
Top