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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CloudAppsOwnerAuthor Commented:

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

    If Me.NewRecord = True Then
        Me.frmInspectionsGeneralSubform.Form.DataEntry = True
        Me.frmInspectionsGeneralSubform.Form.DataEntry = False
    End If

    Exit Sub

    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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.