Exclusive Access - maddening code

VincilLabs

Registered User.
Local time
Today, 12:18
Joined
May 11, 2011
Messages
13
:banghead:

I'm coding on a locally stored db. I have a code snippet that's locking my database, but it should not be.

Code: --Gets field names from the selected table to put into comboboxes on my form.

Code:
    Dim masterTable As DAO.TableDef, thisDatabase As DAO.Database
    Dim fieldList As String, thisField As Field
    Dim controlBox As Control
 
    [COLOR=seagreen]'Set thisDatabase to current Database[/COLOR]
    Set thisDatabase = CurrentDb
 
    [COLOR=seagreen]'Set masterTable to the table definition of the table name in xTableNameCombo.value[/COLOR]
    Set masterTable = thisDatabase.TableDefs("[" & Me.xTableCombo.value & "]")
    [COLOR=seagreen]'fieldList will populate the field combo boxes.  Here we set a default value.[/COLOR]
    fieldList = "Not Selected"
 
    [COLOR=seagreen]'Loop through all of the fields and add the field name to the fieldList variable.[/COLOR]
    For Each thisField In masterTable.Fields
        fieldList = fieldList & ";" & thisField.name
    Next thisField
 
    For Each controlBox In Me.Controls
        If (controlBox.name <> "xTableCombo" And Right(controlBox.name, 5) = "Combo") Then
            Me.Controls(controlBox.name).RowSourceType = "Value List"
            Me.Controls(controlBox.name).RowSource = fieldList
            Me.Controls(controlBox.name).value = "Not Selected"
        End If
    Next controlBox
 
    [COLOR=seagreen]'Cleanup[/COLOR]
    thisDatabase.Close
    CurrentDb.Close
    Set thisDatabase = Nothing
    Set masterTable = Nothing

The code does its job, but when I try to write more code and save it I get this message:

"Microsoft Access can't save design changes or save a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file."

To be clear - this is a local database and only I have it open.
This error only happens after the above code executes and I try to save any changes to the database.

Been working on this for a couple hours - I have to run the code, test, close the db and open the db every time I change the value in that combo.
:banghead:
 
Are you aware of "Field List" as a built-in legit option for row source type?
 
Well that would simplify things wouldn't it. I feel like there was a reason I didn't use that in the first place, but I'll see if I can make that work and let you know.

Thanks man!
 
Now I remember: Here's the issue with using "Field List"

I need this database to be idiot aware (nothing is idiot proof)
If you notice - my code puts a dummy default value in the field list. So if the user accidently selects a field - they can change the value back to "Not Selected"

When we use "Field List" it ONLY has values from field list.

yes - I can set a default
yes - I can force a starting value

But if they select something and then try to revert - the "Not Selected" option is no longer there... :eek:

I don't see a way around this without using some sort of code snippit as above.
 
You could make a "CLEAR" button to set it to Null, and have @;"Not selected" as format for the combobox display field

Otherwise: the clean-up rule normally is to close what you open, but you have neither opened thisDatabase nor Currentdb!
 
Maddening yeah? :p

I'm assuming that either pulling the tableDef or touching the controls is putting some kind of design lock (not a record lock) on the database... but...like...why? how? -- stupid Microsoft...

:banghead:

The ().close are in there to try to fix this...they weren't in there when I started and made 0 difference (well, I mean, obviously)

Oh well - I'm going to create a new form in a new database and do the entire thing from scratch.

!!!!

If you think of anything let me know and I will let you know if I somehow make it work.

Thank you!
 

Users who are viewing this thread

Back
Top Bottom