Continuous Forms - Different Properties For Each Row (1 Viewer)

HairyArse · Jan 30, 2019

I have a form containing a sub form. The main form is for orders, the sub-form contains items against the order and is set to continuous form view.

We have a price field set to a double in the DB back-end that is generally displayed to 2 decimal places on the sub form.

Very occasionally I'd like to be able to display the price to 3 decimal places, but due to continuous forms being, well, continuous, changing a field's properties means that property is applied to every single row of the sub form.

Is there any way at all to have independent formatting or rules-based properties applied to fields to help me achieve what I want? My gut feeling and experience with Access and VBA over many years tells me not but I thought I'd try a last hail-mary and see if anyone had any suggestions?

MajP · Jan 30, 2019

If it is for display only, this could be a calculated field based that determines what to display. However, that will not be editable. You may be able to do some tricks and have the editable field behind the calculated field. When you enter the calculated field you set focus to the editable field. I would have to try it.

MajP · Jan 30, 2019

I will try to see and report back.

MajP · Jan 30, 2019

Seems to work
I have a field called Vals with a textbox txtVals
I have a field called Precision with how many decimal places

I have calculated txtbox called txtCalcVals with a control source of
FormatVals([Vals],[Precision])
This sits on top of txtVals

Code:

Private Sub Form_Current()
  Me.Recalc
End Sub
Public Function FormatVals(Vals As Variant, Precision As Variant) As String
  If Not IsNull(Vals) And Not IsNull(Precision) Then
    FormatVals = CStr(Vals)
    FormatVals = Left(FormatVals, InStr(FormatVals, ".") + Precision)
  End If
End Function

Private Sub txtCalcVals_KeyDown(KeyCode As Integer, Shift As Integer)
  Me.txtVals.SetFocus
End Sub

HairyArse · Jan 30, 2019

Sorry I don't think I explained myself properly. I'm not talking about calculations I'm talking about how the fields are presented on the form and actually, also in reports.

Say I have an order with three items.

I want items 1 and 3 to be displayed to 2 decimal places and item 2 to be displayed to 3.

Yes I can change the properties of the price field to show 3 dp but this will be applied to all items and not the individual rows.

MajP · Jan 30, 2019

Any chance you even read what I posted? You may want to read it again, and give it a try.

Micron · Jan 30, 2019

I have to wonder why a price field is set to double and not currency which will allow up to 15 decimal places. What would be wrong with seeing $123.450 in one record and $123.456 in another if you set to 3 places rather than auto? Or set table field to currency (e.g. 3 decimal places) and form control to General Number and have the decimal places property match what you have in the table. You'll get 123.456 in one record and 123.45 in another and drop the dollar sign on the form, assuming you don't want it.

MajP · Feb 1, 2019

To demonstrate I placed two copies of the subform so you can see the real stored values. The one in the bottom displays the number of decimals places stated in the field decimal places. This uses the technique I described previously.

]

Continuous Forms - Different Properties For Each Row (1 Viewer)

HairyArse

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

HairyArse

Registered User.

MajP

You've got your good things, and you've got mine.

Micron

AWF VIP

MajP

You've got your good things, and you've got mine.

Attachments

Similar threads

Users who are viewing this thread