Form field/line visibility dependant on value selected in other field + table value

[peter2012]

I have a time-recording database, and the relevant tables are Client, Matter and Timecards.

Each timecard records one piece of work for a client on a matter (e.g. Letter out) and the time spent on it.

Any matter can have one of two possible billing methods - either units of time (minimum six-minute units) or minutes. There is a field ([BillingType]) in the matters table which records whether the matter is billed by units or minutes. This is set when the matter is opened.

In my Timecards table I have a field for Units and a field for Minutes. In a different scenario I could just record minutes on all matters and use a formula to convert to units where appropriate, but for historic records purposes I need to have a field for units and a field for minutes.

I have a timecard data entry form.

I have it set up so that when you select a client in the client field dropdown, only the matters relating to that client are shown in the matter field dropdown.

I have down this with an AfterUpdate procedure:

Private Sub ClientName_AfterUpdate()

MatterName.Requery

End Sub

I have also set it up so that I can enter data into the units field or the minutes field (or, at the moment, both, which I don't want to do). in other words both bound fields are shown and capable of having data entered into them.

What I want to do is something similar to the AfterUpdate procedure above, but on the Matter dropdown, so that when I select the matter Access looks in the Matters table to see which billing type is already entered in the [BillingType] field (when the matter was first set up) and on the form makes either the [Units] field or the [Minutes] field or the visible (and the other one invisible) dependant on the [BillingType] field value for the selected matter.

I can see how to do visibility dependant on a value selected on the form itself, along the lines of (probably something like):

If Me.FIELD = Minutes Then Minutes.Visible = True Units.Visible = False
Else
Minutes.Visible = False Units.Visible = True

But I am not sure how to replace Me.FIELD with the value of the field that is already in the Matters table for [BillingType] for the matter that has just been selected in the Matters dropdown on the form.

In other words when the matter is selected in the form dropdown Access looks in the record for that matter to see if the [BillingType] is units or minutes, and if it is units it makes the units field (or could be line) visible and the minutes field (or line) invisible, and vice versa if the value in the [BillingType] field for the selected matter is minutes.

Any assistance would be appreciated.

Peter

 

[RainLover]

Rather than using two fields, one for units and the other for minutes just use one field.

Make that a combo so that you can lookup either Minutes or Units.

Because this is setup in the beginning the result will follow through to your other forms etc.

 

[peter2012]

Rainlover, sorry I do not understand that.

I already have thousands of records in the Matters table with data in the units field, and on those matters and some future matters I still need to be able to add new records in units and report those matters in units.

Are you suggesting a combined field in the Matters database or a combined field in the form (or both). Either way I would be grateful for an explanation as to how to achieve that.

One key feature is that the form has to be full-proof, so that once the matter is selected it is impossible to enter the wrong kind of data.

Peter