Renaming a table in a macro...

[Marsden54]

When performing the Rename action in a macro (to rename a table), is there any way you dont have to specify what you want to rename the table to there and then but rather bring up an input box to type the name in when you run the macro??


Any help appreciated!!

 

[Mike375]

When performing the Rename action in a macro (to rename a table), is there any way you dont have to specify what you want to rename the table to there and then but rather bring up an input box to type the name in when you run the macro??


Any help appreciated!!

If you place your macro on a form and have an unbound text box on the form then in the macro

New Name =[Forms]![MyForm]![TextBoxName]

Then enter the new name of the table in the text box and then run the macro.

Mike

 

[Mile-O]

The only problem here is that the user could hit return without entering a new name into the InputBox which would spell the end for the macro.

And, with examples like this, it becomes easy to see why they are not advised in a database - they fall over too easily and have no way of catching errors before they happen.

Thus, the best method is to use VBA code. A simple sub, for this example, would be (assuming you have the table renaming sub on a button's click event):

Private Sub MyButton_Click()

    On Error Goto Err_ErrorHandler

    Dim strTable As String

    strTable = InputBox("Please enter the new table name.", "Table Name")

    If strTable = vbNullString Then
        MsgBox "No table name entered.", vbExclamation
    Else
        DoCmd.Rename strTable, acTable, "MyTable"
        MsgBox "Table successfully renamed.", vbInformation
    End If

Exit_ErrorHandler:
    strTable = vbNullString
    Exit Sub

Err_ErrorHandler:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler

End Sub

One of the most annoying problems with macros (and code without error handling for that matter) is that should an error occur, all values held within variables are immediately lost.

 

[Mike375]

Not really. The macro condition could be set so that an entry in the text box is required and if not a message comes up.

Of course if the person tries to run it twice then....there is no longer TableABC to rename and a message will come up

Mike

 

[Mile-O]

Of course if the person tries to run it twice then....there is no longer TableABC to rename and a message will come up

So why bother?

 

[Mike375]

On one of the other threads I asked Rich about converting macros to code where the macro had RunMacro actions. My question was "do you convert the macro to code that is being run by RunMacro and then paste the code in to replace RunMacro". He bailed out with some BS about not understanding macros.

I am trying a few to see how it goes but it is not easy.

I think they need to be modules rather than built into OnClick on labels so that they can be changed when the data base has been opened.

I assume an OnClick could run a module (or call a module??) and then you would deal with the module the same as a macro, that is, bring the data base windown forward and enter design for the module.

Mike

 

[Rich]

On one of the other threads I asked Rich about converting macros to code where the macro had RunMacro actions. My question was "do you convert the macro to code that is being run by RunMacro and then paste the code in to replace RunMacro". He bailed out with some BS about not understanding macros.

Mike

No he didn't!

 

[Mike375]

No he didn't!

Yes he did!!

I've no idea, I don't do Macros, they can seriously damage the health of your programme.
In any case even the Wizard can't make a silk purse from a pigs ear

 

[Rich]

In any case even the Wizard can't make a silk purse from a pigs ear