When I add a new record in the main form, the subform shows values from previous record.

I am using Microsoft Access 2013 as the frontend and SQL Server 2012 as the backend of my application.

When my form opens, it opens to a new record and the subform shows no values.

If I lookup an existing record and then try to enter a new record, the subform shows the values of the record which I looked up.

I have the "Link Master Field" and "Link Child Field" set to the proper values.

I tried to Refresh, Repaint and Requery the subform.

Please see the attachment.

I do not have this issue when the backend is an Access database.

Thanks,
Screenshots.pdf
CloudAppsOwnerAsked:
Who is Participating?
 
Dale FyeCommented:
Unlike Access, which assigns an ID value to an autonumber as soon as a new record is initiated, SQL Server will not assign that value until the record is written.

I generally use code in the Form_Current event which hides the subform if the record in the main form is a new record.  Then, when I save the new record, and SQL Server assigns the ID value, set the visible property of the subbform bact to True.

Private Sub Form_Current

   me.subformControlName.Visible = NOT me.NewRecord

End Sub

Private Sub Form_AfterUpdate

    me.subformControlName.Visible = NOT me.NewRecord

End Sub
0
 
Gustav BrockCIOCommented:
Perhaps you in the OnCurrent event if the main form set the default value of the link ID to that of the current record.

/gustav
0
 
CloudAppsOwnerAuthor Commented:
Dale,

Thanks for pointing me in the right direction.

Here is the code that I ended up using:

Private Sub Form_Current()
On Error GoTo HandleError

    Me!InspectionDate.SetFocus
   
    If Me.NewRecord = True Then
        Me.frmInspectionsGeneralSubform.Form.DataEntry = True
    Else
        Me.frmInspectionsGeneralSubform.Form.DataEntry = False
    End If

ExitHere:
    Exit Sub

HandleError:
    MsgBox "The Form_Current Subroutine encountered an unexpected error. " & Chr(13) + Chr(10) & _
        "The Error Number is: " & Err.Number & Chr(13) + Chr(10) & _
        "The Error Description is: " & Err.Description, vbExclamation, "Error Handling Routine"
    Resume ExitHere
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.