Using a variable in a "DoCmd" VBA argument

#1 (**permalink**) 28-Jul-2006, 08:09 AM

I have written an event procedure to run when a user clicks a command button
in a form. The button press is supposed to engage the action to make a copy
of a specified table.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10044-using-a-variable-docmd-vba-argument.html

The form exposes a list box showing all the table names in the current
database to the user, and it allows the user to specify both the present
name of the table to be copied and the new name of the copied table.

The event procedure in VBA declares two variant variables and performs the
DLookup function to obtain the specified names. This part is tested and
works.

I next want to evoke the DoCmd.CopyObject function which includes the names
of the "DestinationDatabase" and "NewName" as arguments; however, I don't
know how to make the call work unless I actually type the table names
enclosed by quotation marks.

For example: this works ...
DoCmd.CopyObject "ClientSourceData", "ClientSourceData_12-31-1999",
acTable, "ClientSourceData"

Is there any way to get the DoCmd to accept the table names by passing the
table names from the user-selected variables rather than by typing literal
names in quotes in the DoCmd call?

Or, is there another way to accomplish what I am trying to do? I can't find
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10044
a solution and would appreciate some suggestions. Thanks.

#2 (**permalink**) 28-Jul-2006, 08:09 AM

If you have the names in controls on the form, you can refer directly to
those instead of the literals in quotes.

For example if you have text boxes named Text1, Text2, and Text3:
DoCmd.CopyObject Me.Text1, Me.Text2, acTable, Me.Text3

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"George Walsh" wrote in message
news:0T_bg.3730$9W5.461@tornado.socal.rr.com...
>I have written an event procedure to run when a user clicks a command
>button in a form. The button press is supposed to engage the action to
>make a copy of a specified table.
>
> The form exposes a list box showing all the table names in the current
> database to the user, and it allows the user to specify both the present
> name of the table to be copied and the new name of the copied table.
>
> The event procedure in VBA declares two variant variables and performs the
> DLookup function to obtain the specified names. This part is tested and
> works.
>
> I next want to evoke the DoCmd.CopyObject function which includes the
> names of the "DestinationDatabase" and "NewName" as arguments; however, I
> don't know how to make the call work unless I actually type the table
> names enclosed by quotation marks.
>
> For example: this works ...
> DoCmd.CopyObject "ClientSourceData", "ClientSourceData_12-31-1999",
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10044
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10044
> acTable, "ClientSourceData"
>
> Is there any way to get the DoCmd to accept the table names by passing the
> table names from the user-selected variables rather than by typing literal
> names in quotes in the DoCmd call?
>
> Or, is there another way to accomplish what I am trying to do? I can't
> find a solution and would appreciate some suggestions. Thanks.
>
>

#3 (**permalink**) 28-Jul-2006, 08:09 AM

Thanks very much! That works.

"Allen Browne" wrote in message
news:%23ncvwUOfGHA.3364@TK2MSFTNGP05.phx.gbl...
> If you have the names in controls on the form, you can refer directly to
> those instead of the literals in quotes.
>
> For example if you have text boxes named Text1, Text2, and Text3:
> DoCmd.CopyObject Me.Text1, Me.Text2, acTable, Me.Text3
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "George Walsh" wrote in message
> news:0T_bg.3730$9W5.461@tornado.socal.rr.com...
>>I have written an event procedure to run when a user clicks a command
>>button in a form. The button press is supposed to engage the action to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10044
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10044
>>make a copy of a specified table.
>>
>> The form exposes a list box showing all the table names in the current
>> database to the user, and it allows the user to specify both the present
>> name of the table to be copied and the new name of the copied table.
>>
>> The event procedure in VBA declares two variant variables and performs
>> the DLookup function to obtain the specified names. This part is tested
>> and works.
>>
>> I next want to evoke the DoCmd.CopyObject function which includes the
>> names of the "DestinationDatabase" and "NewName" as arguments; however, I
>> don't know how to make the call work unless I actually type the table
>> names enclosed by quotation marks.
>>
>> For example: this works ...
>> DoCmd.CopyObject "ClientSourceData", "ClientSourceData_12-31-1999",
>> acTable, "ClientSourceData"
>>
>> Is there any way to get the DoCmd to accept the table names by passing
>> the table names from the user-selected variables rather than by typing
>> literal names in quotes in the DoCmd call?
>>
>> Or, is there another way to accomplish what I am trying to do? I can't
>> find a solution and would appreciate some suggestions. Thanks.
>>
>>
>
>