Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    184

    Unanswered: Why can't I EDIT Anymore? What gives?

    I created an access form with one of my tables, when I use the navigation buttons at the bottom of the form I can navigate, edit, add without any problems.

    Here is where the problem starts.

    I have made my own navigation buttons, add new etc... and set them to form's DAO recordset

    Now if I click on any of these buttons for example I click on move next it moves to the next record but I can no longer edit the records if I navigate now with the built-in form control I can no longer do any edits.

    Any ideas?

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Why can't I EDIT Anymore? What gives?

    Originally posted by rami.haddad
    I created an access form with one of my tables, when I use the navigation buttons at the bottom of the form I can navigate, edit, add without any problems.

    Here is where the problem starts.

    I have made my own navigation buttons, add new etc... and set them to form's DAO recordset

    Now if I click on any of these buttons for example I click on move next it moves to the next record but I can no longer edit the records if I navigate now with the built-in form control I can no longer do any edits.

    Any ideas?
    I'm guessing it has to do with the recordset assignment somehow.

    I'm curious as to why you are using the forms recordset to navigate instead of the DoCmd.GoToRecord method.

    Unless you are using self disabling navigation buttons that need to be aware of the recordsets position......
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I dont know why you cant edit... could you post the code behind your navigation buttons?

    I have made my own navigation buttons before as well so if I see your code maybe I can suggest something or show you mine so you can try and work from there. I havent messed around with DAO or ADO or any of that stuff so I dont know how it would relate to that. I am use VBA.


    Hope I can help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Jan 2004
    Posts
    184

    Re: Why can't I EDIT Anymore? What gives?

    Thank you for your replies. The reason I am using the forms recordset is to try to make portable code.

    To assign the recordset I am writing

    Set MyObject.Recordset = Me.Recordset

    Where MyObject uses my own buttons, where navigation works and everything but I cannot edit (I cannot even type anything into the text boxes at all they lock up, but until I press any of the buttons in my object I can edit just fine).

    I appreciate any help you guys can give.

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Cool Re: Why can't I EDIT Anymore? What gives?

    Originally posted by rami.haddad
    Thank you for your replies. The reason I am using the forms recordset is to try to make portable code.

    To assign the recordset I am writing

    Set MyObject.Recordset = Me.Recordset

    Where MyObject uses my own buttons, where navigation works and everything but I cannot edit (I cannot even type anything into the text boxes at all they lock up, but until I press any of the buttons in my object I can edit just fine).

    I appreciate any help you guys can give.
    I'm just curious about your recordset assignment. I usually declare a recordset object and then Set MyRecordsetObject = Me.Recordset whereas you have set the objects recordset "MyObject.Recordset = Me.Recordset".

    Be interested to see your complete code.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Not sure...

    I am not sure that this would help but I use...

    Code:
    Private Sub cmd90_Click()
    On Error GoTo Err_cmd90_Click
    'Next Record
    
        DoCmd.GoToRecord , , acNext
        cmd91.Enabled = True 'Enable Previous record button
    
    Exit_cmd90_Click:
        Exit Sub
        
    Err_cmd90_Click:
        MsgBox Err.Description
        Resume Exit_cmd90_Click
    End Sub
    
    Private Sub cmd91_click()
    On Error GoTo Err_cmd91_Click
    'Previous Record
    
        DoCmd.GoToRecord , , acPrevious
        cmd90.Enabled = True 'Enable Next Record Button
    
    Exit_cmd91_Click:
        Exit Sub
    
    Err_cmd91_Click:
        MsgBox Err.Description
        Resume Exit_cmd91_Click
    End Sub
    
    Private Sub cmd92_Click()
    'Last Record
    
        DoCmd.GoToRecord , , acLast
        cmd90.Enabled = False 'Disable Next Record
        cmd91.Enabled = True  'Enable Previous Record
    
    End Sub
    
    Private Sub cmd93_Click()
    'First Record
    
        DoCmd.GoToRecord , , acFirst
        cmd91.Enabled = False 'Disable Previous Record
        cmd90.Enabled = True 'Enable Next Record
    
    End Sub
    I hope that will help you in your journey. I am not exactly sure what you are needing past the ability to move in your own records. (You can put other things in those buttons that you need to check for every record. I have had to enable check boxes or disable based on certain criteria.)

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Jan 2004
    Posts
    184

    Re: Not sure...

    Thank you again guys.

    I am trying to use DAO

    Here is the code I use to assign the recordset

    dim rs as DAO.Recordset

    Set rsSCIROCCO = Me.Recordset

    'Me.Recordset is the form's recordset

    At this point I can edit no problem

    Once I click on one of the buttons with code that follows I cannot edit anymore. Here is some code I use to navigate

    rs.MoveNext
    If rs.EOF Then
    rs.MoveLast
    End If

    Any ideas guys? And thank you again JSThePatriot and basicmek
    In abundance of water only the fool is thirsty. Bob Marley.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Without doing any testing, my fist thought is that you're moving your recordset, but not the form's or vice versa.

    You're statement: Set rsSCIROCCO = Me.Recordset creates a second, duplicate, independant recordset object (similar to recordset clone). Moving one does not move the other and Access will fail safe to "locked" when it gets confused, lost, etc.

    I don't know all the details, but I think I would make the form unbound all together to avoid confusion (yours and the computers). You can use "openrecordset" declarations and still be portable, and write a little sub procedures with loops to move the data onto and off of the form.

    Hope this helps

  9. #9
    Join Date
    Jan 2004
    Posts
    184
    Thank you very much tcace for that answer. I had thought that when I set it to an existing recordset all it did was create a pointer to the second recordset and not a clone.

    So for me to create an unbound form. I would create a form, open a DAO recordset and then set the ControlSource of the objects to that recordset like follows?

    Dim rs as DAO.Recordset

    set rs = new DAO.Recordset

    Set rs = database.OpenRecordset(strSQL, RecordsetType, RecordsetOptions, LockType)

    set TextBox.ControlSource = rs

    Thanks again tcace you have really helped me.
    In abundance of water only the fool is thirsty. Bob Marley.

  10. #10
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Why can't I EDIT Anymore? What gives?

    Originally posted by rami.haddad
    I created an access form with one of my tables, when I use the navigation buttons at the bottom of the form I can navigate, edit, add without any problems.

    Here is where the problem starts.

    I have made my own navigation buttons, add new etc... and set them to form's DAO recordset

    Now if I click on any of these buttons for example I click on move next it moves to the next record but I can no longer edit the records if I navigate now with the built-in form control I can no longer do any edits.

    Any ideas?
    Just in case your interested in the Recordset vs RecordsetClone operations. This is from the help files.

    The Recordset property returns the recordset object that provides the data being browsed in a form, report, list box control, or combo box control. If a form is based on a query, for example, referring to the Recordset property is the equivalent of cloning a Recordset object by using the same query. However, unlike using the RecordsetClone property, changing which record is current in the recordset returned by the form's Recordset property also sets the current record of the form.

    I think this is for Access 2K and up.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  11. #11
    Join Date
    Jan 2004
    Posts
    184

    Re: Why can't I EDIT Anymore? What gives?

    Thank you basicmek, that is exactly what happens I am able to navigate using my recordset and I see the form's recordset's pointer move also, but like I mentionned before that is the moment I lose the ability to edit.

    If I were to make a unbound form as tcace suggested would I go about it in this manner?

    Dim rs as DAO.Recordset

    set rs = new DAO.Recordset

    Set rs = database.OpenRecordset(strSQL, RecordsetType, RecordsetOptions, LockType)

    set TextBox.ControlSource = rs

    Thank you.

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Two things:

    1) I use Access 97, and when they moved to 2000 they made some changes to how recordset objects function (DAO vs. ADO - whatever that means!) so you'll have to take my advice with a grain of salt if using a newer version!

    2) To answer your question, I have not used the method of assigning the rs as the control source - I leave all the controls unbound and manage the data through procedures.

    For example:
    When a user opens a specific record, the event calls a procedure that opens a recordset, populates the data on the form, and then close the recordset. I keep a hidden field (also unbound) that keeps track of the record's index (key) value.

    In Access 97, if the objects are unbound, the "dirty" method of the form does not reliably catch changes. There are 2 ways to handle updating changes. You can "fail to safe" and automatically write the data back to the db when changing records, or use an "afterupdate" event on each control that changes a boolean control to "True" when a change was made and only update when you need to. Circumstances will dictate which method to use - my software has to be FDA CFR Part 11 compliant, so I use the more complicated method so that I can enforce audit trails.

    So your basic steps of operation on navigation are:
    1) Check to see if the data changed
    2) Update the data on the screen back to the database (if required or always you decide)
    3) Change to the requested record (Previous, Next, First, Last, etc.)
    4) Update the controls with the new data

    I typically handle "New Records" by creating the record in the table, updating the RS and then treating it as an existing record (becasue now it is) and navigate to it as normal, but sometimes I use a flag and don't touch the table until the user is ready to commit the record.

    All this can be very tidy and efficient if you use Sub procedures.

    This method adds some benefits:
    - you become immune to the "Wheel Mouse" which changes the current record in bound forms (a deadly issue when you're trying to enforce audit trails)
    - you have full control over the data in both directions
    - you reduce network traffic and the strain on Jet because your "hits" to the database are very brief and only when required (not constant as with bound forms).

    Some drawbacks are:
    - it requires a bit of debugging and testing to get it just right (make sure you test it a lot before releasing it or your users will show up at your door with daggers in hand)
    - there are some "recordlocking" things that come into play - namely the record is NOT locked while it's on your user's screen (so I add a couple of fields to the table to flag when a record is "checked out" by another user)
    - the other recordlock issue (at least in 97) is that using recordsets to edit/add data while another user is in using a bound form causes a "phantom record lock" - the user with the bound form can not get out until the user with the recordset completely exits the application. There is a thread on that topic in this forum.

    Good luck; if you like coding, this will be fun!

Posting Permissions

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