Solved Add a new record to a filtered form, and have the linked field populated (1 Viewer)

[acm-gps]

I'm a university case manager and newbie Access user. I created a database where a main form presents student data, and a subform (CaseNotesF) is where I enter case notes. The notes I enter in this subform automatically populates the linked student id number field (UID) in the CaseNotes table.

Yesterday, I moved the database to our work's network drive so additional case managers can use this database. Since the database was running slowly, I decided to remove the CaseNotesF subform and instead insert a command button on the main form to open the case notes form separately. Initially it open ALL records for the case notes form, so I entered the following VBA code to make sure the case notes that were visible were filtered for the student I am looking at in the main form:

Private Sub CaseNts_Click()
    If Not IsNull(Me.UID) Then
        DoCmd.OpenForm "CaseNotesF", , , "UID=" & UID
    Else
    MsgBox (" Doh! You cannot add notes to a blank record. Please enter student info first. ")
    End If
    
End Sub

THE PROBLEM I'M FACING:
I'm having issues with adding NEW records to case notes now that Case Notes is a separate form and not a subform. When I begin a new case notes record, the field that shows the UID goes blank. Consequently, the data I enter is saved, but if I were to open the case notes form again, it doesn't show up because the UID was not saved with the case notes. I don't know how to make the UID (which is the variable from the main student form that filters the case notes form) save with the case notes form. As a result, the new case notes I've entered since making this change are "orphaned"---I've had to manually copy/paste student id numbers into these orphaned records as a workaround for today to run some reports, which I know is silly.

Can someone please let me know what I am missing to make this work? I would like to avoid going back to having case notes as a subform since some students have LOTS of records, and I don't want to slow the database.

Thank you so much!

 

[theDBguy]

Hi. Welcome to AWF!

You have, at a minimum, two options: you can set the default value of the foreign key field when you open the form, or use another form/subform setup for your second form.

 

[acm-gps]

@theDBguy : Thanks for your response-- I managed to create a new child form to fix the problem.

 

[Gasman]

The way I did it was to test if new record and if it was then set the field to the value passed in via Open Args
I was doing this I seem to recall in the Current event of the form. However @Pat Hartman advised that was dirtying the form inadvertently.

In my case I was pretty much the only user, so would be adding extra info.

However, she advised to put it in another event to get the same result that would not dirty the form until it was dirtied by the user (me)

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
    Me.cboClientID = Me.OpenArgs
End If

End Sub

Called by

DoCmd.OpenForm "frmFee", , , "ClientID = " & Me.ClientID, , acDialog, Me.ClientID