VBA - Code stops running correctly

[Joe]

Hi



There are three command buttons and a combo box in a form.



Here is the code in the first button:



On Error GoTo TrataErro

Dim sql As String

sql = "ALTER TABLE Clientes ADD COLUMN Celular Text (38)"

DoCmd.RunSQL sql

MsgBox "The field Celular was created!"

Exit Sub

TrataErro:

MsgBox "The Field Celular already exists!"





Here is the code in the second button:



On Error GoTo TrataErro

Dim sql As String

sql = "ALTER TABLE Clientes DROP COLUMN Celular"

DoCmd.RunSQL sql

MsgBox "The field Celular was deleted!"

Exit Sub

TrataErro:

MsgBox "The field Celular doesn't exist!", , TIT





By clicking the buttons, the code works exactly as expected. The field is
created and then deleted.



Here is the code in the third button, which goal is to program the combo box
cmbClientes:



Dim sql As String

sql = "SELECT DISTINCT Estado FROM Clientes"

cmbClientes.RowSource = sql

cmbClientes.SetFocus

cmbClientes.Dropdown



If I press the first Button, the code doesn't work. The message in the error
handling is shown.



If I disable the error handling, the following message is shown: The
database engine could not lock the table Clientes, it is already in use.





I don't understand where the error is, nor how to solve it.



Please, could you help me?



Thanks



Joe

 

[Tom van Stiphout]

On Thu, 30 Oct 2008 00:58:03 -0200, "Joe" <[email protected]>
wrote:

I am assuming you meant "If I press the third Button, the code doesn't
work..."
Try this:
At the top of that procedure write:
Currentdb.Tabledefs("Clientes").Fields.Refresh

-Tom.
Microsoft Access MVP

 

[Joe]

Hi Tom

By pressing the first and second buttons before the third, the code in these
two buttons works correctly.

After pressing the 3rd button, by pressing the 1st and 2nd, their codes
doesn't run anymore.

It is shown the message as written before.

Anyway, I inserted the code you suggest, but unfortunately it doesn't work.

Thanks for your help.

If you have another idea, please share with me.

Joe