Can't add new rows to a form after migrating backend to SQL.

[yong_sa]

Hello:
I'm trying to migrate an MS Access Frontend/MS Access Backend to a MS Access Frontend/SQL Backend. I used Microsoft SQL Server Migration Assistant for Access to perform the migration.
I've been mostly successful, but I'm running into a showstopper snag.



On the Original Access Backend version, I'm able to add rows.

On the SQL Backend version, I can't add rows.



When I load this form (based on a date entered on a previous screen) using the SQL backend version, and there is already data, it shows the data (again not able to add a new row).



Any ideas on where I can start to fix this issue?

Thanks so much!

Sincerely,

yong_sa

[yong_sa]

Here are some screenshots of the Query that populates the form



as well as the form design itself :



and its data properties

[Gicu]

You probably did not specify the unique ID (primary key) when you linked the SQL table back into your Access front-end therefore the table is read only. Open the SSMS interface and make sure the table has the primary key properly set in SQL server (if using auto-numbers in Access make sure the SQL equivalent has the IsIdentity =True). Then refresh the link in your front-end and it should work.

Cheers,

[yong_sa]

Thanks Gicu for pointing me in a starting direction.

Here's what I have for the Table that the query gets its data from:



AbsentHoursID is the primary key for the table, and it looks like the Identity parameter is correct.

Thanks.

[Gicu]

If you look at the table in design view (ignore the warning and just open it in design view) in Access do you see the primary key? In regular (datasheet view) is the new record button on the bottom enabled?
EDIT: also check if the query is editable.

Cheers,
Vlad

[yong_sa]

Hi Vlad:

Yes, AbsentHoursID is the primary key. In datasheet view, I can click on the new record button and add a new row. The query seems to be editable. I'm able to change the filters add/remove columns etc.

Herb

[Gicu]

Any code in the form's open/load/current events?

I also noticed that the query is dependent on a date on another form, is that form open when you open the one with the problem?

You could try to add the primary key to both the query and the form (hide the control if you don't want to display it).

Cheers,
Vlad

[yong_sa]

This is the code to open the form after a date is selected:

Private Sub Command2_Click()


strAbsentFormSignedUser = " "
strAbsentFormSignedDateTime = " "

Call DeleteRecordsAbsentFormSignoffCheck


DoCmd.OpenQuery "Append AbsentForm Signoff Check", acViewNormal, acEdit

Call CheckAbsentFormSignoff("AbsentForm Signoff Check", "SignOffUser", "SignOffDateTime")


DoCmd.OpenForm "Absence Accounts Form Add Edit", acNormal, "", "", , acNormal

End Sub

[Gicu]

Hard to say what the code does without a sample db. I assume the code is running in the click event of a button on SelectAbsenceDate form? You seem to set to string varaibles to a space character, why?

Can you try to replace the last line with just

DoCmd.OpenForm "Absence Accounts Form Add Edit"

Try to remove the reference to the selected date in the query and see if the form allows additions.

Cheers,

[Gicu]

Does the [Absence Accounts Form Add Edit] form have any code in the open/load/current events?

[yong_sa]

Just on load. The code for the form is below. The stuff in bold is what's on the On Load.

Option Compare Database


Private Sub Combo13_AfterUpdate()


Hold.SetFocus


If Combo13 = 12 Then
    Combo30 = 3
End If


If Combo13 = 42 Or Combo13 = 47 Or Combo13 = 68 Then
    Reason.SetFocus
End If


End Sub


Private Sub Combo13_GotFocus()
    Me.Combo13.Dropdown
End Sub


Private Sub Combo17_AfterUpdate()


    [AbsentDate] = Forms![SelectAbsenceDate]![AbsenceDate]


    'DoCmd.OpenQuery "Populate Employee Defaults on Absence Form", acViewNormal, acEdit
    
    'I did not use the above query here for employee defaults.  I used Me.Combo17 procedures.
    'I used DoCmd.OpenQuery "Populate LTA Employee Defaults on Absence Form", acViewNormal, acEdit when
    'adding LTA employees from the other form.


    Me.OccupationCode = Me.Combo17.Column(4)
    Me.OccupationDescription = Me.Combo17.Column(5)
    Me.DefOccupationCode = Me.Combo17.Column(4)
    
    Me.Refresh
    
    Hold.SetFocus


End Sub


Private Sub Combo17_GotFocus()
    Me.Combo17.Dropdown
End Sub


Private Sub Combo30_GotFocus()
    Me.Combo30.Dropdown
End Sub


Private Sub Combo37_AfterUpdate()


    If [DefOccupationCode] = [OccupationCode] Then


        [Override] = False
    
    Else


     [Override] = True
    
    End If
    
    Me.Refresh


    'DoCmd.OpenQuery "Update Absence OccupationDescription", acViewNormal, acEdit
    
    Me.OccupationDescription = Me.Combo37.Column(1)
       
    Hold.SetFocus
    
End Sub


Private Sub Combo37_GotFocus()
    Me.Combo37.Dropdown
End Sub


Private Sub Command43_Click()


    DoCmd.OpenReport "Absent Hours from Absence Form", acViewReport, "", "", acNormal


End Sub


Private Sub Command45_Click()


    Call DeleteRecordsMissingCodeCheck
    
    DoCmd.Requery
    
    DoCmd.OpenQuery "Append to MissingCodeCheck"
    
    Call CheckForMissingCode("MissingCodeCheck", "EarningsCode")
    
    If boolMissingCode = True Then
    
        MsgBox "An Earnings Code must be entered for every employee!"
        
        Hold.SetFocus
        
    Else
    
        Hold.SetFocus
    
        Command45.Enabled = False


        DoCmd.OpenForm "AbsentForm Signoff", acNormal, "", "", acAdd, acNormal


    End If


End Sub


Private Sub Command51_Click()


    DoCmd.OpenForm "Message Form Add All For Stat Holiday"


End Sub


'------------------------------------------------------------
' Form_Load
'
'------------------------------------------------------------
Private Sub Form_Load()
On Error GoTo Form_Load_Err


    If boolAbsentFormSigned = True Then
        Command45.Enabled = False
    End If


    [Text48] = strAbsentFormSignedUser + "  " + strAbsentFormSignedDateTime


    DoCmd.GoToControl "Hold"
    
    If strPermissionLevel = 5 _
    Or strPermissionLevel = 6 _
    Then
    
        Command43.Visible = True
        
    End If




Form_Load_Exit:
    Exit Sub


Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit


End Sub




'------------------------------------------------------------
' Reason_AfterUpdate
'
'------------------------------------------------------------
Private Sub Reason_AfterUpdate()
On Error GoTo Reason_AfterUpdate_Err


    DoCmd.GoToControl "Hold"




Reason_AfterUpdate_Exit:
    Exit Sub


Reason_AfterUpdate_Err:
    MsgBox Error$
    Resume Reason_AfterUpdate_Exit


End Sub




'------------------------------------------------------------
' Combo30_AfterUpdate
'
'------------------------------------------------------------
Private Sub Combo30_AfterUpdate()
On Error GoTo Combo30_AfterUpdate_Err


    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="Reason" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application" xmlns:a="http://schemas.microsoft.com/office/accessservices/2009/11/forms"><Sta
    ' _AXL:tements><Action Name="GoToControl"><Argument Name="ControlName">Hold</Argument></Action></Statements></UserInterfaceMacro>
    DoCmd.GoToControl "Combo13"




Combo30_AfterUpdate_Exit:
    Exit Sub


Combo30_AfterUpdate_Err:
    MsgBox Error$
    Resume Combo30_AfterUpdate_Exit


End Sub




'------------------------------------------------------------
' Command39_Click
'
'------------------------------------------------------------
Private Sub Command39_Click()
On Error GoTo Command39_Click_Err


    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="Combo30" Event="AfterUpdate" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application" xmlns:a="http://schemas.microsoft.com/office/accessservice
    ' _AXL:s/2009/11/forms"><Statements><Action Name="GoToControl"><Argument Name="ControlName">Combo13</Argument></Action></Statements></UserInterfaceMacro>
    On Error Resume Next
    DoCmd.GoToControl Screen.PreviousControl.Name
    Err.Clear
    If (Not Form.NewRecord) Then
        DoCmd.RunCommand acCmdDeleteRecord
    End If
    If (Form.NewRecord And Not Form.Dirty) Then
        Beep
    End If
    If (Form.NewRecord And Form.Dirty) Then
        DoCmd.RunCommand acCmdUndo
    End If
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If




Command39_Click_Exit:
    Exit Sub


Command39_Click_Err:
    MsgBox Error$
    Resume Command39_Click_Exit


End Sub




'------------------------------------------------------------
' Command28_Click
'
'------------------------------------------------------------
Private Sub Command28_Click()
On Error GoTo Command28_Click_Err


    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="Command39" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application" xmlns:a="http://schemas.microsoft.com/office/accessservices/2009/11/forms"><
    ' _AXL:Statements><Action Name="OnError"/><Action Name="GoToControl"><Argument Name="ControlName">=[Screen].[PreviousControl].[Name]</Argument></Action><Action Name="ClearMacroError"/><ConditionalBlock><If><Condition>Not [Form].[NewRecord]</Condition><Sta
    ' _AXL:tements><Action Name="DeleteRecord"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[Form].[NewRecord] And Not [Form].[Dirty]</Condition><Statements><Action Name="Beep"/></Statements></If></ConditionalBlock><ConditionalBlock>
    ' _AXL:<If><Condition>[Form].[NewRecord] And [Form].[Dirty]</Condition><Statements><Action Name="UndoRecord"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[MacroError]&lt;&gt;0</Condition><Statements><Action Name="MessageBox"><Arg
    ' _AXL:ument Name="Message">=[MacroError].[Description]</Argument></Action></Statements></If></ConditionalBlock></Statements></UserInterfaceMacro>
    
    Call DeleteRecordsMissingCodeCheck
    
    DoCmd.Requery
    
    DoCmd.OpenQuery "Append to MissingCodeCheck"
    
    Call CheckForMissingCode("MissingCodeCheck", "EarningsCode")
    
    If boolMissingCode = True Then
    
        MsgBox "An Earnings Code must be entered for every employee!"
        
        Hold.SetFocus
        
    Else
    
        DoCmd.Close , ""


        Forms![SelectAbsenceDate]!Command33.Visible = True
        
    End If


Command28_Click_Exit:
    Exit Sub


Command28_Click_Err:
    MsgBox Error$
    Resume Command28_Click_Exit


End Sub

[Gicu]

Have you tried removing the criteria (date from the query)? Can you upload a small sample with just the objects involved? You can select the SQL table, do a CTRL C/CTRL V and paste it as a local table then remove all the sensitive data, only need a couple dummy records.

Cheers,
Vlad

[yong_sa]

Have you tried removing the criteria (date from the query)? Can you upload a small sample with just the objects involved? You can select the SQL table, do a CTRL C/CTRL V and paste it as a local table then remove all the sensitive data, only need a couple dummy records.

Yes, I've tried to removing the date from the query. It displays the whole table but still doesn't allow me to add additional records.

If I convert the tables involved from a SQL table to a local table, I am now able to add new records.

[Gicu]

And if you bypass the form and open the linked table in Access you can add new records? You have read write

[yong_sa]

And if you bypass the form and open the linked table in Access you can add new records? You have read write

Correct. But if I try to add another row via the Query Table in Datasheet view on Access, I am not able to. It's locked out.