Subform Loses FK After First Record

[dascoli]

I am building a database to track client information and the services they need. I have a form that is used for the entry of client information and a form that tracks the services the client needs.



I have the ClientID as a primary key in a table named ClientEntry and as a foreign key in a table named Assessment. When done entering the client's information a button is used to open the assessment form (frmAssessment).

In the On Click event of the button, there is vba code that opens the assessment form, checks to see if there is a record that matches the ClientID, if it doesn't exist it adds the ClientID to the Assessment table. If it does exist it passes the ClientID to frmAssessment.

Private Sub btnOpenAssess_Click()

'Beginning of error trapping
On Error GoTo ErrorHandler


'If one of the required fields is empty then
If IsNull(Me.cboEntryPerson) Or IsNull(Me.Entry_Date) Or IsNull(Me.txtFirstName) Or IsNull(Me.txtLastName) Or IsNull(Me.Birth_Date) Then
    
    'Show a message box prompting user to fill in the required fields
    MsgBox "A required field was left blank!", vbOKOnly, "Required!"
    
Else
    
'Identify Variables
Dim longClientID As Long


'Assign Client ID to variable
longClientID = Me.pkClientID


'Close Client Entry Form and Save
DoCmd.Close acForm, "frmClientEntry", acSaveYes




'see if record exsists
If IsNull(DLookup("ClientID", "Assessment", "ClientID = " & longClientID)) Then


 'If record does not exsist then the longClientID is added to the recordset
 Dim db As Database
  Dim rs As DAO.Recordset


  Set db = CurrentDb
  'Set the record set to be used in the following code to the Assessment record set
  Set rs = db.OpenRecordset("Assessment") '// Or a specific query in the parentheses.


    'Add a new record set
  rs.AddNew
    'Set the record set field ClientID value to the variable longClientID
  rs("ClientID").Value = longClientID
  rs.Update




'Opens form to [Table]![Field] = longClientID
DoCmd.OpenForm "frmAssess", , , "[assessment]![ClientID] = " & longClientID
Else


'Open Assessment Form and assign the longClientID variable to the ClientID field in the Assessment Form
DoCmd.OpenForm "frmAssess", , , "[assessment]![ClientID] = " & longClientID


'End second If Statement
End If


'End First If Statement
End If


'Continue error trapping


ExitHandler:
'Exit subroutine when ExitHandler called
Exit Sub


ErrorHandler:
    'Find specific error number (#94)
    Select Case Err
        Case 94
        'Do not change mouse pointer to hourglass
        DoCmd.Hourglass False
        'ExitHandler called
        Resume ExitHandler
    'Handle all other errors as normal
    Case Else
        'Display typical error message box
        MsgBox Err.Description
        'Do not change mouse pointer to hourglass
        DoCmd.Hourglass False
        'ExitHandler called
        Resume ExitHandler
    'End error trap
    End Select
End Sub

This code works perfectly and transfers the ClientID to the next form (frmAssessment).

My problem comes from the frmAssessment. I used to have multiple check boxes on the form for each individual service. This is obviously poor design, but it worked. Since then, I have been instructed to add a time frame to frmAssessment that can be used to specify a time frame for each service.

So to make this happen I figured I'd change the design of that form entirely. Instead of multiple check boxes I chose to use a subform in datasheet view (sfrmClientAssess). I did this by making the frmAssessment bound to the Assessment table. Then I created a separate form that had two combo boxes. Both are just lists stored in fields in the Assessment table. One combo box has the time frames listed, the other has the services listed.

Here is where my problem begins. The form works well, except that when I open the Assessment table after adding records to the subform, only the first record logs the ClientID field. The other records (no matter how many I add) have the ClientID blank. I've attached a picture below that shows what the Assessment table looks like when it's open.



I cannot figure out why the form drops the ClientID after the first record.

The frmAssessment has the ClientID as a hidden field in the detail section of the form.

Any help on this would be greatly appreciated!

[June7]

You are opening frmAssessment independently, not as a subform? Then you are only populating field of first record. There is no way Access knows the next record belongs to the same client. There is no 'link' as would be the case in a form/subform arrangement. Options:

1. use form/subform

2. use code to set the DefaultValue property of the ClientID textbox in frmAssessment when it opens

[dascoli]

Thank you very much! That makes a lot of sense! I knew it was going to be something simple that I had overlooked.

That works perfectly now!

You are a life saver!