Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using a variable in a "DoCmd" VBA argument

Discussion in 'Information Technology' started by George Walsh, Jul 28, 2006.

  1. George Walsh

    George Walsh
    Expand Collapse
    Guest

    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",
    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.
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <Geo_Walsh@san.rr.com> 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",
    > 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.
    >
    >
     
  4. George Walsh

    George Walsh
    Expand Collapse
    Guest

    Thanks very much! That works.


    "Allen Browne" <AllenBrowne@SeeSig.Invalid> 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" <Geo_Walsh@san.rr.com> 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",
    >> 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.
    >>
    >>

    >
    >
     

Share This Page