How do I place a lock a subform until a field in the header is compled

[cwhite23]

Good morning,

I have a form that is used to enter a log of daily activities for each of our customer service representatives. In the header (DAL_Header), there are two fields that are required: the CSR's employee number (CSR-Num, and the date (DAL_Date). In the detail section is a sub-form (DAL_Footer) that is used to enter the actual list of activities.

What I need is for the subform to remain locked until the CSR enters their number and the date in the header. Once both fields are completed, the subform becomes unlocked, and the user can enter their data.

Any ideas would be appreciated.

 

[dhookom]

You can set the subform Enabled property to No. Then use code that can be called in the On Current of the form and After Update of the controls to set Enabled to Yes:

  Public Function Set_Footer()
    If IsNull(Me.[CSR-Num]) Or IsNull(Me.DAL_Date) Then
      Me.DAL_Footer.Enabled = False
     Else
      Me.DAL_Footer.Enabled = True
    End If
  End Function

Call the function from the On Current of the form and the After Update of the controls.

Duane
Hook'D on Access
MS Access MVP

 

[cwhite23]

Looks like this will work, but I do have one issue. I need to add this to several forms as each user has their own form. Is there a way to make this into a module and call that module on each form; or am I going to have to modify each form individually?

I know this may seem like a convoluted way of doing this, but the company that I work for has an 85% rate of employment of visually impaired workers. I therefore have to tailor each user's form to their specific visual needs.

 

[dhookom]

Are all of the controls named the same? I suppose you could add something to the Tag property of the required controls and use code to loop through them to check for values. If they all have values then enable the subform.

Duane
Hook'D on Access
MS Access MVP

 

[cwhite23]

All of the controls are named the same on the different forms.

 

[dhookom]

You could try a function in a standard module like:

  Public Function Set_Footer(frm As Form)
    If IsNull(frm.[CSR-Num]) Or IsNull(frm.DAL_Date) Then
      frm.DAL_Footer.Enabled = False
     Else
      frm.DAL_Footer.Enabled = True
    End If
  End Function

You would call this from your forms in code like:

Call Set_Footer(me)

Duane
Hook'D on Access
MS Access MVP

 

[AJLoehr]

I know you didn't ask for this answer, but you could make the program auto-assign the CSR's name based on a table. There is code on this site that pulls the NETID of whoever is logged on. For instance, I'm logged on my computer right now as "jeffrey.loehr". If your company uses a similar method of network authentication, you could make a table with two fields. CSRNAME and Netid. Then use the "fosUserName" code (found on this website), to look up and apply the CSRNAME based on the Netid. You put the fosUserName code in a module and you can pull that from any form easily. Then your CSR doesn't have to input their name each time (time-savings). For your date, just make the date fields mandatory and they won't be able to save the info until they put in the date. Or you can go the long way and do this to check data before your save data code.

If isnull(date.value) then
   field1.enabled = false
   field2.enabled = false
   etc.
end if

 

[MacroScope]

I've got a much simpler way. On either the OnCurrent or OnOpen events of the main form you have a simple macro with one command. GoToControl, and you take the user to the control that you want filled in. On the OnExit event of that control, you use another macro with a condition. The condition would be IsNull([CSR-Num]), and if the control is null the macro runs. It would have two steps. First, a MsgBox informing the user that the control must be filled in, and a GoToControl action to take the user right back to the control that must be populated. Once that control is filled in, the AfterUpdate event would be another macro with another GoToControl to take you to the second control.

The same scenario would apply for the second control. It's OnExit would have a macro with a condition attached, IsNull([DAL_Date]). If that control is null, the macro would fire, with the same two actions. A MsgBox to inform the user what must be done, and a GoToControl action to bring the user right back to DAL_Date.

You don't need to lock the subform. You just have to force the user to where you want them to go, and not allow them to do anything else until the desired actions are completed.