Subform losing focus when Adding new record

Question

My DB is split into Front and Back. I have three tables that I am working with in this problem; tblMain, tblDetail, tblNote. I know that the number of records is relatively small but I am designing this for long term use. I have a bound form (frmMain) for tblMain that has a subform (subDetail) for tblDetail. SubDetail has a subform (subNote) for tblNote. When I click a button on the main form, frmMain, the subDetail form becomes visible and allows me to add a new record. I copied the button and the VBA to subDetail so I could do the same thing for adding a record to subNote if needed. However, after the record is created, the subNote form disappears and focus is returned to subDetail. If I move to a new record then back, the subNote form becomes visible and shows an empty record except for the PK. I've checked all the properties of both subDetail and subNote. They are the exact same except for the OnCurrent event for subDetail. I have the following for the subDetail OnCurrent Event.

'Hide subform if no records
Private Sub Form_Current()
    Me!ctrlmlsID.SetFocus
With Me!subNote.Form
    .Visible = (.RecordsetClone.RecordCount > 0)
End With

End Sub

This hides or displays the subform, subNote, depending on if there is any records or not. Any help in figuring out this behavior would be appreciated.

Here is the code for adding a new record to subNote upon button click.

If Me.Dirty Then Me.Dirty = False
Dim AddTransID As String
AddTransID = Me!ctrlID.Value
Me!subNote.Visible = True
Me!subNote.SetFocus
DoCmd.GoToRecord , , acNewRec
Me!subNote.Form!ctrlID = AddTransID
Me!subNote.Form!ctrlType.SetFocus

I've used breakpoints and the adding record works just fine. It's just the subform going invisible again and the focus being changed back to the parent that is the issue because it won't allow those notes to be added.

Chase Saunders Chase Saunders · Accepted Answer · 2013-12-31T19:27:55

I get the impression you are fighting Access here.

You normally do not need a button to create a new record using a properly bound subform with AllowAdditions = True. That will happen automatically.

The problem you are running into may stem from the fact that the new records don't really exist yet until saved (as you observe there is no PK value). So the logic to hide subforms isn't going to work. If you insist on staying with this approach you might try

.Visible = (.RecordsetClone.RecordCount > 0 or .NewRecord)

But I think your life will be easier if you remove the buttons and code you have mentioned and let Access do what it is good at doing. Make sure your subform data binding properties are set right.

Subform losing focus when Adding new record

1 Answers