Linking DE form and subform to same record in same table

[A1M6K]

Very new to VBA and struggling to link the main form and the subform to the same record in the same table. Currently they are each entering separate records in the same table.

They are linked by the date in the parent-child connection and both contain a date textbox, the only difference is that the main allows the user to enter the date and the date is invisible on the subform.

I have tried a few pieces of code that I have found on similar boards across the internet but none seem to work for me. I feel like part of the problem is that I don't know where exactly I should put it - ie. onentry, beforeupdate, etc.

As some further background to this problem, the purpose of having linked record form and subform entries to the same table is that the subform pops up based on selection on the main form to allow further entry if needed. I don't want to make it a tab because I'm trying to poka yoke this process to prevent addition entry where it is not needed.

[NeoPa]

Are you using the term subform as in Access's SubForm, or do you mean something else. Access SubForms don't pop up. They are a part of the main Form they're attached to.

[A1M6K]

It is an Access Subform, it is part of the main but I have made it invisible and also made it open when the user selects 'Yes' on a check box. My apologies for the confusion, that is what I meant when I said it pops up.

[NeoPa]

Interesting concept. Linking a main and a sub form together is usually done by setting the Link Master Fields and Link Child Fields properties on the SubForm control itself (NOT the sub form but the SubForm/SubReport control on the main form which contains the sub form - if that makes sense).

Linking to the same record may be a problem. You may find that one of them locks out the other and you can get in a mess if you're not very careful.

[A1M6K]

Thanks NeoPa.

So to be sure I understand you correctly, accessing the subform from where it is situated on the main as opposed to opening it separately and accessing the individual properties? If that is what you mean, I have done that and set both of those fields that you mentioned to Date.

Although it might be on the messier side I'm determined to make it happen one way or another. Alternatively, is there a way to have fields on a form become visible based on a previous selection (such as my check box)? This would eliminate the need for a subform while serving the same purpose.

[A1M6K]

Hello All,

I seem to have cracked the code (haha) after trying a few different combinations of things from various posts. For those who are looking through this thread for a similar answer, here are the various combinations that I am using that seem to work so far.

Please note that I switched to using ID as the parent-child connection and just made it invisible on both form and subform. I also use a checkbox to open the subform instead of making it visible on the main all the time.

In the main form:

Private Sub ID_AfterUpdate()

    Me.Form.AllowAdditions = False
    DoCmd.Requery "FrmEntryHelp1"
    
End Sub
------------------------------------------------
Private Sub Help_still_required__Click()
  
    If True Then
        If Me.Dirty = True Then Me.Dirty = False
        DoCmd.Save acForm, "FrmEntry"
        DoCmd.OpenForm "FrmEntryHelp1", , , "ID=" & Me!ID
    End If
           
End Sub

In the subform:

Private Sub btnHelpRequest_Click()
    If CheckForEmpty = False Then
        MsgBox "Please fill in empty fields."
    Else
        DoCmd.Close acForm, "FrmEntry"
        DoCmd.Save acForm, "FrmEntryHelp1"
        DoCmd.Close acForm, "FrmEntryHelp1"
    End If
    Form_FrmTrackerMain.Requery
    Form_FrmTrackerMain.Refresh
    
End Sub

I believe the fact that I close the main (FrmEntry) then save and then close the sub (FrmEntryHelp1) has made quite a difference. Again, this is a combination of solutions from different threads that all suddenly came together to work after trying SEVERAL different combinations. Hopefully this will work for someone else too!

[NeoPa]

A1M6K:
I have done that and set both of those fields that you mentioned to Date.

Let me correct you on terminology here. I think I understand what you mean but terminology - its use and misuse - can be a perennial source of problems so getting it right can prove particularly important. The two items that needed setting were properties of the SubForm control. If you have date fields you should certainly try, where possible, to give them a name other than 'Date', which is, of course, a reserved word. You can use Reserved Words a field names, but it's not generally recommended due to the likelihood of misunderstandin g being introduced.

A1M6K:
Alternatively, is there a way to have fields on a form become visible based on a previous selection (such as my check box)? This would eliminate the need for a subform while serving the same purpose.

Absolutely. This is quite a common requirement. The example below illustrates the approach. I have a table about people with one Field called Nationality and another called VisaExpiry. On my form I only want to show the latter if the Nationality value =/= 'British'. So I have two controls (cboNationality & txtVisaExpiry) on the form to reflect these Field values - one of which should only show when the Nationality value is anything other than British.

We need to make sure the visibility is set under two circumstances.

1. When we change from showing one record to another.
2. When the value within a single record is changed.

Private Sub Form_Current()
    Call SetVUVisibility
End Sub

Private Sub cboNationality_AfterUpdate()
    Call SetVUVisibility
End Sub

Private Sub SetVUVisibility()
    With Me
        .txtVisaExpiry.Visible = (.cboNationality <> "British")
    End With
End Sub

NB. As a general rule we don't allow members to set their own posts as Best Answer. Under special circumstances we may allow it, but this isn't really one of those I'm afraid.