Subform fields lock/unlock enable/disable from userinput

[dek]

Hi,
Re: Controlling fields in a subform from a combo list value

[vba]
Field names:
cboActivity - this combo box lists the Activity Numbers (i.e 1.1, 1.2, 1.3, 2.1, 2.2, 3.1)
txtStartDate - this date field is an input field
txtEndDate - this date field is an input field
txtQuantity - this text field is an input field
txtStartDepth - this text field is an input field
txtEndDepth - this text field is an input field
[/vba]
Goal / Solution Required:
I would like the subform to lock the last three text fields (txtQuantity, txtStartDepth, txtEndDepth) on load.
<br>I would like to enable these 3 fields for input based on the "cboActivity" value.

The following condition to enable the text fields are:
[vba]
Condition 1:If "cboActivity" starts with a "1", then enable the txtQuantity field,
Condition 2:If "cboActivity" starts with a "2", then enable the txtStartDepth and txtEndDepth field,
Condition 3:If "cboActivity" starts with a "3", then enable the txtQuantity, txtStartDepth and txtEndDepth fields,
[/vba]
Help appreciated.

[Imdabaum]

For this kind of functionality, you'll want to use the OnCurrent event.

In the code you'll want to check

[VBA]
SELECT Case Me.cboActivity.Value
Case 1:
Me.yoursubformname.txtStartDepth.enabled =True
Me.yoursubformname.txtEndDepth.Enabled=True
Case 2:
' enable other controls
Case 3:
' enable other controls
Case Else
' disable all controls. Me.subformname.Controlname.enabled = false
[/VBA]

[hansup]

For this kind of functionality, you'll want to use the OnCurrent event.

In the code you'll want to check

[VBA]
SELECT Case Me.cboActivity.Value
Case 1:
Me.yoursubformname.txtStartDepth.enabled =True
Me.yoursubformname.txtEndDepth.Enabled=True
Case 2:
' enable other controls
Case 3:
' enable other controls
Case Else
' disable all controls. Me.subformname.Controlname.enabled = false
[/VBA]

A Select Case block seems the right approach. But those Case statements won't work. The OP list sample activity code values as 1.1, 1.2, 1.3 ...
None of those will match Case 1

If cboActivity.Value is numeric, use:

[VBA]Select Case Int(Me.cboActivity.Value)[/VBA]

If cboActivity.Value is a string, use:

[VBA]Select Case Left(Me.cboActivity.Value, 1)
Case "1"
Case "2"[/VBA]

(No colons after Case statements.)

In addition to doing this for On Current, I think he may need to do it for cboActivity After Update.

[Imdabaum]

Glad you have my back Hansup. I have no idea why I write my Case statements in Java syntax still. I haven't written java in 3 years.

Thanks.

[dek]

Hi Guys,

Firstly, thanks for your replies.

I have performed the steps above and receive the error "Compile error: method or datamember not found".

Please note: the combo is named cboAct, not cboActivity as described in original post.

The "cboAct" (combo box field) is in the subform, not the main form. I am unsure if your solution was on the basis that combo was in the main form.

[vba]Private Sub Form_Current()
Select Case Left(Me.cboAct.Value, 1)
Case "1"
Me.tbl_TimeData.txtStartDepth.Enabled = True *** error is trigerred here with "tbl_TimeData" being highlighted when the error occurs
Me.tbl_TimeData.txtEndDepth.Enabled = True
Case "2"
Me.tbl_TimeData.txtQuantity.Enabled = True
Case "3"
Me.tbl_TimeData.txtStartDepth.Enabled = True
Me.tbl_TimeData.txtEndDepth.Enabled = True
Me.tbl_TimeData.txtQuantity.Enabled = True
Case Else
Me.tbl_TimeData.txtStartDepth.Enabled = False
Me.tbl_TimeData.txtEndDepth.Enabled = False
Me.tbl_TimeData.txtQuantity.Enabled = False
End Sub
[/vba]

What steps did I undertake?

1. The code above has been loaded in the subforms "on current" event (as you can tell by the procedure which is named "Private Sub Form_Current()"

2. The cboAct (combo box) field sources from a text field, not an integer. Thus, I have used the text solution using "left" statement.

Confirmations:
1. The subform name is "tbl_TimeData". (obtained when putting main form in design mode and selecting subform).

Questions:
1. Should the procedure be in the main form or the subform? (I assume in the subform).

I attach the database for convenience. It is in 2007 format (accdb). comments?

Again, thanks for the assistance.

[hansup]

Try this version of the form module.

I moved the code which enables the controls to its own procedure so that it can be called from both Form Current and cboAct After Update.

I used Fix() instead of Left() in the Select Case in order to distinguish between 1.1 and 10.1. (With Left(Me.cboAct.Value, 1), they both would return "1")

I think you may still want to add some .Enabled = False statements to those Case conditions, but I left that for you. I think this is at least closer to what you want than what you had before. :-)

[VBA]Private Sub cboAct_AfterUpdate()
Me!txtCD = Me!cboAct.Column(1)
Me.Refresh
SetEnabledFields
End Sub

Private Sub Form_Current()
SetEnabledFields
End Sub

Private Sub SetEnabledFields()
Select Case Fix(Me.cboAct.Value)
Case 1
Me.txtStartDepth.Enabled = True
Me.EndDepth.Enabled = True
Case 2
Me.txtQuantity.Enabled = True
Case Else
Me.txtStartDepth.Enabled = False
Me.EndDepth.Enabled = False
Me.txtQuantity.Enabled = False
End Select
End Sub[/VBA]

[dek]

Hi,

Thanks again for the swift response.

Unfortunately there is an issue when creating a new record. Error "You cannot disable a control while it has the focus".

Suggestions?

Furthermore, when I review an existing record in the subform, all of the fields are active. I would assume that based on the combol list value that the appropriate fields are locked/disabled?

Thanks

Try this version of the form module.

I moved the code which enables the controls to its own procedure so that it can be called from both Form Current and cboAct After Update.

I used Fix() instead of Left() in the Select Case in order to distinguish between 1.1 and 10.1. (With Left(Me.cboAct.Value, 1), they both would return "1")

I think you may still want to add some .Enabled = False statements to those Case conditions, but I left that for you. I think this is at least closer to what you want than what you had before. :-)

[vba]Private Sub cboAct_AfterUpdate()
Me!txtCD = Me!cboAct.Column(1)
Me.Refresh
SetEnabledFields
End Sub

Private Sub Form_Current()
SetEnabledFields
End Sub

Private Sub SetEnabledFields()
Select Case Fix(Me.cboAct.Value)
Case 1
Me.txtStartDepth.Enabled = True
Me.EndDepth.Enabled = True
Case 2
Me.txtQuantity.Enabled = True
Case Else
Me.txtStartDepth.Enabled = False
Me.EndDepth.Enabled = False
Me.txtQuantity.Enabled = False
End Select
End Sub[/vba]

[hansup]

Unfortunately there is an issue when creating a new record. Error "You cannot disable a control while it has the focus".

Suggestions?

Use SetFocus to move the focus to a field which you won't disable.

Furthermore, when I review an existing record in the subform, all of the fields are active. I would assume that based on the combol list value that the appropriate fields are locked/disabled?

The procedure I wrote only enables fields. I left it for you to sort out which fields should be disabled in Case 1 and Case 2.

But perhaps I'm misunderstanding. I didn't review the form operation in detail.

[dek]

Hi Hansup,

You replied stating to use 'SetFocus'. Would you please elaborate a little more with some procedural steps to execute.

I have reviewed your code and have the following understanding. Please correct me if I am wrong.

[VBA]Private Sub Form_Current()
SetEnabledFields 'calls the procedure below'
End Sub

Private Sub SetEnabledFields()
Select Case Fix(Me.cboAct.Value)
'I assume the subform disables the three fields in CASE ELSE
Case 1
'The two fields below are enabled of the three that were disabled
Me.txtStartDepth.Enabled = True
Me.EndDepth.Enabled = True
Case 2
'The field below is enabled of the three that were disabled
Me.txtQuantity.Enabled = True
Case Else
Me.txtStartDepth.Enabled = False
Me.EndDepth.Enabled = False
Me.txtQuantity.Enabled = False
End Select
End Sub [/VBA]

It is my understanding that the 'Case Else' statement handles which fields are disabled on subform load. In the code above, it is those three fields. Is this correct?

It is my understanding that those three fields (in Case Else) toggle between enabled and disabled when the cboAct value is 1 or 2.

So, in summary, I assume that I only have to declare which fields I want as disabled on subform load in the Case Else section and those that I want enabled in Case 1 or 2 or 3,4,5,6, etc.

Thanks for your help so far.

[hansup]

You replied stating to use 'SetFocus'. Would you please elaborate a little more with some procedural steps to execute.

A form has 2 text box controls: txtFoo and txtBar

I want to disable txtBar. However, since txtBar can't be disabled if it has focus, must first switch focus to txtFoo.

[vba]Me!txtFoo.SetFocus
Me!txtBar.Enabled = False[/vba]

It is my understanding that the 'Case Else' statement handles which fields are disabled on subform load. In the code above, it is those three fields. Is this correct?

No. Only one Case block is active ... the first Case block which evaluates as True. So, at subform load if the activity code in the first record is 2.1, the Case 2 block is run (the other Case blocks are ignored). And in Case 2, txtQuantity is enabled. However, txtStartDepth and EndDepth with also be enabled because no code has run to disable them.

It is my understanding that those three fields (in Case Else) toggle between enabled and disabled when the cboAct value is 1 or 2.

No. Similar to above, Case 1 and Case 2 don't disable any controls; they only enable certain controls.

So, in summary, I assume that I only have to declare which fields I want as disabled on subform load in the Case Else section and those that I want enabled in Case 1 or 2 or 3,4,5,6, etc.

No. Case Else only applies when the activity code doesn't match Case 1 or Case 2.

I think you will find Access Basics Crystal useful: http://www.accessmvp.com/strive4peace/

Her full tutorial is excellent. And Chapter 5 is devoted to Form and Report Design.

[dek]

Hi Hansup,

It seems that the process is to reinitialise thecontrols to not enabled before you check for the cboActivity value and then set the appropriate controls enabled (as outlined in the below code).

Can you confirm that Fix() works on a text value.


[vba]
Private Sub Form_Current()

Me.txtStartDepth.Enabled = False
Me.txtEndDepth.Enabled = False
Me.txtQuantity.Enabled = False

Select Case Fix(Me.cboAct.Value)
Case "1"
Me.txtStartDepth.Enabled = True
Me.txtEndDepth.Enabled = True
Case "2"
Me.txtQuantity.Enabled = True
Case Else
Me.txtStartDepth.Enabled = False
Me.txtEndDepth.Enabled = False
Me.txtQuantity.Enabled = False
End Select
End Sub [/vba]

Thanks for the link. Will read with interest.

Are there any adequate resources that can assist with deploying the database into a multi user environment?

[hansup]

It seems that the process is to reinitialise thecontrols to not enabled before you check for the cboActivity value and then set the appropriate controls enabled (as outlined in the below code).

I think that logic can work to disable the appropriate fields for the Current event. But, a few days ago, I tried to get you to consider what should happen if the user changes an activity code, say from 1.1 to 2.1, for an existing record. In that situation, the set of enabled fields would include those enabled in both Case 1 and Case 2. Maybe it's not a concern?

Can you confirm that Fix() works on a text value.

Fix() is not the cause of your current problem. Where I wrote Case 1, you changed it to Case "1". And same for Case 2. Therefore, your Select Case does not work as intended.

However, I decided I made a mistake by using Fix(Me.cboAct.Value) Although it works, I now think Int(Val(Me.cboAct.Value)) is a better choice for you because the intention is clearer.

Are there any adequate resources that can assist with deploying the database into a multi user environment?

Visit "Splitting your Microsoft Access MDB into a front end and back end": http://www.granite.ab.ca/access/splitapp/index.htm

Splitting the application is a "best practice" prerequisite for a multi-user Access application. And for deployment specifically, visit the link for Auto FE Updater from that page.