How to hide/show From Detail via VBA

[LakotaMan]

Hi all,

I am using a form to allow access to a table to change passwords. In the header, I have a drop-down for Dept. and a textbox for the user to type the password. On hitting cmdOK, I check their choices via SQL and allow or disallow access to the detail section.

I have done this by setting all the detail controls' visible property to false in design, and if they match the dept and password, the controls visible prop is set to true. I used a for-loop to cycle through the controls of the detail section to accomplish this.

Everything works beautifully, except that I'd now like to hide the detail section of the form itself and only show the header on opening. I've tried the same scenario and added a single line to my code to set the form's detail section.visible to true, but it won't come back!

Here's the code so far:

Private Sub cmdOKEnter_Click()

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strPwd As String
Dim strDept As String


Set cnn = CurrentProject.Connection

If Len(Me.cboChooseDept & "") > 0 And Len(Me.txtEnterPwd & "") > 0 Then
strDept = cboChooseDept
strPwd = txtEnterPwd
Else
MsgBox "You must choose a Department and Enter a Password!", vbExclamation + vbOKOnly, "Incomplete Info"
End If


strSQL = "Select * FROM tblProcedMaintSecurity WHERE txtMngrPwd = '" & strPwd & "'" & " AND txtDept = '" & Me.cboChooseDept & "'"
rs.Open strSQL, cnn, adOpenStatic

If rs.RecordCount > 0 Then
DoCmd.ApplyFilter , "txtMngrPwd = '" & strPwd & "'and txtDept='" & Me.cboChooseDept & "'"
Me.Detail.Visible = True
For Each ctl In [Forms]![frmProcedMaintSecurity].Detail.Controls
If ctl.Tag = "Off" Then
Else
ctl.Visible = True
End If
Next ctl

Else
MsgBox "You are not authorized to use this feature!", vbOKOnly + vbCritical, "Password Incorrect!"
Exit Sub
End If
End Sub

I hope someone can help,

Thanks in advance,
Tru

 

[mgolla]

at the end at a Me.Refresh statement


HTH
Mike

 

[LakotaMan]

Mgolla,

I added the statement, but the detail section is still not showing. Any thoughts?

Thanks,
Tru

 

[RoyVidar]

Hi!

Try setting the detail section to visible in design view, then in the forms on load event, set it to false (works in my setup).

Roy-Vidar

 

[LakotaMan]

Roy,

I tried your suggestion on my current form, no success, then I tried it with a new form, bound to a table with just a couple of the controls in the details section. The controls hide fine on loading, but the section itself is still visible (I've got it a different color than the header). I coded a button in the header section to make the detail section visible on click, and that brings the controls back fine.

Am I missing something basic here? I want the entire section to disappear and seem to "drop down" when the button is clicked. About the only way I can seem to get close is by restoring on load and sizing to hide the empty details section, then maximizing on the click event if the password is correct. If I add the DoCmd.Echo False and back on to True at the end, it doesn't seem too bad, but not quite what I wanted.

You said you could get it to work on your setup, so that's why I tried a new form from scratch, nothing much on it and no properties changed. . .

Pulling my hair out again,
Tru

 

[RoyVidar]

In my setup the form has the same size all the time (just showing or not what's within the details section). Should perhaps have mentioned that, sorry, but probably misunderstood the question.

I'm not sure if you're able to change the height of the form or any of the sections at runtime (other than maximize/restore), but would've been nice if someone could tell us otherwise.

Roy-Vidar

 

[LakotaMan]

Roy,

Glad to hear that I wasn't totally messing up on following your instructions.

It's looking more and more like doing this by "hiding" that bottom part (the details section) via the MoveSize method on the form would be about the only way, and that's a little more trouble than I have time to go into.

Thanks again,
Tru