Code to Break a Lock to a Table 1

[Bry12345]

I have an application that in a simple sense keeps track of locations at which items are stored.

The data entry form has 5 combo boxes designed to give the user a list of 'available' storage locations. When the 'GotFocus' event of each of these combo boxes fires, code runs which builds a one field table listing the available locations. A SELECT statement bound to the control then allows the control to only list for selection those areas which are 'available'.

This works great when the first combo box gets the focus. My problem is that while the form remains open, I get an error (Runtime Error 3211 - the database engine could not lock the table which the SELECT statement queries to show the available locations).

What I need, apparently, is a method to 'unlock' the table after the combo box executes it's SELECT statement. I've tried saving the record as part of this process, but that didn't help.

Thanks! - - - -

Bryan

 

[chrissie1]

please give your code

"What a wonderfull world" - Louis armstrong

 

[Bry12345]

Hi Chrissie . .

Here you go . .

I thought that DoCmd.Save would solve the problem . . I don't think it is firing, though, because I have the form's 'Before Update' trapped, and I don't see the form being saved.

- - -
Private Sub Location1_GotFocus()

'This is identical for all 5 combo boxes
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT qryLocation1.Location1 AS Location INTO tblCombinedLocations FROM qryLocation1"
DoCmd.RunSQL "INSERT INTO tblCombinedLocations ( Location ) SELECT qryLocation2.Location2 AS Location FROM qryLocation2"
DoCmd.RunSQL "INSERT INTO tblCombinedLocations ( Location ) SELECT qryLocation3.Location3 AS Location FROM qryLocation3"
DoCmd.RunSQL "INSERT INTO tblCombinedLocations ( Location ) SELECT qryLocation4.Location4 AS Location FROM qryLocation4"
DoCmd.RunSQL "INSERT INTO tblCombinedLocations ( Location ) SELECT qryLocation5.Location5 AS Location FROM qryLocation5"
'Next line causes error . .tblAvailableLocations is locked from previous query
DoCmd.RunSQL "SELECT tblLocation.Location INTO tblAvailableLocations FROM tblLocation LEFT JOIN tblCombinedLocations ON tblLocation.Location=tblCombinedLocations.Location WHERE (((tblCombinedLocations.Location) Is Null))"
DoCmd.SetWarnings True

End Sub

- - -
Private Sub Location1_LostFocus()

'This is identical for all 5 combo boxes
DoCmd.Save acForm, "frmTest"

End Sub
- - -

Row Source:

SELECT tblAvailableLocations.Location FROM tblAvailableLocations;
- - -

Thanks! - - - -

Bryan

 

[chrissie1]

i think i now the problem

normally you can only do a select into
into a new table not into one that already exists
"What a wonderfull world" - Louis armstrong

 

[Bry12345]

Thanks, but I don't think that's it. The set warnings to FALSE lets the table get deleted automatically.
I have approximately the same code in a module, and I can continually run it with a SELECT INTO without deleted the 'Selected into' table first.

Just to double check this, I remarked out the SET WARNINGS line, which gave me the Warning prompts. I got to the point where I was warned that the target table would be deleted. The routine errored after I said yes to 'delete the table'. Same thing, the table had been locked by the previous query and the table couldn't be deleted.

If I have to, I'll settle for running the routine once when I open the form. Worst case, each combo box won't update individually, just when the form opens.

Thx.

B. - - - -

Bryan

 

[chrissie1]

could it help if you do this before the select and insert

me.recordsource = ""
me.requery

and then after all the selcts and inserts

me.recordsource = "SELECT tblAvailableLocations.Location FROM tblAvailableLocations;"
me.requery

because you are trying to delete and hav it open at the same time



"What a wonderfull world" - Louis armstrong

 

[chrissie1]

could it help if you do this before the select and insert

me.recordsource = ""
me.requery

and then after all the selcts and inserts

me.recordsource = "SELECT tblAvailableLocations.Location FROM tblAvailableLocations;"
me.requery

because you are trying to delete and hav it open at the same time



"What a wonderfull world" - Louis armstrong

 

[Bry12345]

Thanks Chrissie!!

I cleaned up the syntax:

Got Focus:
Me.Location2.RowSource = ""
Me.Location2.Requery

my code

Me.Location2.RowSource = "SELECT tblAvailableLocations.Location FROM tblAvailableLocations;"
Me.Location2.Requery

Lost Focus:
Me.Location2.RowSource = ""
Me.Location2.Requery

Setting the RowSource to "" did what I was looking for. I just need to get the record saved in between moves and I'm good to go.

Thanks again!! - - - -

Bryan

 

[chrissie1]

uhm by the way
i have been thinking
if you use this in a multiuser enviroment you are going to run into trouble because several users will be trying to create this table at the same time.
Solution

try adding this to the table name
username and computername

for username you have an easy function currentuser
for computername you will have to search for a api call on the net like this

http://www.mvps.org/access/api/api0008.htm

"What a wonderfull world" - Louis armstrong

 

[Bry12345]

Good point!

Thanks! - - - -

Bryan