Running module from Macro

[Harley Guy]

I have created a macro that calls to a module. I can get it to run but from a user standpoint, it would be confusing to perform. Here is the code to the module:

Private Function 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, "Server List"
MsgBox "Table successfully renamed.", vbInformation
End If
Exit_ErrorHandler:
strTable = vbNullString
Exit Function
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Function

I have designed an Open Module macro but I would like to have it not show the module and have the user run the sub form to rename the table. I want the dialog box to have the user enter in the new table name and have the "Table successfully renamed" box appear, then click OK and return to my switchboard.

Is it possible to code for that? I am somewhat of a novice and found the coding above in some other forums but haven't been able to find anything regarding this issue.

Thanks in advance!

[pbaldy]

You don't want OpenModule, you want RunCode, with the name of the function.

[Harley Guy]

You don't want OpenModule, you want RunCode, with the name of the function.

As I said before, I'm pretty much a novice in this part of Access.

I've found the RunCode but when I enter in the Private Function name, I get an error that says The expression you entered has a function name that Microsoft Office Access can't find.

In the design mode of the Macro created, I changed the action to RunCode and in Function Name, I used MyButton_Click().

Thanks!

[pbaldy]

Private Function

should be

Public Function

It should also be in a standard module, if it isn't already.

for the function to be able to be called from anywhere.

[Harley Guy]

OK, I got passed the not finding the expression...I created a control on a form to run the macro and I get an action failed...

I halt the macro and I get message "The object doesn't contain the automation object 'ArchiveCurrentAudit'

[aac]

I want linked a field with one record to another field with about 300 records that relation together, when I edit that record, those 300 records be changed. can u help me?

[pbaldy]

Harley: I don't see that in any of your code. Is it somewhere else? Is Option Explicit at the top of the module, and does the code compile?

aac: Since your question is unrelated to the topic of this thread, you should start a new thread rather than adding to this one.