Another Visible/Hidden Form Field Question

[psyc0tic1]

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

This was good stuff. I was able to implement your code and make my form grow and shrink based on the choices in the combobox in the header of the main form called frm_labtestinput which used a query called qry_labtestinput.

I took all of the lines, labels, and fields that I wanted to show and hide and put them in their own form called frm_usbinput and added that form to the main form as a subform. (although I have not been able to make the actual subform appear)

Now I don't know much about subforms but from what I have been reading... subforms are typically used in datasheet view and are maybe not designed for data input?

Anyway... I did read that typically the subforms are related to a different table than the main form.

So here is what I am using:

Main form - choose a record from the combobox in the header and it populates the PO number and part number fields in the detail section leaving more fields that need to be filled out to update the data for that chosen record.

Now the usb data subform is in the middle of these fields on the main form and ALSO needs to have data updated in the fields that corresponds with that PO number and part number choice.

This data is all stored in the same table though... do I need to split it up and store the USB data in it's own table? I have a feeling I do because I can't figure out how to link the two forms together with just one table. I would rather now split it into two but I will if I have to.

I would need some advice on how to properly structure the new table so both can relate to each other.

In the sample database you attached the main form has no data input... only the subform... mine needs both but it is not creating new records... it is only updating existing records.

 

[psyc0tic1]

right now I have this code in my frm_labtestinput:

Private Sub ShowStretch()

    Me.InsideHeight = 9800
    
    Me.UsbInput.Visible = True
    Me.UsbInput.Height = 7920
    Me.Label46.Top = 10000
    Me.LabDefectFound.Top = 10000
    Me.Label48.Top = 10000
    Me.Label47.Top = 11000
    Me.LabAtt.Top = 11000
    Me.UpdateRecord.Top = 11100

End Sub

Private Sub HideShrink()

    Me.InsideHeight = 3000

    Me.UsbInput.Visible = False
    Me.UsbInput.Height = 0
    Me.Label46.Top = 1800
    Me.LabDefectFound.Top = 1800
    Me.Label48.Top = 1800
    Me.Label47.Top = 2880
    Me.LabAtt.Top = 2880
    Me.UpdateRecord.Top = 3050
    
End Sub

and I this is now the code for the combobox in the header:

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
      ShowStretch
    Else
      HideShrink
    End If
     
End Sub

The subform does not show but the form grows and shrinks based on the part numbers listed in the AfterUpdate.

 

[isladogs]

Glad to see my example helped you achieve a lot of what you wanted

I don't know much about subforms but from what I have been reading... subforms are typically used in datasheet view and are maybe not designed for data input?

Anyway... I did read that typically the subforms are related to a different table than the main form....

This data is all stored in the same table though... do I need to split it up and store the USB data in it's own table? I have a feeling I do because I can't figure out how to link the two forms together with just one table. I would rather now split it into two but I will if I have to.

I would need some advice on how to properly structure the new table so both can relate to each other.

There are many different ways of using form/subform combination - the format is VERY adaptable
Usually both will contain data - the form will often be a single record & the subform either continuous or a datasheet.
The record source is usually 2 different tables (or queries) but you can also use different fields from the same table (as in your case)
Usually both forms will be linked using master/child field(s) that are in the record source for each.

But all the above isn't always the case. As in my previous example. the main form may have no data & act as a placeholder for the subform.

Attached is a new postal address example where both main & subform contain data.
I've deliberately kept the code to be almost identical to the previous example
The layout is single main & datasheet subform.

The common postcodes field acts as the master/child field

In your case, use different fields from the same table and ensure the primary key field is in both main & subform (it doesn't have to be visible) & use that as the master/child link field

If you are having trouble getting the subform to show/hide, remember that the subform is a control in the main form. The subform control may not be the same as the actual subform name. Check this by clicking the outline of the subform so you are looking at the control. This can be tricky if you have Access 2016/365. Then either change the control name to match the subform name or change the .Visible code to reference the control name

NOTE: Although at the end of all this you should have a form/subform that does what you want, it is still important to remember that your table isn't normalised. You should still deal with that issue as a priority

HTH

 

[psyc0tic1]

In your case, use different fields from the same table and ensure the primary key field is in both main & subform (it doesn't have to be visible) & use that as the master/child link field

That I believe this is the trick I was looking for on the linked field.

I figured out that from other attempts at this that I had all the lines, labels, fields etc visibility set to no causing the subform not to show and corrected that. Everything hides and shows just as I was looking for now.

However... due to this code:

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

When I click on a field in the subform to update data it just gives me the message box telling me to update all fields. The code recognizes only the fields in the main form not not the subform.

This I believe is now the only thing stopping this from working. I can't test whether or not it will update a record from both forms because of this.

Any suggestions on how to have both forms covered by this?

For full form code reference here is all the code in the frm_labtestinput:

Option Compare Database
Option Explicit
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 Form_Load() 'added this so the form would act right on form load otherwise it was not correct per part number
    If Me.PartNumber = 11 Or _
       Me.PartNumber = 2 Or _
       Me.PartNumber = 10 Or _
       Me.PartNumber = 13 Or _
       Me.PartNumber = 45 Then
      ShowStretch
    Else
      HideShrink
    End If
End Sub

Private Sub ShowStretch()

    Me.InsideHeight = 9800
    
    Me.UsbInput1.Visible = True
    Me.UsbInput1.Height = 7920
    Me.Label46.Top = 10000
    Me.LabDefectFound.Top = 10000
    Me.Label48.Top = 10000
    Me.Label47.Top = 11000
    Me.LabAtt.Top = 11000
    Me.UpdateRecord.Top = 11100

End Sub

Private Sub HideShrink()

    Me.InsideHeight = 7560

    Me.UsbInput1.Visible = False
    Me.UsbInput1.Height = 0
    Me.Label46.Top = 1800
    Me.LabDefectFound.Top = 1800
    Me.Label48.Top = 1800
    Me.Label47.Top = 2880
    Me.LabAtt.Top = 2880
    Me.UpdateRecord.Top = 3050
    
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
     
    If Me.PartNumber = 11 Or _ 'this changes it as you choose different records from the combobox
       Me.PartNumber = 2 Or _
       Me.PartNumber = 10 Or _
       Me.PartNumber = 13 Or _
       Me.PartNumber = 45 Then
      ShowStretch
    Else
      HideShrink
    End If
     
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

 

[psyc0tic1]

For testing purposes I commented out the code that was not allowing me to click on a field in the subform and it does work as expected. Of course I had to move the:

 Me![LabUpdate] = Date

to this area to get that to work again:

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
        Else
        Me![LabUpdate] = Date
    End If
End Function

So now everything works except the commented out code that makes sure they don't leave any blank fields. Not sure I absolutely have to have it but people sure like to omit information if you do not harass them when they do.

 

[psyc0tic1]

it is still important to remember that your table isn't normalised. You should still deal with that issue as a priority

On that side note... I was reading about normalizing the table but all I really saw was statements about removing duplicated values. When I run the wizard it wants to make a bunch of tables out of my one table... do I really need to do that? What is really wrong with having all of the record data fields in the one table?

I have a data table and I have tables for lookup data.

Pardon my ignorance on this matter.

 

[isladogs]

Hi

There's a lot to answer in your 3 posts so if I forget something let me know.

However, do bear in mind when you asked me to remove my solution from the thread back in post #14, I also removed it from my PC. With hindsight, that's a pity but too late now. So my memory of your db is getting increasingly hazy.

The Analyse Table wizard is very good at identifying issues with normalisation but its not perfect. In an ideal world you would see this:

If its suggesting splitting tables, its a good indication that there are issues that need resolving. An alternative may be to use fewer fields so one record is split into several records. As I can't recall your table design now, I can't be specific.

I do recall this message driving me nuts:

strMsg = "Please click the Update button to save your changes, " & vbNewLine & "or Escape to reset them."

If you don't need it I'd bin it.
There may be better ways of ensuring all data is completed e.g. set certain fields as REQUIRED.

When I click on a field in the subform to update data it just gives me the message box telling me to update all fields. The code recognizes only the fields in the main form not not the subform.

If you need to reference a subform control from the main form, use

Me.SubformName.Form.SubFormControlName

e.g. Me.UsbInput1.Form.

To reference a main form control from a subform use

Parent.MainFormControlName

HTH

 

[psyc0tic1]

Hi

If its suggesting splitting tables, its a good indication that there are issues that need resolving. An alternative may be to use fewer fields so one record is split into several records. As I can't recall your table design now, I can't be specific.

I looked into splitting the table up manually because the wizard was dividing up fields in a manner that would make functioning hard to follow.

So when I tried to do it manually it told me there were too many fields for the relationships linking. UGH.

I suppose I don't really need to split it up as it works great as is.

I understand what you are saying about the harassment message... I will see if I can get the gentleman who wrote that to try to solve that issue otherwise I will leave it out because everything works great as of right now.

I did get a bit more fancy and since some parts that have USB only have one port and others have two so I made another subform with showing only the fields for a single USB port part and nested it in the same spot as the other one and I tweaked the code so it shows the full set of fields if there are 2 ports or it shows the half amount of fields if only one port.

As of this moment the only little annoyance is the tab order.

I got it to tab to the subform correctly but when it should tab to the fields at the bottom of the main form after filling out all subform fields... it just blanks all the subform fields andstarts over.

this is very strange but I am sure I will figure it out sooner or later.

ridders... I can't thank you enough for all your help with this little silly modification I am doing.

Funny... when I sit back and look at the solution now that it is complete... it really wasn't that hard of a solution if it weren't for my moronic brain.

I attached some screenshots to show the results:
No USB Ports:

One USB Port:

Two USB Ports:

 

[isladogs]

Looks good....
You can easily change the tab order from the ribbon
As for the 'blanking out' issue, perhaps adding the line Me.Refresh will help???? Or maybe add the line DoEvents???

One more thing
Attachment fields are best avoided as they cause databases to significantly increase in size, reducing performance. Eventually you may hit the 2GB limit.

A much better approach is to store the files externally. You then add a text field with the file path of each file. If you google or do a forum search, Yu should find working examples

 

[psyc0tic1]

Looks good....
You can easily change the tab order from the ribbon
As for the 'blanking out' issue, perhaps adding the line Me.Refresh will help???? Or maybe add the line DoEvents???

One more thing
Attachment fields are best avoided as they cause databases to significantly increase in size, reducing performance. Eventually you may hit the 2GB limit.

A much better approach is to store the files externally. You then add a text field with the file path of each file. If you google or do a forum search, Yu should find working examples

Excellent advice... I will look into that as well.

Thank you again for everything.

 

[Gasman]

I
As of this moment the only little annoyance is the tab order.

I got it to tab to the subform correctly but when it should tab to the fields at the bottom of the main form after filling out all subform fields... it just blanks all the subform fields andstarts over.

this is very strange but I am sure I will figure it out sooner or later.

I thought that was the default behaviour?


See https://www.tek-tips.com/faqs.cfm?fid=1064

 

[psyc0tic1]

I thought that was the default behaviour?


See https://www.tek-tips.com/faqs.cfm?fid=1064

Are you saying that blanking and starting over is the default behavior or the default behavior should be that it goes back to the next control on the main form?

I have tried all the coding I could find on the subject and this one almost works correctly. I put this in the KeyUp for the last control (named Part55MinReading1_2) on the subform (named frm_usbinput) to then go to the next control (named LabDefectFound) on the main form (named frm_labtestinput).

Private Const conNavKey As String = vbKeyTab
Private Sub Part55MinReading1_2_KeyUp(KeyCode As Integer, Shift As Integer)
If KeyCode <> conNavKey Then Exit Sub
Forms!frm_labtestinput!LabDefectFound.SetFocus
End Sub

Now this almost works correctly... when I tab to that last control it instantly jumps to the next control on the main form called LabDefectFound rather than waiting for me to hit tab again. Effectivly not allowing me to enter data in the last field on the subform called Part55MinReading1_2

Any ideas on how to correct this code for that?

 

[psyc0tic1]

Well... actually this almost works when I am viewing the form stand alone but from within my navigation form it errors on finding the main form focus again.

 

[isladogs]

Richard
I'm not sure this is default behaviour and it may be worth posting a screenshot of the form/subform in design view to illustrate the issue.

But I suggest scrapping the KeyUp code.
Instead add after update code to the last control in your subform to go to the next control in your main form. Something like

Parent.LabDefectFound.SetFocus

If that works, then we can work out what to do if the final subform control isn't updated.
Possibly use the lost focus event instead or as well.

 

[Gasman]

Are you saying that blanking and starting over is the default behavior or the default behavior should be that it goes back to the next control on the main form?

I have tried all the coding I could find on the subject and this one almost works correctly. I put this in the KeyUp for the last control (named Part55MinReading1_2) on the subform (named frm_usbinput) to then go to the next control (named LabDefectFound) on the main form (named frm_labtestinput).

Private Const conNavKey As String = vbKeyTab
Private Sub Part55MinReading1_2_KeyUp(KeyCode As Integer, Shift As Integer)
If KeyCode <> conNavKey Then Exit Sub
Forms!frm_labtestinput!LabDefectFound.SetFocus
End Sub

Now this almost works correctly... when I tab to that last control it instantly jumps to the next control on the main form called LabDefectFound rather than waiting for me to hit tab again. Effectivly not allowing me to enter data in the last field on the subform called Part55MinReading1_2

Any ideas on how to correct this code for that?

I was saying/thinking that if you are on a record in the subform and you tab off the last control, then Access thinks you want to start a new record.?

Doesn't even have to be a subform, I've used that feature to just add new records in a single form when I was keying a huge amount of data.

That is why you have to use a combination of keys Ctrl + Tab etc ?

 

[isladogs]

I was saying/thinking that if you are on a record in the subform and you tab off the last control, then Access thinks you want to start a new record.?

Doesn't even have to be a subform, I've used that feature to just add new records in a single form when I was keying a huge amount of data.

That is why you have to use a combination of keys Ctrl + Tab etc ?

Yes, I understood that.
Once the tab reaches the last control on the form/subform it will indeed go to the next main form record.

BUT if there are other main form controls after the subform in the tab order, it will go from main form to subform, cycle through the subform control then return to the remaining main form controls in turn BEFORE moving to the next record.

If you look at my example in post 23, this goes to the Hide Subform button on the main form after tabbing through the subform

 

[psyc0tic1]

Richard
I'm not sure this is default behaviour and it may be worth posting a screenshot of the form/subform in design view to illustrate the issue.

But I suggest scrapping the KeyUp code.
Instead add after update code to the last control in your subform to go to the next control in your main form. Something like

Parent.LabDefectFound.SetFocus

If that works, then we can work out what to do if the final subform control isn't updated.
Possibly use the lost focus event instead or as well.

ridders,

I put this code in the AfterUpdate of the last control (bottom right corner) on subform 1 (named frm_usbinput):

Private Sub Part55MinReading1_2_AfterUpdate()
Parent.LabDefectFound.SetFocus
End Sub

Parent form has 5 fields at top of detail area and 2 fields and a command button at the bottom.

Child (1 of them) form has 1100 fields. All child form fields have a default value set to "0")

Here is the result. If I simply tab through the controls from the parent form to the child form without updating the default value of "0" in the last one it blanks all of the child form fields except for fields 1,2,5,6,9,10,13,14, 17&18 and focuses back on the first control of the child form. If I update the values in all the fields including the last one on the child form and hit the tab key it does jump to the next control on the parent form but it also blanks all of the fields in the child form except for the ones I mentioned above.

Screenshots of the forms in design view (at the moment I am only working with the parent form and subform 1):
Parent Form:

Subform 1:

Subform 2:

 

[psyc0tic1]

Screenshot of what the form looks like after I hit the tab key after updating the values:

 

[Gasman]

OK Colin, you've got me. How does it do that.?
I thought at first it was due to AllowAdditions = No.
I can see that if I AllowAdditions then I can add a new record and if I complete said new record I will be offered another record to complete a I thought I would. However if I just tab through the controls and do not enter data in the last control, it goes to the mainform button.?

Yes, I understood that.
Once the tab reaches the last control on the form/subform it will indeed go to the next main form record.

BUT if there are other main form controls after the subform in the tab order, it will go from main form to subform, cycle through the subform control then return to the remaining main form controls in turn BEFORE moving to the next record.

If you look at my example in post 23, this goes to the Hide Subform button on the main form after tabbing through the subform

 

[isladogs]

@gasman
I wish I could say it's done by some very clever coding but I would be lying.
There is no trickery and indeed no code.
I did set AllowAdditions=No but that was nothing to do with this thread
If you look at the tab order for the main form, the subform control precedes the hide subform button so it tabs from subform to that button

@Richard
Sorry I got led astray by your key up code
As stated above, you don't need any code in the last control on your subform.
Remove everything you've done for this issue.
Just make sure the tab order has subform before lab defect textbox and it should tab correctly