Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    18

    Unanswered: Error Setting Form Recordset

    I have a form in an Access 2007 MDB that locks up Access when I reset the form's recordset a second time. It works fine when it gets set initially in a control's afterupdate event. When I update that text box a second time I get an error.

    Run-Time Error '-2147417848 (80010108)
    Method 'RecordSet' of object '_Form_frmHistoryDtl' failed

    Code:
    Set Me![frmHistoryDtl].Form.Recordset = rst
    Code:
    Dim rst As DAO.Recordset
    Dim strSql As String
    Set rst = Nothing
    
    strSql = "SELECT Inactive, ID, Serial, DateEntered, ActionCode, ResultCode, DefectCode, RefCode, LocCode, ReworkCode, Comment, ID AS IDSort " & _
                "FROM History " & _
                "WHERE Serial = " & FixUp(strSerial) & ";"
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    Set Me![frmHistoryDtl].Form.Recordset = rst
    I saw the same issue posted here with no solution.

    I am using Access 12.0.6606.1000

    Any insight would be appreciated.

    Thanks,
    Daniel

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As you noticed, it's not always safe to directly manipulate de Recordset object of a form. A bound form includes 3 data objects: the Recordset, the RecordsetClone and the Dynaset. They work together and from these, only the RecordsetClone can be accessed safely in any circumstances.

    If you want to modify the data source of a form, pass its name or a string containing the SQL of a SELECT query to the RecordSource property of that form:
    Code:
    Dim strSql As String
    
    strSql = "SELECT Inactive, ID, Serial, DateEntered, ActionCode, ResultCode, DefectCode, RefCode, LocCode, ReworkCode, Comment, ID AS IDSort " & _
                "FROM History " & _
                "WHERE Serial = " & FixUp(strSerial) & ";"
    
    Me![frmHistoryDtl].Form.RecordSource = strSql
    Have a nice day!

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am obviously missing something, but why don't you just assign the strSql string to the forms Record Source (and requery if required)?!


    MTB

  4. #4
    Join Date
    Nov 2011
    Posts
    18
    There's no reason I would not be happy using recordsource rather than recordset. The thing that troubles me is that is was working for a time while I was using recordset. It makes me feel like I don't really understand the underlying problem.

    I made the changes suggested and I got no errors but I didn't get any records in the subform either.

    Code:
    Me("frmHistoryDtl").Form.RecordSource = strSql
    Me("frmHistoryDtl")("txtDateEntered").ControlSource = "DateEntered"
    Me("frmHistoryDtl").Form.Requery
    I ran the output of Debug.print recordsource through the query designer and I got 15 records.

    Thanks

  5. #5
    Join Date
    Nov 2011
    Posts
    18
    DataEntry Property was set to yes. I can see the records now.

    Thanks.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Note: For better understanding the Form Recordset property, see: Recordset Property*[Access 2007 Developer Reference]
    Have a nice day!

Posting Permissions

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