Trouble adding and editing with same form.

[brewpedals]

Hi all,

I have one form whose record source is a table. I am attempting to use the same form to both add new records and edit records. I also use a hidden form to store the users ID for comparison to prevent users from editing records they did not create.

My problem is I can not create a new record unless the users ID matches the user ID in the first record? I get the error:

" You can't go to the specified record. You may be at the end of a recordset."

frm_DMSS is the form I use to add & edit records
frm_DetectIdleTime is the form I use to store the user ID

Here is my Form Open Code for frm_DMSS :

Private Sub Form_Open(Cancel As Integer)
    
    Dim strUserID As Single
    Dim strOwnerID As Single
    
    strUserID = Forms!frm_DetectIdleTime!UserID.Value
    strOwnerID = Me!txt_OwnerID.Value
   
   Select Case strOwnerID
        Case Is = strUserID
            Me.[Form].[AllowAdditions] = True
            Me.[Form].[AllowEdits] = True
            Me.[Form].[AllowDeletions] = True
        Case Is <> strUserID
            Me.[Form].[AllowAdditions] = False
            Me.[Form].[AllowEdits] = False
            Me.[Form].[AllowDeletions] = False
            Me.Rev_date.OnClick = ""
            Me.cmd_Delete_doc.Enabled = False
            Me.cmd_delete_file.Enabled = False
            Me.cmd_send.Enabled = False
            Me.cmd_browse.Enabled = False
            Me.Frame372.Enabled = False
            Me.Child399.Enabled = False
            Me.ctl_subfrm_DMSS_Access.Enabled = False
            Me.ctl_subfrm_DMSS_Controls.Enabled = False
            Me.ctl_subfrm_DMSS_Revision.Enabled = False
            Me.ctl_subfrm_DMSS_Roles.Enabled = False
            
    End Select
    
End Sub

Here is my New record code I use to launch frm_DMSS:

Private Sub cmd_AddNew_Click()
On Error GoTo Err_cmd_AddNew_Click

    Dim stDocName As String

    stDocName = "frm_DMSS"
    DoCmd.OpenForm stDocName
    DoCmd.GoToRecord acDataForm, stDocName, acNewRec
    Forms!frm_DMSS!txt_OwnerID.Value = Forms!frm_DetectIdleTime!UserID.Value
    Forms!frm_DMSS!DataEntry = True
    Forms!frm_DMSS!AllowAdditions = True
    Forms!frm_DMSS!AllowEdits = True
    Forms!frm_DMSS!Form.Caption = "Create a NEW Document record."

    Forms!frm_DMSS!Date = Date
    Forms!frm_DMSS!txt_Plant.Value = DLookup("[Plant_ID]", "tbl_Users", "[ID] =" & Forms!frm_DetectIdleTime!UserID.Value)
    
Exit_cmd_AddNew_Click:
    Exit Sub

Err_cmd_AddNew_Click:
    MsgBox Err.Description
    Resume Exit_cmd_AddNew_Click
    
End Sub

Many thanks for your time and help.

 

[Kevin_S]

Hey Brew -

why not simplify this and use a query to create the recordset for the form? this way you could incorporate the userid requirement in the query itself thereby each individual user would only see the data that pertains to them...?

hth,
kev

 

[brewpedals]

Thanks for the quick response Kev,

I want all users to view all records, but only edit theirs.

I also found my problem. It was my logic, or the order of the commands in my VBA script.

1. I was launching my form
2. Creating a new record
3. Placing values in the controls
4. Then seting the form properties.

This resulted in my "filtering" the new record. Access responded with the error " You can't go to the specified record. You may be at the end of a recordset." Makes perfect sense - NOW!

When I changed my script to

1. Launch the form
2. Set its properties
3. Create a new record
4. Set the control values

It works perfectly. Hope this help someone else.