Runtime Error 2471

[Thompyt]

I am trying to add a column to a table.



It is easy if it is not there

CurrentDb.Execute "ALTER TABLE TableName ADD COLUMN FieldName Type(Char length)"

When it is there, then of course I error out. I am trying to set a function to check if it is there or not. If it is not there then make the Field and update from another source. If it is there, then only update from the other source.

I think it is how I am referencing the function as I error out on the red Italicized, Bold and underlined code in the function. Am I calling the function incorrectly or am I putting in the incorrect information in the call code?

See below

Thanks

Public Sub AddColumn_Click()
Dim strSQL As String

strSQL = "UPDATE Data INNER JOIN Units ON Data.Unit = Units.Units SET Data.Netbase = [Units].[Netbase] WHERE (([Units].[Units]=[data].[Unit]));"

Call FieldExist("Data", "Netbase")
    CurrentDb.Execute "ALTER TABLE Data ADD COLUMN Netbase Text(20)"
    DoCmd.RunSQL strSQL
    Exit Sub

Call FieldExist("Data", "Netbase")
    DoCmd.RunSQL strSQL

End Sub

Function =

Public Function FieldExist(sField As String, sTable As String) As Boolean
   Err.Clear
   If (DCount(sTable, sField) = 0) And Err Then
        FieldExist = False
   Else: FieldExist = True
   End If
End Function

[ranman256]

Is the db split?

why can't you just use manual table design?

[Thompyt]

Because I am naturally lazy and prefer to do a click vs a multitude. Actually, the table gets replaced every week with an updated imported version. The extra column is to use as a reference to another table that does not even come close structurally. (Another product from another organization.)

[pbaldy]

Can you use normal error handling, and for that error have it Resume Next instead of going to the error handler?

http://www.baldyweb.com/ErrorTrap.htm

[Thompyt]

Thanks pbaldy. Your suggestion got rid of the function module.

Public Sub AddColumn_Click()
Dim strSQL As String

On Error GoTo Errorhandler

strSQL = "UPDATE Data INNER JOIN Units ON Data.Unit = Units.Units SET Data.Netbase = [Units].[Netbase] WHERE (([Units].[Units]=[data].[Unit]));"

    CurrentDb.Execute "ALTER TABLE Data ADD COLUMN Netbase Text(20)"
    DoCmd.RunSQL strSQL
    Exit Sub

ExitHandler:
    DoCmd.RunSQL strSQL
    Exit Sub

Errorhandler:
        DoCmd.Hourglass False
        Resume ExitHandler
End Sub

It works as I wish, no errors popping up and updates both ways (No field or with field).

[pbaldy]

I was thinking more like:

Public Sub AddColumn_Click()
 Dim strSQL As String

On Error GoTo Errorhandler

strSQL = "UPDATE Data INNER JOIN Units ON Data.Unit = Units.Units SET Data.Netbase = [Units].[Netbase] WHERE (([Units].[Units]=[data].[Unit]));"

    CurrentDb.Execute "ALTER TABLE Data ADD COLUMN Netbase Text(20)"
    DoCmd.RunSQL strSQL

ExitHandler:
    Exit Sub

Errorhandler:
  Select Case Err
    Case 2471          'field already exists, ignore
      DoCmd.Hourglass False
      Resume Next
    Case Else
      MsgBox Err.Description & " in AddColumn_Click "
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

[Thompyt]

pbaldy,
That gives me a pop up msg and doesn't update the column if it already exists.

[pbaldy]

Is the error number right? No big deal, you have something that works for you, but this is more "typical" error handling.

[Thompyt]

I think the error came from the Function. You suggestion lessens the code needed. Thanks!

[pbaldy]

No problem!