Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: "subform datasheet view will only allow me to edit when first opened"

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "subform datasheet view will only allow me to edit when first opened"

    I have added a subform to the main form so I could have some controls on the main form and the datasheet view on the subform. When I first open the main form I can edit in the subform datasheet without a problem. But if I execute the combo box "cboJobNumber" from the main form to search for a record it works fine. Once the record is found and I try to edit it I get an error: (Update or CancelUpdate without AddNew or Edit.) I have tried everything I can think of to fix it. I added Requery after update but nothing seems to work. The only way it will let me edit a record is to open and close it again.

    I have all the property sheet correct with allow deletions, edits and filters set to Yes

    I have it both forms linked together in the Master and Child fields by the primary key "tagID"

    Any idea's?

    Thanks
    Tony

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,702
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    could be a number of things, but I'd start with asking if the combo is bound or just has a row source. If you're using it to filter subform records but you have it bound, this is incorrect. Once you make a selection you have put one form or the other (perhaps both) in edit mode. If the record selectors are visible in the form(s) see if the pencil appears. That would mean the form is in edit mode.

    If that's not it then it also reads like you have code behind the form and are using a recordset. Seems like you have attempted to Update or Cancel without putting the recordset into AddNew or Edit mode. In that case, your code might shed some light on the issue.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    This is an Emulated Split Form. The Parent and Child supposedly don't need to be linked. But I have not got it to work with them linked or without.


    Recordset Code:

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Handler
    'This is the main form open event handler

    Dim strSQL As String

    strSQL = "SELECT tblTag FROM tblTag;"

    Me.RecordSource = strSQL 'set the recordsource of the main form
    Me.subfrmTagentry.SourceObject = "subfrmTagEntry" 'load datasheet subform w/ blank RecordSource
    Set Me.subfrmTagEntry.Form.Recordset = Me.Recordset 'set subform recordset to same object as main form's

    Exit_Handler:
    Exit Sub

    Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description & " in Form_Open procedure"

    End Sub

    ***************************************************************************
    Combo Box Code:

    'Combobox to filter records by JobNumber
    Private Sub cboFilterByJobNumber_AfterUpdate()
    Me.Filter = "JobNumber = " & Forms!frmTagEntry!cboFilterByJobNumber & ""
    Me.FilterOn = True
    Set Me.subfrmTagEntry.Form.Recordset = Me.Recordset
    cmdClearFilter.Enabled = True
    cmdClearFilter.SetFocus
    End Sub



    Last edited by bama4954; Oct 8th, 2019 at 07:41 PM.

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,702
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    Please post more than a few lines of code within code tags (# on forum toolbar).
    I would have guessed that it's not possible to have a table and a field with the same name but I guess I would have been wrong. Not really a good idea. Nor do I usually have a subform and subform control with the same name.

    Have to admit I've seen the emulated split form referred to somewhere but never had the desire to try the code. One thing I suspect would be an issue is that data isn't present in the form in the Open event; only in the Load event. Are you sure you can pass a recordset object to another form as its recordset object? If you set the source on one form, why not just set the same source for the other form instead?

    I don't see why you have quotes here when it is supposedly a number
    Forms!frmTagEntry!cboFilterByJobNumber & ""

    Unfortunately you can't upload files here otherwise I might be able to find something specific.
    BTW, I asked a question in the very first sentence of my reply but you have not shed any light on that. Is the combo bound or not?

  5. #5
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    No it is not bound. I think I am going to just throw the towel in and use the regular method of having a main form and sub form done the old fashion way. The whole reason is I did not want to link the master and child tables to the sub form because it would only show one record instead of all the records. I have learned that the master and child fields do not have to be linked for the database to work properly as long as the unerlying codes and queries supports it.

    But the
    Emulated Split Form does indeed work flawlessly on the file I have that someone else designed. I was just trying to reproduce it to work on the database I am developing.

    "Thanks you very much for your help"

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,702
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    it would only show one record instead of all the records.
    2 potential reasons I can think of for that - the recordsource only returns 1 record (because of query design) or when it's a table and not a query, the filter causes only one record to be returned. To validate the latter, create a query that applies the same criteria as your filter and see if it returns only one record. If so, then only one record satisfies the query which means only one can satisfy the filter. The fact that it doesn't work as expected in this form version may have nothing to do with form settings.

  7. #7
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    I understand what you are saying. The filter and the query are both correct. The problem is when I link the master and child to say the PK or JobNumber. It only returns one job number or one record. The user wants to be able to see all of the records on the subform datasheet and be able to use a combo box to filter the different job numbers, and be able to clear them to see all records again. The Emulated Split Form does exactly that. It acts like a form that is split but is not. I read an article today that said if you leave the main form with the row sorce blank and put all of the controls into the form header including the combo box filter that it would be able to filter whatever field criteria and see also see all records by clearing the filter. I have this in a subform now that they can do exactly that by utilizing the built in sort button. I just thought it would be cool to have a nice filter instead.
    Last edited by bama4954; Oct 9th, 2019 at 09:39 PM.

  8. #8
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,702
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    Sorry I couldn't be of more help. I figured that since you had a prior example that did work and you were trying to replicate it, there had to be something wrong with the filtering. However, if you've now moved it to a subform and still can't filter it, then I don't know what else to think besides the filter doing exactly what it's supposed to but that isn't what you expect. Filtering a subform from a main form is common. If using a combo, one has to be careful that one uses values from the correct column.
    Too bad you can't upload files here...

  9. #9
    Board Regular
    Join Date
    May 2013
    Posts
    654
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    removed
    Last edited by welshgasman; Oct 10th, 2019 at 03:29 AM.
    Office 2007
    Access novice. Sometimes trying to give something back

  10. #10
    Board Regular
    Join Date
    May 2013
    Posts
    654
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "subform datasheet view will only allow me to edit when first opened"

    You just need a combo in the main form and a subform for that.

    I use the emaulated split form, but only as a replacement for a split form and make sure the datasheet/continous is read only. I use it more for navigation.

    Quote Originally Posted by bama4954 View Post
    I understand what you are saying. The filter and the query are both correct. The problem is when I link the master and child to say the PK or JobNumber. It only returns one job number or one record. The user wants to be able to see all of the records on the subform datasheet and be able to use a combo box to filter the different job numbers, and be able to clear them to see all records again. The Emulated Split Form does exactly that. It acts like a form that is split but is not. I read an article today that said if you leave the main form with the row sorce blank and put all of the controls into the form header including the combo box filter that it would be able to filter whatever field criteria and see also see all records by clearing the filter. I have this in a subform now that they can do exactly that by utilizing the built in sort button. I just thought it would be cool to have a nice filter instead.
    Office 2007
    Access novice. Sometimes trying to give something back

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •