Table Locked

[handlebarry]

Hi, I have a form with two subforms on it, both subforms have different record sources. However subform A is used to update the table that subform B is based on.

This is causing an error as subform B is locking the table. I've set the recordset type to snapshot and made sure that there are no record locks but the problem persists.

Would creating a recordset clone be the solution or maybe someone has a different suggestion.

Thanks in advance

 

[KenReay]

Hi

Could you be a bit more precise about "locking the table", if formB does not allow update (more than one way to achieve this, but say .AllowAdditions, .AllowEdits, .AllowDeletions all set to false), and recordlocking strategy is optimistic, then a record in formB should not get locked, since a lock can only occur at the point a row is written, aftre an update, and since updates are disabled...

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[handlebarry]

Hi Ken - All of the AllowAdditions etc are set to false. The error meesage is:

Cannot modify table structure. Another user has the table open.

This is the code I am using to update the table

Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb
Set tdf = db.TableDefs("tblContractsToProperties")
tdf.Fields("ContractCode").DefaultValue = Chr(34) & Forms!frmEditContractProperties.ContractCode & Chr(34)
DoCmd.RunSQL "INSERT INTO tblContractsToProperties (GIS_Property_code, Property_Address) SELECT GIS_Property_Code, Property_Address FROM tblproperties WHERE tblproperties.check = True"

If I remove tblContractsToProperties as the recordsource from subformB then the code works fine.

thanks for the help

 

[earthandfire]

tdf.Fields("ContractCode").DefaultValue = Chr(34) & Forms!frmEditContractProperties.ContractCode & Chr(34)

This is the line causing the problem. You need the table opened exclusively to modify its properties.

Hope this helps.

 

[handlebarry]

I don't know another method other than the one I've used (default value)

So what I've done is changed the recordsource of the subform before and after the above code is used.

If anyone can come up "cleaner" solution I would be most appreciative

thanks for the advice

 

[earthandfire]

Instead of changing the DefaultValue property, could you not modify your sql statement to include the value for ContractCode?

Hope this helps.

 

[handlebarry]

had a problem doing this before as the contract code comes from a different table so could not get the syntax right, but will try again

 

[PHV]

Another way is to change the DefaultValue property of the subform's control

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886