Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Adding columns using an Access2007 form with VBA

Adding columns using an Access2007 form with VBA

Adding columns using an Access2007 form with VBA

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?   

RE: Adding columns using an Access2007 form with VBA

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

RE: Adding columns using an Access2007 form with VBA

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

RE: Adding columns using an Access2007 form with VBA

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


DoCmd.Close acForm, fName, acSaveYes

Simon Rouse

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close