Adding columns using an Access2007 form with VBA

[sousabeast]

I'm using Access 2007 for the first time and I have made a form for my datasheet. However, the user needs the ability to add columns in the form. I have worked out the VBA code and the only thing standing in my way is that I can't end the command button control proccess/module or anything like that. Here is my code

Public Sub addfield()


DoCmd.Close acTable, "Non HAPS", acSaveYes
DoCmd.Close acForm, "Non HAPS", acSaveYes
DoCmd.Close acModule, "Add New Non HAP", acSaveYes

Dim curDatabase As Object
Dim tblNon_HAPS As Object
Dim colz As Field
Dim z As String


Set curDatabase = CurrentDb

Set tblNon_HAPS = curDatabase.TableDefs("Non HAPS")

z = InputBox("Enter New Non HAP Name")

Set colz = tblNon_HAPS.CreateField(z, dbText)



tblNon_HAPS.Fields.Append colz

End Sub
This sub is referenced by the form commandbutton private sub. So when I click my commandbutton I get an error saying that the database could not lock engine because it is in use by another person or project.


Is there anyway I can end the commanbutton private sub without being in it?

 

[DrSimon]

I think you are confusing a table with a form that displays that table in Datasaheet view. You can amend a table relatively easily with VBA, but you cannot amend a form dynamically unless it is in Design View, so you have to use another form to amend it.

Create another form and attach your existing code to a button (or however you want to run it). Insert the code below underneath it.
It will open your form in Design View, create a new field bound to the new column and then give the new form field the same name. It then save the form and reopen it in Datasheet View.

' after tblNon_HAPS.Fields.Append colz
Dim fName As String
fName = "Non HAPS"
DoCmd.OpenForm fName, acDesign
Set MyForm = Forms(fName)
Set MyControl = CreateControl(fName, acTextBox, acDetail, , z)
MyControl.Name = z
DoCmd.Close MyForm, fName, acSaveYes
DoCmd.OpenForm fName, acFormDS
End Sub

If you are trying to add fields in Form View you will have a LOT more work to position those extra fields. The code above will just place the new fields at top left of the form.
Note that you ought to add a check that the new field doesn't already exist or blNon_HAPS.Fields.Append colz will fail

Simon Rouse

 

[sousabeast]

Thanks for the help. I didnt' think about checking to make sure the field doesn't already exist!

 

[DrSimon]

btw I just noticed. The 2nd last line should be

DoCmd.Close acForm, fName, acSaveYes

Simon Rouse