MS Access Fill in ID in the Main Form after select drop down from combo box in Subform

Question

I have similar situation with this link: MS Access automatically fill id in main form when data is entered in subform

In short, I wanted to be able to create autonumber ID in the main form when I enter data in subform. This is possible with this code inside sub form:

Private Sub Form_Dirty(Cancel As Integer)
With Me.Parent
    If IsNull(!MainFormID) Then
        ' Change any field to create the parent record
        .Description = "Test"
        ' Save changes on the parent form.
        .Dirty = False
    End If
End With
End Sub

I have pasted the VBA Code and create additional field called "Description" which capture changes in the main form. This works if I fill in a text box in subform. However, if I select an item from combobox it doesn't work.

Lets say my combobox is cboSelectItem, I also have tried changing the event to below but to no available.

Private Sub cboSelectItem_BeforeUpdate(Cancel As Integer)

Anyway to change the code to be able to run for combo box and still retain my selection from the drop down?

Please, Brian, don't make me read another thread in order to understand your question. Instead, amend your question to be fully explained here and add a reference about where you copied the code from. — Variatus
Hi @Variatus Have included the background information. Are you able to help on this? — Brian Chew
Is Sub Form_Dirty a procedure in the sub form? How it is called? Where does the Cbx you mention come in? I'm not familiar with IsNull(!MainFormID) syntax. So, I would question the use of the exclamation point. Your syntax for addressing Description and Dirty fields in Me.Parent looks odd to me. If these are TextBoxes I would expect to set the Value or Text properties. If they are labels the default might be Caption. Dirty could be a checkbox. I think you should specify the properties you mean. But no, sorry, I don't think I am qualified to assist with VBA for Access. — Variatus

Krish Krish · Accepted Answer · 2020-03-17T11:05:26

You can intercept the On Enter event of the sub form frame and check if the "parent" form has an active record. In this way, you can still enfore relationship between parent and sub form when adding new records to the "parent" form.

On the main form select the Sub form frame object (where you usually select link master fields \ link child fields. That fram should have two events, On Enter & On Exit.

Use the On Enter event to check if the "parent" form has any record. Note, you are already in the parent forms so parent form = current form.

The event should look like this:

'This code should appear in the parent form's code page
Private Sub SubFormName_Enter()
    On Error Resume Next
    If (Me.NewRecord) Then

        Me!Description = "test"
        Me.Dirty       = False
    End If
End Sub

This should work when you enter the sub form and parent form has no records. If it's good idea doing this way is up to you to decide.

MS Access Fill in ID in the Main Form after select drop down from combo box in Subform

1 Answers