none
Table is still locked after the form using the table in the data query has been closed RRS feed

  • Question

  • I have a form that shows data from a table called "Beschrijvingen". I want to change the default value in some of the fields in the table, based on data in the present record.

    I call a public sub in an external module. Inthe sub I store the data from the record, then close the form.

    Yet I am getting Error 3422: Can not change the structure of the table. Table is opened by an other user.

    To make sure I have closed the form I use:

    Alweer:
        DoCmd.Close acForm, "Fotoalbummaken"
        If IsLoaded("Fotoalbummaken") Then
            MsgBox "nog niet gesloten"
            GoTo Alweer
        Else
            MsgBox "gesloten"
        End If
    and am getting the message "gesloten". And indeed the form disappeared

    The error occurs at the statement"

             CurrentDb.TableDefs("Omschrijvingen").Fields("Beschrijving").DefaultValue = strBeschrijving

    What is my mistake?

    Thanks for your help!

    Thursday, September 3, 2015 12:35 PM

Answers

All replies

  • Hi. Just a guess, but if you're executing the code behind the same form, then that might be the problem. The form is not completely closed until the is finished.
    Thursday, September 3, 2015 1:13 PM
  • Thanks DB guy,

    No the code is running in a module that I called Global. Your response brought me on an idea and I moved the sub into a new classmodule.

    However, the result is the same...

    Thursday, September 3, 2015 1:46 PM
  • Hi. Sorry to hear that. Maybe one way to find out where the hang up is coming from is to step through the code. Have you tried doing that? Just curious...
    Thursday, September 3, 2015 4:17 PM
  • Hi DB guy,

    I ran against an other issue: It seems to be impossible to operate on the attributes of the fields.

    I will re-design by making a table with the default values and insert the values at starting a new record.

    Thanks for thinking with me

    Thursday, September 3, 2015 6:12 PM
  • Hi. You're welcome. Sorry I couldn't help. Good luck with your project.
    Thursday, September 3, 2015 6:23 PM
  • Thanks
    Thursday, September 3, 2015 6:41 PM
  • I have a form that shows data from a table called "Beschrijvingen". I want to change the default value in some of the fields in the table, based on data in the present record.

    I call a public sub in an external module. Inthe sub I store the data from the record, then close the form.

    Yet I am getting Error 3422: Can not change the structure of the table. Table is opened by an other user.

    To make sure I have closed the form I use:

    Alweer:
        DoCmd.Close acForm, "Fotoalbummaken"
        If IsLoaded("Fotoalbummaken") Then
            MsgBox "nog niet gesloten"
            GoTo Alweer
        Else
            MsgBox "gesloten"
        End If
    and am getting the message "gesloten". And indeed the form disappeared

    The error occurs at the statement"

             CurrentDb.TableDefs("Omschrijvingen").Fields("Beschrijving").DefaultValue = strBeschrijving

    What is my mistake?

    Could it be that some other form or query is using the table?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, September 4, 2015 4:28 AM
  • I ran against an other issue: It seems to be impossible to operate on the attributes of the fields.

    It is possible to do this, in principle.  I tested, and I successfully set the DefaultValue property of a field in a table with this statement:

        CurrentDb.TableDefs("Books").Fields("Publisher").DefaultValue = "Sybex"

    So there must be some particular unknown factor that is preventing you from doing this.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, September 4, 2015 4:30 AM
  • I ran against an other issue: It seems to be impossible to operate on the attributes of the fields.

    Hi Vic,

    As far as my experience goes, you cannot change Field properties on linked tables. If the table is a linked table, then you can set a reference to the database where the table is located, and use something like:

            DataDb.TableDefs("Omschrijvingen").Fields("Beschrijving").DefaultValue = strBeschrijving

    Groeten, Imb.

    Friday, September 4, 2015 6:14 AM
  • Ichecked, but could not find one. I redesigned the project by making a table for the defaults and entering them when a new record is selected.

    Thanks

    Saturday, September 5, 2015 7:31 AM
  • Thanks Dirk,

    I redesigned the project.

    Saturday, September 5, 2015 7:36 AM
  • Thanks Imb,

    I changed the design, so the problem is solved. But it was not a linked table....

    Saturday, September 5, 2015 7:37 AM