Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select and move a record (Win ME/Access 97)

    I need to select a record with the mouse in datasheet view, move it to another table, and then delete the original record. Can that be done?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select and move a record (Win ME/Access 97)

    It would help if you fleshed out the question a bit more. You could cut and paste it from one table to another if that's what you're asking, but are you working in the tables or from a form? Is this something you want to do using code?
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select and move a record (Win ME/Access 97)

    Do you mean datasheet view of a table or query? Just use cut and paste.

    Or is this in a form in datasheet view? In that case, you need a way to call code. Some possibilities:
    <UL><LI>Place the datasheet form as a subform on a form in form view. Put a command button on the main form.
    <LI>Convert the form to a continuous form (you can make a continuous form look like a datasheet, but you can have a form header and footer, and you have more control over the form.)
    <LI>Use the Form_KeyPress event (combined with KeyPreview = True) to react to a keyboard shortcut.
    <LI>Create a custom toolbar and attach it to the form.[/list]The code to move a record involves constructing the SQL statement for an append query and a delete query and executing them, or creating and storing an append query and delete query with a parameter referring to the form and running them. The exact form depends on the structure of the source and target tables:
    <UL><LI>Do source and target contain exactly the same fields?
    <LI>Does the source contain an AutoNumber field? If so, how about the corresponding field in the target? Is it also an AutoNumber field?[/list]If you would like more detailed instructions, please tell us which of the above options you would like to implement, and provide answers to the questions.

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select and move a record (Win ME/Access 97)

    I am with you through the keyboard shortcut. Why the custom toolbar? In the append and delete queries, how do I convey to the queries which is the specific record that I want to cut and paste?

    The source and target contain the exact same structures, with the exception that the source field has a number field, whereas the target contains an autonumber field.

    I know this can be done with cut and paste, but I would like something easier for a user to do.

    This is still in the discussion phase, but it looks like something that could be very helpful.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select and move a record (Win ME/Access 97)

    The most common way to initiate an action in a form is a command button. In a form in datasheet view, you can't display a command button, however. The first bulleted list was meant as a series of alternatives; one of these is to create a custom toolbar with a toolbar button that moves the current record. You can "tie" a custom toolbar to a form by setting the Toolbar property to yhe name of the toolbar.

    You write
    <hr>the source field has a number field, whereas the target contains an autonumber field<hr>
    Are you sure it isn't the other way round? That would seem much more logical.

    The queries could look like this:

    INSERT INTO tblTarget SELECT * FROM tblSource WHERE ID = [Forms]![frmMyForm]![ID]

    DELETE tblSource.* FROM tblSource WHERE ID = [Forms]![frmMyForm]![ID]

    tblSource and tblTarget stand for the source and target tables, frmMyForm for the name of the form, and ID for a field that uniquely identifies the record. By referring to the form, you copy/delete the current record.

    If you make your form into a subform, the reference should be changed to [Forms]![frmMainForm]![sbfMyForm]![ID] where frmMainForm is the name of the main form, and sbfMyForm is the name of the datasheet form as a control on the main form. This is not necessarily the same as the name of the datasheet form in the database window.

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select and move a record (Win ME/Access 97)

    I created a form called Test Move Pagos History and a query called QTest Move Pagos.

    The query statement is:

    INSERT INTO TPagos
    SELECT * FROM TpagosHistory WHERE ID = [Forms]![Test Move Pagos History]![SeqNum];

    The target table is TPagos, source table is TPagosHistory and the unique record identifier is SeqNum.

    Right now, I open the form and then go to the DataBase window and run the query. I get a window asking for the paramenter ID.

    Shouldn

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select and move a record (Win ME/Access 97)

    The reason you are getting that parameter dialog box is that it cannot recognise ID.

    Is ID a field in table tpagosHistory?

    Pat

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select and move a record (Win ME/Access 97)

    No, Pat. The

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •