none
Error 3078 on OpenRecordset

    Question

  • I have three tables: Control, Formula, and Details.  I also have one form built around the Formula table.  There is a relationship between Form and Detail on the field [Formula ID].  I'm having trouble with the line marked (**).

    I'm doing three things in the code below.  The first section, opens the Control table which has one record that keeps track of the last assigned formula number in the database.  I open the Control table, increase the formula number tracking field, then assign it to a global variable (FormulaID) which is used in a variety of capacities.

    The second part, starting with the problem line (**), is supposed to open the Details table, add a record, and save the newly assigned Formula ID. Other fields in the Details table will be entered at a later point by the user.

    The third part simply sets up a filter for the form using the global variable.

    The first and third parts work like a charm. 

    The code is generating error 3078 saying the object Details isn't found.  But it's there.  I've tried with and without the brackets, but that doesn't change anything.  I've also tried dbOpenTable, but that just generate a different error (3011) with essentially the same message ... can't find Details.

    I'm sure this is simple, but my eyes just aren't seeing the problem.

    Any thoughts?

    THANK YOU!

     

    Private Sub Form_Open(Cancel As Integer)
      Dim db As DAO.Database
      Dim rst As DAO.Recordset
      Dim strSQL As String
    
      Set db = CurrentDb()
      strSQL = "SELECT FFORMID FROM Control"
      Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      With rst
        If .RecordCount > 0 Then
          .MoveFirst
          .Edit
          !FFORMID = !FFORMID + 1
          FormulaID = !FFORMID
          .Update
        End If
        .Close
      End With
      
    **  Set rst = db.OpenRecordset("SELECT * from [Details]", dbOpenDynaset)
      
      With rst
        .AddNew
        ![Formula ID] = FormulaID
        .Update
      End With
      
      Me.Filter = "[Formula ID]=" & FormulaID
    End Sub


    Tuesday, July 12, 2011 3:40 PM

Answers

  • Your code looks fine to me.

    Take a really close look at how the table name Details is spelled. Could it have a space in front of it? Try renaming it, compacting and the naming it back to the old name and compacting again.

    If that does not solve the problem set rst = Nothing before you set it the second time.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    • Proposed as answer by Macy Dong Monday, July 18, 2011 1:17 AM
    • Marked as answer by Macy Dong Monday, July 18, 2011 1:33 AM
    Tuesday, July 12, 2011 3:51 PM

All replies

  • Is it possible you have the controls in the header instead of the details section of the form?
    Tuesday, July 12, 2011 3:49 PM
  • Nope, nothing there or in the footer.
    Tuesday, July 12, 2011 3:51 PM
  • Your code looks fine to me.

    Take a really close look at how the table name Details is spelled. Could it have a space in front of it? Try renaming it, compacting and the naming it back to the old name and compacting again.

    If that does not solve the problem set rst = Nothing before you set it the second time.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    • Proposed as answer by Macy Dong Monday, July 18, 2011 1:17 AM
    • Marked as answer by Macy Dong Monday, July 18, 2011 1:33 AM
    Tuesday, July 12, 2011 3:51 PM
  • Ok, I feel really foolish now. :-/

    I had the navigation bar on the left set to "custom" view and "Details" was a short cut to the original table, which I had named something else.  Once I renamed the TABLE instead of the SHORTCUT it works fine.  Shesh!

    Almost 30 years of programming and its STILL the little silly things that trip me up.

    THANKS!!!

    Tuesday, July 12, 2011 4:02 PM
  • That's why I never use captions and such with data objects. I, too, have pounded my head on the wall until I realized what I did was think the caption was actually the name.

    I've only got 15 years behind me so I guess I can count on at least another 15 of stupid mistakes, huh?


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    Tuesday, July 12, 2011 4:12 PM
  • This is really inefficient code and could likely be replaced with a single SQL INSERT statement:

    INSERT INTO DetailsTable ([FormulaID])
    SELECT Control.FFORMID
    FROM Control
    

    Now, you still need to increment the ID there, so before calling that you'd want to execute this SQL:

    UPDATE Control
    SET FFORMID = FFORMID + 1
    

    But really, why do you need a table for this? Why not just do this:

    INSERT INTO DetailsTable ([FormulaID])
    SELECT Max(DetailsTable.FormulaID) + 1
    FROM DetailsTable
    

    The only reason to use a table would be if you've got multi-user issues, but then you'd want to lock that table during the time you're incrementing the value, but you're not doing that, so it doesn't seem to me that you are too concerned about multi-user issues, so the Max() + 1 ought to work just fine.


    David W. Fenton
    David Fenton Associates


    Wednesday, July 13, 2011 9:12 PM