Access add column by form data

[Ferndi Kiletus]

Hi!

I am struggling with a piece of code. I am new to VBA and i want this code to create a new coloumn in a table with a name that is from a text box on a form (same form as the save button) on button press. But I am not exactly sure how to refer to the value...

Can somebody help me please?

Private Sub savebutton1_Click()
mSaved = True
CurrentDb.Execute ("ALTER TABLE table1 ADD COLUMN Forms![Add employee]![Id].Value Text;")
MsgBox "Saved!"
End Sub

For a bonus question, how can I check if all the fields are correctly filled, and only then make the changes and saving process?

Any help appriciated...

[migi48]

Hi,

Have you tried putting the value of the textbox in a string first before you execute your sql code?

something like this:

Private Sub savebutton1_Click() 
mSaved = True
Dim newColName As String 
Set newColName = Me.txtNewColName.Value
CurrentDb.Execute ("ALTER TABLE table1 ADD COLUMN " & newColName & "TEXT(50)") 
MsgBox "Saved!" 
End Sub

Not really sure though. Just trying to help. :)

[Ferndi Kiletus]

I altered my code like:

Private Sub savebutton1_Click()
  Dim newColName As String
  Set newColName = Me.ID.Value
  CurrentDb.Execute ("ALTER TABLE table1 ADD COLUMN" & newColName & "Text;")
  mSaved = True
  MsgBox "Saved!"
End Sub

But it doesnt work, it sais: Compile error, object required, abd highlights Set newColName part.

[migi48]

Hmmmm.

try this instead:

currentdb.Execute ("ALTER TABLE table1 ADD COLUMN('" & me.ID.Value& "') Text;")

[Ferndi Kiletus]

I tried, and there is a syntax error in it

[TheSmileyCoder]

I dont know if it can be done in the way you have suggested. I am however fairly sure it can be done if the string is parsed first.
A thing to note however is that this will likely only work if noone is using the table at the same time, and you should probably have error handling to inform the user in case it fails.

Example below:

Private Sub savebutton1_Click() 
On Error goto err_Handler
 'First check for valid input
 If IsNull(Me.ID) then
  Msgbox "Nothing was entered as field name"
  Exit Sub
 End If

 Dim strSQL as String
 strSQL="ALTER TABLE tablazat ADD COLUMN [" & Me.Id & "] Text;") 
 CurrentDb.Execute strSQL,dbFailOnError
 
 mSaved=true
 MsgBox "Saved"

 Exit Sub

Err_Handler:
  Msgbox "An error occured while trying to add new column." & vbnewline & _
         "See below for details on error:" & vbnewline & vbnewline & _
         "[" & err.number & "]" & vbnewline & err.Description
End Sub

[TheSmileyCoder]

The reason that

Set newColName = Me.txtNewColName.Value

fails, is that only objects needs to use the SET command.
The correct syntax would be:

newColName = Me.txtNewColName

The .Value is optional, since .Value is the default parameter for textboxes.

[Ferndi Kiletus]

It worked, thank you so much...

[migi48]

Haha. Good job. :)

Like I said, I'm also a newbie in VBA and just trying to help. Sorry for my syntax errors. :D Thanks to TheSmileyCoder for also teaching me. :)