Another Visible/Hidden Form Field Question

P

psyc0tic1

Access Moron
Local time
Today, 11:22
Joined
Jul 10, 2017
Messages
360
Hello all,

(Access 2013 64bit)

I have a form designed for updating records you can see in the attachment "Capture3". When a PO# is chosen from the combo box at the top the PO# and Part# are automatically populated below.

As you can see in the picture there is a large box below the top fields called "USB Port Test Data" and all of the lined around it and inside it are just lines drawn for ease of identifying blocks of data to be entered.

Not all part numbers require USB port test data to be entered and since I require all fields be filled out to be able to submit from the command button I have default values in all USB port test data fields.

So here is the question...Is it possible, based on the choice from the combo box, make everything including the lines, labels etc. go away (not visible) if certain choices are made from the combo box? I know the fields can be hidden but not sure about the lines.

Also... this is the most important question... can the form shrink down to the way it looks in the attachment "Capture4" and grow back based on the choice in the combo box?

Example: I choose a PO# that has a part number of a part that does not have USB port(s)... once the part# field populates with the part number all of the USP port data fields, lines, labels etc. disappear and the form shrinks down moving the "Defect(s) Found field, Attachments field, and command button up to below the top 6 fields.

And then the opposite if a choice is then made for a part that does have USB ports the USB fields etc return and the form grows moving the before mentioned bottom fields/command button back down.

Does all that make sense? Is this possible?

This is not a necessary thing that is make or break but it is a visual thing that my OCD keeps pinging me on.
 

Attachments

  • Capture3.jpg
    Capture3.jpg
    91.2 KB · Views: 222
  • Capture4.JPG
    Capture4.JPG
    33 KB · Views: 224
Yes you can also show/hide lines
There are at least 2 ways of doing this:

1. Move everything you want to hide onto a subform which you can then show/hide as necessary

2. Use the tag property to show/hide a group of controls at once.
This needs just one line of code e.g. ShowControls False, "X"
Have a look at this example which explains how its done together with the ShowControls function you need to make it work
https://www.access-programmers.co.uk/forums/showthread.php?t=293439
 
ridders said:
Yes you can also show/hide lines
There are at least 2 ways of doing this:

1. Move everything you want to hide onto a subform which you can then show/hide as necessary

2. Use the tag property to show/hide a group of controls at once.
This needs just one line of code e.g. ShowControls False, "X"
Have a look at this example which explains how its done together with the ShowControls function you need to make it work
https://www.access-programmers.co.uk/forums/showthread.php?t=293439
Click to expand...

I tried creating another form out of the data, lines, etc. I wanted to make show or hide but since the stuff I am trying to hide is also for data entry... when I add it to the form as a subform I don't know how to make it work.

I have code in the main form requiring users to complete all fields but when I click on a field in the subform fields I just get the prompt telling me to fill out all fields.

I admit I know nothing about subforms because I have not used them before.

The main form has a query... does the subform use the same query? I did try that but it keeps poping up parameter entry boxes.

Totally confused lol.
 
Here is the query for the main form:
Code: 
SELECT tbl_auditdata.Status, tbl_auditdata.TotalFunctTested, tbl_auditdata.LabTestDate, tbl_auditdata.TotalFunctBad, tbl_auditdata.TotalFunctGood, tbl_auditdata.AuditID, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.VisInspectDate, tbl_auditdata.PartProdDate, tbl_auditdata.QCLine, tbl_auditdata.BlackGreenDot, tbl_auditdata.TotalVisInspected, tbl_auditdata.TotalVisBad, tbl_auditdata.TotalVisGood, tbl_auditdata.VisDefectFound, tbl_auditdata.LabDefectFound, tbl_auditdata.LabAtt, tbl_auditdata.VisualInspectorUserID, tbl_auditdata.LabInspectorUserID, tbl_auditdata.LabUpdate, tbl_auditdata.InitReading1, tbl_auditdata.[30SecReading1], tbl_auditdata.[InitReading1-2], tbl_auditdata.[30SecReading1-2], tbl_auditdata.InitReading2, tbl_auditdata.[30SecReading2], tbl_auditdata.[InitReading2-2], tbl_auditdata.[30SecReading2-2], tbl_auditdata.InitReading3, tbl_auditdata.[30SecReading3], tbl_auditdata.[InitReading3-2], tbl_auditdata.[30SecReading3-2], tbl_auditdata.InitReading4, tbl_auditdata.[30SecReading4], tbl_auditdata.[InitReading4-2], tbl_auditdata.[30SecReading4-2], tbl_auditdata.InitReading5, tbl_auditdata.[30SecReading5], tbl_auditdata.[InitReading5-2], tbl_auditdata.[30SecReading5-2], tbl_auditdata.[5MinRearing1], tbl_auditdata.[5MinReading1-2], tbl_auditdata.Part2InitReading1, tbl_auditdata.Part230SecReading1, tbl_auditdata.[Part2InitReading1-2], tbl_auditdata.[Part230SecReading1-2], tbl_auditdata.Part2InitReading2, tbl_auditdata.Part230SecReading2, tbl_auditdata.[Part2InitReading2-2], tbl_auditdata.[Part230SecReading2-2], tbl_auditdata.Part2InitReading3, tbl_auditdata.Part230SecReading3, tbl_auditdata.[Part2InitReading3-2], tbl_auditdata.[Part230SecReading3-2], tbl_auditdata.Part2InitReading4, tbl_auditdata.Part230SecReading4, tbl_auditdata.[Part2InitReading4-2], tbl_auditdata.[Part230SecReading4-2], tbl_auditdata.Part2InitReading5, tbl_auditdata.Part230SecReading5, tbl_auditdata.[Part2InitReading5-2], tbl_auditdata.[Part230SecReading5-2], tbl_auditdata.Part25MinReading1, tbl_auditdata.[Part25MinReading1-2], tbl_auditdata.Part3InitReading1, tbl_auditdata.Part330SecReading1, tbl_auditdata.[Part3InitReading1-2], tbl_auditdata.[Part330SecReading1-2], tbl_auditdata.Part3InitReading2, tbl_auditdata.Part330SecReading2, tbl_auditdata.[Part3InitReading2-2], tbl_auditdata.[Part330SecReading2-2], tbl_auditdata.Part3InitReading3, tbl_auditdata.Part330SecReading3, tbl_auditdata.[Part3InitReading3-2], tbl_auditdata.[Part330SecReading3-2], tbl_auditdata.Part3InitReading4, tbl_auditdata.Part330SecReading4, tbl_auditdata.[Part3InitReading4-2], tbl_auditdata.[Part330SecReading4-2], tbl_auditdata.Part3InitReading5, tbl_auditdata.Part330SecReading5, tbl_auditdata.[Part3InitReading5-2], tbl_auditdata.[Part330SecReading5-2], tbl_auditdata.Part35MinReading1, tbl_auditdata.[Part35MinReading1-2], tbl_auditdata.Part4InitReading1, tbl_auditdata.Part430SecReading1, tbl_auditdata.[Part4InitReading1-2], tbl_auditdata.[Part430SecReading1-2], tbl_auditdata.Part4InitReading2, tbl_auditdata.Part430SecReading2, tbl_auditdata.[Part4InitReading2-2], tbl_auditdata.[Part430SecReading2-2], tbl_auditdata.Part4InitReading3, tbl_auditdata.Part430SecReading3, tbl_auditdata.[Part4InitReading3-2], tbl_auditdata.[Part430SecReading3-2], tbl_auditdata.Part4InitReading4, tbl_auditdata.Part430SecReading4, tbl_auditdata.[Part4InitReading4-2], tbl_auditdata.[Part430SecReading4-2], tbl_auditdata.Part4InitReading5, tbl_auditdata.Part430SecReading5, tbl_auditdata.[Part4InitReading5-2], tbl_auditdata.[Part430SecReading5-2], tbl_auditdata.Part45MinReading1, tbl_auditdata.[Part45MinReading1-2], tbl_auditdata.Part5InitReading1, tbl_auditdata.Part530SecReading1, tbl_auditdata.[Part5InitReading1-2], tbl_auditdata.[Part530SecReading1-2], tbl_auditdata.Part5InitReading2, tbl_auditdata.Part530SecReading2, tbl_auditdata.[Part5InitReading2-2], tbl_auditdata.[Part530SecReading2-2], tbl_auditdata.Part5InitReading3, tbl_auditdata.Part530SecReading3, tbl_auditdata.[Part5InitReading3-2], tbl_auditdata.[Part530SecReading3-2], tbl_auditdata.Part5InitReading4, tbl_auditdata.Part530SecReading4, tbl_auditdata.[Part5InitReading4-2], tbl_auditdata.[Part530SecReading4-2], tbl_auditdata.Part5InitReading5, tbl_auditdata.Part530SecReading5, tbl_auditdata.[Part5InitReading5-2], tbl_auditdata.[Part530SecReading5-2], tbl_auditdata.Part55MinReading1, tbl_auditdata.[Part55MinReading1-2]
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Lab"));

Here is the code for the form:
Code: 
Option Compare Database

Private blnGood As Boolean

Private Sub Form_Open(Cancel As Integer)
    If Credentials.AccessLvlID <> 1 And Credentials.AccessLvlID <> 3 And Credentials.AccessLvlID <> 4 Then
        DoCmd.OpenForm "frm_loginform"
        Cancel = 1
    End If
    
End Sub

Private Sub cboGoToRecord_AfterUpdate()
     On Error Resume Next
     Dim rst As Object
     Set rst = Me.RecordsetClone
     rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
     Me.Bookmark = rst.Bookmark
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    
    If Not blnGood Then
        Cancel = True
        strMsg = "Please click the Update button to save your changes, " & vbNewLine & "or Escape to reset them."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
        Else
        Me![LabUpdate] = Date
    End If
    
End Sub

Private Sub UpdateRecord_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Complete"
        Me.Recordset.Fields("LabInspectorUserID").Value = Credentials.UserId
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
    blnGood = False
End Sub

Private Function validate() As Boolean
    validate = True
    If (IsNull(Me.LabTestDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctBad.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctTested.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctGood.Value)) Then
        validate = False
    End If
End Function
 
I'm sorry but I don't have time to look through your form code today.

Did you look at the other solution I suggested using the tag property and the ShowControls function. You might find it much easier to grasp instead of learning how to use subforms
 
ridders said:
I'm sorry but I don't have time to look through your form code today.

Did you look at the other solution I suggested using the tag property and the ShowControls function. You might find it much easier to grasp instead of learning how to use subforms
Click to expand...

Looking into it now.
 
Have you considered normalizing the data. What you have is a "spreadsheet". You are forcing your data input to mimic what you want for an output report. That is not how relational databases work.
 
ridders said:
I'm sorry but I don't have time to look through your form code today.

Did you look at the other solution I suggested using the tag property and the ShowControls function. You might find it much easier to grasp instead of learning how to use subforms
Click to expand...

I set the tag for all of the labels, lines and fields I wanted to hide to "A".

I tried putting in this code in my frm_labtestinput related to the PartNumber field
Code: 
Private Sub Part_Number_AfterUpdate()

   On Error GoTo Err_Handler
   
   If PartNumber = 77916 Or 30035 Or 77842 Or 78630 Or 30221 Then ShowControls True, "A"
   Else
   ShowControls False, "A"
   
   End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_Handler

End Sub

and I put in the modules from your example but when I change the records via the top combo box... it doesn't matter what part number shows up in the PartNumber field... nothing else changes. No errors.

I'm sure i did this wrong but gave it a shot.
 
The issue is nothing to do with the ShowControls function
The problem is your If statement is incorrect
a) Each PartNumber needs to be specified
b) The part after Then needs to be on a new line

Corrected version:
Code: 
    If PartNumber = 77916 Or PartNumber = 30035 Or PartNumber = 77842 Or PartNumber = 78630 Or PartNumber = 30221 Then
        ShowControls True, "A"
    Else
        ShowControls False, "A"
    End If

However, I suggest using select case instead to tidy up the code:

Code: 
Select Case PartNumber

Case 77916 , 30035 , 77842, 78630 , 30221
    ShowControls True, "A"

Case Else
    ShowControls False, "A"

End Select

NOTE: You only need the modControlState for this code to work
 
ridders said:
The issue is nothing to do with the ShowControls function
The problem is your If statement is incorrect
a) Each PartNumber needs to be specified
b) The part after Then needs to be on a new line

Corrected version:
Code: 
    If PartNumber = 77916 Or PartNumber = 30035 Or PartNumber = 77842 Or PartNumber = 78630 Or PartNumber = 30221 Then
        ShowControls True, "A"
    Else
        ShowControls False, "A"
    End If

However, I suggest using select case instead to tidy up the code:

Code: 
Select Case PartNumber

Case 77916 , 30035 , 77842, 78630 , 30221
    ShowControls True, "A"

Case Else
    ShowControls False, "A"

End Select

NOTE: You only need the modControlState for this code to work
Click to expand...

I tried both of those codes and the result was the same... nothing changes... no errors.
 
I'm busy this evening but if you upload the relevant parts of your database, I'll look at it sometime tomorrow
 
ridders said:
I'm busy this evening but if you upload the relevant parts of your database, I'll look at it sometime tomorrow
Click to expand...

I have attached the database. I converted the tables to local since this was a split database. I also removed a bunch of the data from the tbl_users, and tbl_auditdata to get the file size down to under 3 meg.
 
Last edited:
I've made a few changes to frmlabtestinput and partly solved it for you
Old version of form saved as frmlabtestinput_old

1. Your Part_Number combo was locked so it couldn't be updated - now unlocked
2. The bound column is for the ID field so the after update code didn't work even once unlocked
I've added a variable lngPartNumber to get the vaLue of the 2nd column & used that in the Select Case code

On its own the frmlabtestinput now works perfectly

However there some sort of conflict when used as a subform of your navigation form. You are losing other parts of your main form and getting error 2165. I've already spent an hour on this and can't afford to devote more time to it as I don't know how your overall form design works

Navigation forms are OK if you stick to the basic design but a total PITA when you alter them in any significant way.
For that reason I NEVER use them, preferring to create my forms/tabbed forms/subforms from scratch so I have total control

Hope you can fix the rest of it yourself

UPDATE:
Attachment has now been deleted at the request of psychotic
 
Last edited:
ridders said:
I've made a few changes to frmlabtestinput and partly solved it for you
Old version of form saved as frmlabtestinput_old

1. Your Part_Number combo was locked so it couldn't be updated - now unlocked
2. The bound column is for the ID field so the after update code didn't work even once unlocked
I've added a variable lngPartNumber to get the vaLue of the 2nd column & used that in the Select Case code

On its own the frmlabtestinput now works perfectly

However there some sort of conflict when used as a subform of your navigation form. You are losing other parts of your main form and getting error 2165. I've already spent an hour on this and can't afford to devote more time to it as I don't know how your overall form design works

Navigation forms are OK if you stick to the basic design but a total PITA when you alter them in any significant way.
For that reason I NEVER use them, preferring to create my forms/tabbed forms/subforms from scratch so I have total control

Hope you can fix the rest of it yourself
Click to expand...

Thank you for trying. The whole idea of the part number field being locked was so it updates only by the choices in the combobox in the header. If the part number field is editable it can create messed up records matching up part numbers with the PO numbers.

I did see it kinda works if you change part numbers but nothing happens if you change PO numbers from the header.

I will play around with this some more and see if I can get it to work.

Thank you very much for the time you spent on it for me.

Will you do me a favor and delete the attachment?
 
psyc0tic1 said:
Thank you for trying. The whole idea of the part number field being locked was so it updates only by the choices in the combobox in the header. If the part number field is editable it can create messed up records matching up part numbers with the PO numbers.

I did see it kinda works if you change part numbers but nothing happens if you change PO numbers from the header.

I will play around with this some more and see if I can get it to work.

Thank you very much for the time you spent on it for me.

Will you do me a favor and delete the attachment?
Click to expand...

Oh I see ...
After update event is irrelevant in that case.
If the value is externally changed, the On Change event would normally be used. Just tried it - no joy.

If you scrap your navigation form & build it from scratch you might have a chance of success???

You should only ever post databases where none of the data is confidential.
Normally you should alter items before uploading.
I see you've now deleted your own attachment but how many people had downloaded it first?
I will delete it on this occasion but normally items should stay available in case they are useful to others
 
ridders said:
Oh I see ...
After update event is irrelevant in that case.
If the value is externally changed, the On Change event would normally be used. Just tried it - no joy.

If you scrap your navigation form & build it from scratch you might have a chance of success???

You should only ever post databases where none of the data is confidential.
Normally you should alter items before uploading.
I see you've now deleted your own attachment but how many people had downloaded it first?
I will delete it on this occasion but normally items should stay available in case they are useful to others
Click to expand...

I understand... there was no confidential data included in the data base and we will never know how many downloaded it but the reason was just to keep all the code from just being out there anyway. If there is ever a question about code that my code will solve I will most definitely post the relevant code to help. I am not stingy that way.

Back to the modification... I was able to get the fields, lines etc to hide properly. I used the combobox (cboGoToRecord) and setting the partnumber ID number in the code as such:
Code: 
Private Sub cboGoToRecord_AfterUpdate()
     On Error Resume Next
     Dim rst As Object
     Set rst = Me.RecordsetClone
     rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
     Me.Bookmark = rst.Bookmark
     
     If Me.PartNumber = "11" Or _
       Me.PartNumber = "2" Or _
       Me.PartNumber = "10" Or _
       Me.PartNumber = "13" Or _
       Me.PartNumber = "45" Then
         Me.Line306.Visible = "True"
         Me.Line307.Visible = "True"
         Me.Line308.Visible = "True"
         Me.Line118.Visible = "True"
         Me.Line304.Visible = "True"
         Me.Line89.Visible = "True"
    Else
         Me.Line306.Visible = "False"
         Me.Line307.Visible = "False"
         Me.Line308.Visible = "False"
         Me.Line118.Visible = "False"
         Me.Line304.Visible = "False"
         Me.Line89.Visible = "False"

etc.

I didn't show every single line, label and field but you get the idea.

This works as expected... when I choose a PO number with a part number ID listed in the beginning of the code all of the stuff I want to appear works and if I change it to one not in the list all the stuff appears.

This works within the navigation form as well as outside of it.

The only thing is the form is still big with white space between the hidden stuff and the visible stuff at top and bottom.

This is where I have no idea what to do to make the form shrink or grow if the stuff is visible or not. I have seen that on reports but not forms and I have not been able to find anything of a solution online.
 
Hi

Glad you managed to get a solution.
For info, there should never be quote marks around True & False and I'm surprised what you wrote works reliably

Corrected versions:
Code: 
Me.Line306.Visible = True
...
Me.Line306.Visible = False

The only thing is the form is still big with white space between the hidden stuff and the visible stuff at top and bottom.

This is where I have no idea what to do to make the form shrink or grow if the stuff is visible or not. I have seen that on reports but not forms and I have not been able to find anything of a solution online.
Click to expand...

As I also deleted your database from my own computer, I can't give you a specific answer. However you could possibly:
a) add code to move the objects at the bottom of the form up when the controls are hidden
b) shrink that section (Detail?) as well e.g.
Code: 
Me.Detail.Height =3000
- values in twips 1440 twips = 1 inch; 567 twips = 1cm

Obviously you would then then to reverse all of that when the controls are visible. Sounds like a lot of effort for little gain

Instead, I strongly recommend you use a subform as previously suggested.
This would act as one control so could easily be shown / hidden as required.
When hidden reduce its height to zero or as small as Access allows
 
Just read your last post again.
Are you sure it's working properly as there is another "" issue

PartNumber is a number field.
There should also be no quotes around the number values. Use e.g.
Code: 
If me.partnumber=11 then
 
ridders said:
Just read your last post again.
Are you sure it's working properly as there is another "" issue

PartNumber is a number field.
There should also be no quotes around the number values. Use e.g.
Code: 
If me.partnumber=11 then
Click to expand...

Yea... it works perfectly with or without the quotation marks. Of course other than the resizing of the form.

I tried the Me.Detail.Height thing... didn't change anything. I tried Me.InsideHeight... didn't change anything.

Tried adding a sub in the OnLoad... didn't change anything. I didn't try in the AfterUpdate.

I have read a lot on the sizing but from what I can figure... you can't resize a form to smaller than the fields contained in it so even if they are not visible they are still there and therefore cannot shrink the form.

I am trying to set the field, lines, label height to 1 twip each if visible=false to see if that does anything.

*EDIT* that didn't work
 
Last edited:
Sorry you had trouble with adjusting the form height using code

Attached is a very simple form/subform example showing how the height of a subform can be changed at the same time as it is made visible / hidden.
In addition, the position of two other controls is moved up/down accordingly as is the height of the main form

When shrunk, the subform height has been set to zero ... even though it contains controls

The 2 forms just happened to be the first I had to hand from other posts
There's no significance to either of them
 

Attachments

Last edited:
You must log in or register to reply here.

Similar threads

W
Calculated field in form from another table
Replies
7
Views
355
Pat Hartman
P
V
Missing Value in combo Box while Opening another form using openArgs
Replies
3
Views
225
Uncle Gizmo
Uncle Gizmo
N
Solved Placing a label behind text box
Replies
9
Views
235
NigelShaw
NigelShaw
J
Continuous form problem.
Replies
2
Views
193
John Sh
J
M
Binding vs display value
Replies
5
Views
293
monika_V
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom