Table remains unlock (1 Viewer)

[dianaha]

Hi,

I am a beginner in ACCESS and not a native English speaker, I thank you in advance for your understanding

I am at moment confronted to a weird problem.
After attempting to create a sub to append new field to my table (called here "Table1"), I have realized that the name of this table could not be modify, just after the Form switches in modus Application View (by pressing on F5 key). The message which appears says "Db Engine could not lock the table "Table1" because it is already used by an other person or another process".

This table is used in many Private Subs throught SQL Queries, but :
- those Private Subs are executed when there is an event for a control, not when pressing on F5 key...
- the query are always written in this way (set the recordset, close, set to nothing) :

Dim strSQL as String
Dim strSQL1 as DAO.Recordset
strSQL = "SELECT Field_name FROM Table1"
Set strSQL1 = CurrentDb.OpenRecordset(strSQL)
With strSQL1
If strSQL1.RecordCount > 0 Then
Forms("Name_Form").Controls("list_box_name").RowSource = strSQL 'to show all items of a column/field
Forms("Name_Form").Controls("list_box_name").Visible = True
Else: Forms("Name_Form").Controls("list_box_name").Visible = False
End If
.Close
End With
Set strSQL1 = Nothing

I have searched in every Private Subs, where this table is used, there is nothing in the "Private Sub Form_Open(Cancel As Integer)".
Nobody except me is using at moment this DB.

I really don't understand why when I press to F5, systematically when I want to change the name of Table1 (I have also tried to delete it: impossible), I have this message from ACCESS saying that the table is used somewhere in the VBA code of the Form.

Is there a way to see by which Private Sub, or when or how this Table1 is used?
Is there a way to force the lock of the table1?

I think it is one of the reason my attempt to append a new field failed. But I will maybe speak about this subject in the "Querry" category of the Forum...

I thank you a lot in advance for your help!

 

[arnelgp]

you use Alt-F11 to open the VBA.
F5 is used to Run a sub/function.

you can furthere simplify your code:

Dim strSQL as String
strSQL = "SELECT Field_name FROM Table1"
With Forms("Name_Form").Controls("list_box_name")
    If DCount("1", "Table1")
        .RowSource = strSQL 'to show all items of a column/field
        .Visible = True
    Else
        .Visible = False
    End If
End With

 

[dianaha]

Hi arnelgp,

Thanks for your answer!
I didn't know the function DCount(), which is really useful indeed and more simple.
I am going to modify my subs and see if it solves my problem..

 

[dianaha]

Hi again,

I have tried to do with the function DCount() what I have made before
I want actually to make a dynamic search and make a listbox appear if there is a partial/complete match.
So I need to find all items (in the field of table) which contain the written string (in the textbox of the Form)

I have tried something like that but it doesn't work

DCount(field_name, table_name, "[" & field_name & "] = '*" & Me.Controls(textbox_name).text & "*'")

It return always the value of zero..

 

[Gasman]

All parameters must be enclosed with " ?

 

[dianaha]

Hi Gasman,
Do you speak about the two first parameter inside the brackets? (field_name, table_name)
There have actually been already determined previously as strings

 

[Gasman]

Hi Gasman,
Do you speak about the two first parameter inside the brackets? (field_name, table_name)
There have actually been already determined previously as strings

Ah, I thought that was just your example?
Put the criteria into yet another string, debug.print that, and when correct use that in the function.

I would have thought you would need to concatenate Me.Controls and TexboxName, but not something I have ever done?, however I always check my criteria.

Edit: Seems it will work if you remove the .Text ?

 

[dianaha]

I will see documentation about debug.print, but actually I always test my Private Subs by pressing on F5 key when I have my opended Form.

I have managed to make the dynamic search, here the solution (use of "LIKE" instead of the equal sign "="):

DCount(field_name, table_name, "[" & field_name & "] LIKE '*" & Me.Controls(textbox_name).text & "*'")

 

[Gasman]

Yes, sorry, I should have spotted that. I tried with an = for a textbox value and that worked.
F5 will not help that much unless you have breakpoints? I use F8 (line by line/or between breakpoints), however to my mind a debug.print is much easier, and can comment out at a later date.?

Good luck with the rest of your project.