Rename tables via inputbox variable

  • Thread starter Thread starter Petr
  • Start date Start date
P

Petr

Hallo,
I would need to dynamically rename the tables with the user input
variables submitted by user via inputbox.
I tried approach from Excel, but syntax error has occured during
compilation in Access.

Any ideas. Thanks a lot in advance for any suggestions.

Petr Duzbaba


Function rename_via_inputbox()

On Error GoTo Rename_rename_Err

Dim SegmInput As String
Do:
SegmInput = InputBox("Submit abbrev (SR, SFDI, SFRB, SFZP, SZIF, SFK,
SFCK, SFZUP or MR)!")

If Not SegmInput = "SR" And Not SegmInput = "SFDI" And Not SegmInput =
"SFRB" _
And Not SegmInput = "SFZP" And Not SegmInput = "SZIF" And Not
SegmInput = "SFK" And Not SegmInput = "SFCK" _
And Not SegmInput = "SFZUP" And Not SegmInput = "MR" Then
MsgBox "Invalid input - must be: SR, SFDI, SFRB, SFZP, SZIF, SFK,
SFCK, SFZUP or MR!", vbCritical
End If


Loop Until SegmInput = "SR" Or SegmInput = "SFDI" Or SegmInput =
"SFRB" Or SegmInput = "SFZP" Or SegmInput = "SZIF" _
Or SegmInput = "SFK" Or SegmInput = "SFCK" Or SegmInput = "SFZUP" Or
SegmInput = "MR"

segment_dbvr_acc = SegmInput


Dim YearInput As Variant
Do:
YearInput = InputBox("Submit year (n-1)!")

If Not YearInput = "2004" And Not YearInput = "2005" And Not YearInput
= "2006" And Not YearInput = "2007" And Not YearInput = "2008" And Not
YearInput = "2009" And Not YearInput = "2010" Then
MsgBox "Invalid input - must be within 2004-10!", vbCritical
End If

Loop Until YearInput = "2004" Or YearInput = "2005" Or YearInput =
"2005" Or YearInput = "2007" Or YearInput = "2008" Or YearInput =
"2009" Or YearInput = "2010"

year_dbvr_acc = YearInput


DoCmd.Rename " segment_dbvr_acc & " " & year_dbvr_acc & " Pr",
acTable, "nsr"



Rename_rename_Exit:
Exit Function

Rename_rename_Err:
MsgBox Error$
Resume Rename_rename_Exit

End Function
 
Sorry in the new name field of the macro put
=InputBox("What is the name of the new table?")

Chris
 
Back
Top