DoCmd Alter Add Column Questions

[Carl23]

Below is my code for my program. I am stuck on the bottom code (SUB COMMAND 35) where I am trying to add a column to a table. The name of the column is REPO_NUM. I am getting an error code 'SYNTAX ERROR'. I would also like to add another InputBox and ask for a value that would be inserted in the column stating REPO_NUM. FYI, I am new at VB and SQL and Stewart Ross punched up Sub_Command 28.
Thanks,
Carl

Option Compare Database
Private Sub Command7_Click()
   DoCmd.RunCommand acCmdImportAttachExcel
End Sub
Private Sub Command28_Click()
   Dim StrSql As String
   Dim tblname As String
   MyValue1 = InputBox("Enter File Number", "MyInputbox")
   tblname = "Sheet" & MyValue1
   StrSql = "DELETE * FROM [" & tblname & "] WHERE Physician_Comment <>'y'"
   DoCmd.RunSQL StrSql
End Sub
Private Sub Command35_Click()
   Dim StrSql As String
   Dim tblname As String
   MyValue = InputBox("Enter Report Number Column", "MyInputbox")
   tblname = "Sheet" & MyValue1
   StrSql = "ALTER [" & tblname & "] ADD COLUMN REPO_NUM"
   DoCmd.RunSQL StrSql
End Sub

[Stewart Ross]

Hi Carl. The syntax for the ALTER statement can be checked in Access by typing ALTER into the Help search box. If you do so you will see that you are missing two things: the keyword TABLE after the ALTER, and the type of the field REPO_NUM. If I guess that it is a whole number represented by a long integer value, line 18 above should be

StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN REPO_NUM LONG"

If in fact REPO_NUM is a text value then change LONG to something more appropriate, like CHAR(20) for example (representing a character sequence of up to 20 characters).

Be aware that you cannot execute this on the same table more than once - a run-time error would occur if you attempt to add a field with a duplicate name.

-Stewart

[Carl23]

Command35 subroutine has been modified per your suggestion and now works and reads:

Private Sub Command35_Click()
   Dim StrSql As String
   Dim tblname As String
   MyValue = InputBox("Add Column to Table--Enter Report Number that will be inserted in table", "MyInputbox")
   tblname = "Sheet" & MyValue
   StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN REPO_NUM LONG"
   DoCmd.RunSQL StrSql
End Sub

I looked up the help file-and saw the INSERT statement and I would like to know how to use the sql statement below instead of the 6th line above.

StrSql = "ALTER  [" & tblname & "] ADD COLUMN REPO_NUM" & "INSERT INTO [" & tblname & "] REPO_NUM 'MyValue'"

Thanks in advance,
Carl

[Stewart Ross]

I suspect you are confusing an Update action with an Insert. Insert is intended to add one or more new rows, update is intended to update field values in existing rows. It is of the form

UPDATE [yourtable]SET [yourfield]=somevalue WHERE somecondition

-Stewart

[Carl23]

YIPEE! Once again, big thanks. Below is the working code for the UPDATE.

Private Sub Command35_Click()
   Dim StrSql As String
   Dim tblname As String
   Dim MyValue As Integer
   MyValue = InputBox("Add Column to Table--Enter Report Number that will be inserted in table", "MyInputbox")
   tblname = "Sheet" & MyValue
   StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN REPO_NUM LONG"
   StrSql1 = "UPDATE [" & tblname & "] SET REPO_NUM = MyValue"
   DoCmd.RunSQL StrSql
   DoCmd.RunSQL StrSql1
End Sub

Thanks again,
Carl

[Stewart Ross]

Hi Carl. If you want the field to be updated to the value of variable MyValue line 8 should actually be

StrSql1 = "UPDATE [" & tblname & "] SET REPO_NUM = " & MyValue

-Stewart