Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    18

    Question Unanswered: Is a Drop Down Box's Source Table Locked?

    Hi all,

    In an Access database(mdb), a dropdown box is used on a form. This dropdown box's record source is a query that pulls distinct occurences of field1 on Table A.

    Is the source table (Table A) for that dropdown's query locked while users are in the form? I don't know if this matters but the box's LimitToList Property is set to yes.

    Thanks in advance!
    -gcoding

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It shouldn't be; are you seeing something that makes you think it is?
    Paul

  3. #3
    Join Date
    Feb 2009
    Posts
    18
    This is a multi-user db. TableA is a local Table in Access.

    While the user is filling out a form, he/she picks one of the options from the drop down box. However, that's the only thing tied back to that table. There are no bound fields that reference this table.

    While a user was still in the form, I tried to update the underlying table (Table A). When doing this it gives me the following error message: "You tried to lock table 'TableA' while opening it, but the table cannot be locked because it is currently in use. Wait a moment, and then try the operation again.

    I thought this may be just a coincidence, so I tried to do the data update once the user was out of the other user was out of the database completely. This time, the data update did work.

    Is there something else that I might be missing? I'm stuck on this one and would welcome any suggestions.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample that does this? I just tested on a form with a combo, and I was able to go in and edit the table with the form open (I even edited the record selected in the combo). I tried with both Access and SQL Server tables as the row source.
    Paul

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    LimitToList just stops the user entering data which is not already in the recordsource.

    Is the combobox recordsource just Table A itself or a query based on the table?

    If it is just based on the table,. Try using a query based on [Table A] instead.
    (This allows you to sort the options too).

    Say:

    SELECT yourcolumn FROM [TABLE A] ORDER BY yourcolumn;

    OR if you want more than one column in the como...

    SELECT yourcolumn, yourothercolumn FROM [TABLE A] ORDER BY yourcolumn;

    If the first item in the combo is the primary key of Table A and you want to hide it, change the number of columns in the combo control and make the size of the first column 0cm to hide it.

    Hope this helps.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Feb 2009
    Posts
    18
    garethdart:
    Unfortunately, I'm already using the query:

    The dropdown's rowsource is a query that contains the following:
    SELECT DISTINCT [TableA].field1
    FROM [TableA];

    pbaldy: I've tried the table update while in the form and it works sometimes. However, it appears that it will not allow me to update data for TableA when the user is in the process of entering information into or choosing information from the dropdown within the form.

    What kind of sample do you need? The code behind the controls?

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Pretty much anything that will let me recreate the problem, since I haven't been able to.
    Paul

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    A .zip version of your database would be nice - I think I know what's going on here.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Feb 2009
    Posts
    18
    The attached database is the best I can do at sending a sample. I've added correct field names this time. TableA (as described in my previous posts) is really the [Work Orders] table and field1 is really the [ORDNBR] field.

    The steps are as follows:
    1) The user clicks on "Load Work Orders" to load in the current Work Orders.
    2) After the load process is complete, the user clicks on "Input Sheet" to being entering in Order Information. The first step is to select a Work Order from the dropdown box.

    It appears that if one user is entering in a new sheet and is choosing the work order while another user tries to "Load Work Orders", an error will appear saying that the database engine could not lock the table table because it is already in use.

    On the database you'll notice a table named IMPORTED_WOs where the [Work Orders] information is obtained. In the real database, this is a linked table to a DB2 database. However, I have imported some sample data into a local table to give you some test data.

    I hope this helps! Let me know if you need any additional information.
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2009
    Posts
    18
    Any takers? I'm open to any suggestions.

Posting Permissions

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