Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Nov 2004
    Location
    Crick, just outside Rugby
    Posts
    42

    Question Unanswered: move record to archive table

    Hi

    I'm building a simple database and I need some help in moving a record.

    This database is to control quality documents within the company and when one it to be withdrawn I want to move it to an archive table. I also want to be able to retrieve it at a later date if necessary.

    I already have a simple form to display the record to move, but I'm having problems in writing a macro to move it by a simple click of a button.

    Can anyone help me please?

    Thank you

    Sherri

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    All youll need are a couple of quick queries to do this
    Something like

    INSERT INTO TblArchieve
    SELECT tblMain.* From tblMain
    WHERE tblMain.ID = Forms!FrmName!ControlID

    and also

    DELETE tblMain.* From tblMain
    WHERE tblMain.ID = Forms!FrmName!ControlID

    then get your Macro to run both queries
    HTH
    Dave

  3. #3
    Join Date
    Nov 2004
    Location
    Crick, just outside Rugby
    Posts
    42
    Thanks...... but how?

    I'm not sure how or where to put that. Can you explain a bit deeper please?

    Thanks
    Sherri

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    If you must do it as a Macro do the following. First make the first query just as DavidCoutts suggested. Then make the second query in the same fashion. Let’s assume you name them QryStep1 and QryStep2 respectively.

    Next go to macros and select new. Make sure the “Macro Name” column is showing. If it is not select View/Macro Name. In the first row for “Macro Name” put something telling like “Archivemacro”. In the next field “Action “ select open query. At the bottom of the window select QryStep1. In the next row put nothing in the Name column in Action select open query once more and select QryStep2. Close and save the Macro. Macro1 will suffice.

    In the form in question select the button you wish to use open it’s properties and on the Event Tab select the OnClick field from the drop down select “Macro1.Archivemacro”.

    Save.

    Hope that is helpful.
    Darasen

  5. #5
    Join Date
    Nov 2004
    Location
    Crick, just outside Rugby
    Posts
    42

    Red face

    That's great.

    I have made the first QueryStep1, as suggested, and created the macro to run it but I'm a bit confused about the WHERE statement as when I run it, it tells me that it is going to append (0) records.

    This is the SQL that I used:

    INSERT INTO [Withdrawn Documents]
    SELECT [Master Document List].*
    FROM [Master Document List]
    WHERE [Master Document List].ID = Forms!ConfirmDeletionForm!ControlID;

    The 'Master Document List' is the original table and the 'Withdrawn Documents' is the archive table. The form that is displaying the document to be moved is called 'ConfirmDeletionForm'

    In the reply from David Coutts the WHERE statement has 'ContolID' at the end - was I supposed to use this or was I supposed to change it to something? If so what?

    Thank you for all your help

    Sherri
    Last edited by I'mSherriToo; 11-04-04 at 06:29.

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Sherri,

    What i was trying to indicate was that you should reference the control on your form that contains you primary key field even if its hidden, So if its a textbox then it would be the Name of that text box,
    Look in the properties if your unsure,
    This should let you reference whatever text is in your control.
    Hope this makesa things clearer

    Dave

  7. #7
    Join Date
    Nov 2004
    Location
    Crick, just outside Rugby
    Posts
    42
    Hi David,

    The Primary Key field is called 'Number'.
    I replaced your ControlID in the SQL code like this:

    INSERT INTO [Withdrawn Documents]
    SELECT [Master Document List].*
    FROM [Master Document List]
    WHERE [Master Document List].ID = Forms!ConfirmDeletionForm!Number;

    When I run it, it tells me that it is going to run the Append Query etc. but then also says 'You are about to append 0 row(s)' and it doesn't move anything.

    Am I missing something?

    Thanks

    Sherri

    p.s. sorry for being a bit dim - I am trying....... honest!!

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    It's Ok Sherri,

    Assign on your Form a textBox with a link to your primary key just as you have for your other fields
    to do this put a text box on the form and under control source in the data tab put choose your primary key, in the name field put a name in for it lets call it txtPrimaryKey, on the "other" tab
    If you don't want it to be visible go to the format tab and change the Visible property to false,

    Change this line in your query

    Forms!ConfirmDeletionForm!Number

    to

    Forms!ConfirmDeletionForm!txtPrimaryKey

    now the query wont work until there is a value in this textbox
    so to test open your form in view mode choose the value you want to test with make sure its appearing and the textbox weve just added is showing the Value of the Number Field and Run your query, Everything should work fine after that

    if you dont understand anything about what ive just said just shout andd ill try to explain in a different way,

    Dave

  9. #9
    Join Date
    Nov 2004
    Location
    Crick, just outside Rugby
    Posts
    42
    Hi Dave,

    Thanks for your help.

    I have followed your instructions - but it remains the same. It tells me that it will append 0 row(s), and moves nothing.

    I think I'll give up now - thanks for trying anyway.

    Best wishes

    Sherri

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok we wont give up just yet option 2 might be to run some code

    something like this

    Code:
    Private Sub CmdArchieve_Click()
        Dim myText As String
        Dim mySQL As String
        
        DoCmd.SetWarnings False
        
        myText = txtPrimaryKey.Value
        
        mySQL = "INSERT INTO [Withdrawn Documents] " & _
                "SELECT [Master Document List].*" & _
                " FROM [Master Document List]" & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
        Debug.Print mySQL & Chr(10)
        DoCmd.RunSQL mySQL
          mySQL = "DELETE [Master Document List].*, [Master Document List].Number, * " & _
                " FROM [Master Document List] " & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
        Debug.Print mySQL & Chr(10)
        DoCmd.RunSQL mySQL
        
        DoCmd.SetWarnings True
        
    End Sub
    create a cmd button called command archieve and put this code in the on click event ive tested it and it's working fine

    Dave

  11. #11
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    0 Records

    I was glad to find this thread as just last night I was struggling with the exact same issue.

    I found that if the records were already sent to the "Withdrawn Records" table (mine is RemovedInventory) that I got the error message about 0 records being moved. That's how I caught onto the fact that the query copied and not moved the records to the new table.

    I poked around with queries but decided to go to bed before my head exploded.

    Try looking in the Withdrawn Records table to see if the records are already there.

    Perplexed

  12. #12
    Join Date
    Nov 2004
    Location
    Crick, just outside Rugby
    Posts
    42
    Hi,

    I have tried the above but I get a runtime error telling me that the Data is mismatched.

    This is the portion that it is highlighting in the debug:

    DoCmd.RunSQL mySQL
    mySQL = "DELETE [Master Document List].*, [Master Document List].Number, * " & _
    " FROM [Master Document List] " & _
    " WHERE [Master Document List].Number =" & _
    myText & ";"


    I have realised that I have missed some information that may be important - sorry!

    There is a field called 'ID' which goes to a lookup table called 'Document Types Lookup Table' to display 'Document Type' in the 'ConfirmDeletionForm' - could this be the reason?

    Although the 'Master Document List' table and the 'Withdrawn Documents' tables are identical, the ConfirmDeletionForm does have a field for called Document Type rather that ID as per the other tables.

    I'm sorry but this didn't occur to me until this data mismatch error appeared.

    I can tell it's a challenge for you now! I hope you can help me sort this out...... I can't grovel any lower!!

    Thanks

    Sherri

  13. #13
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok, let me try to get my head around the problem right,

    You have a form with fields from the 'Master Document List' on it
    By now you should have a textbox on the form with the primary key in it called txtPrimaryKey
    the second table 'Withdrawn Documents' is completely identical to your master Document List
    txtPrimarykey changes with everytime you change your record on your form,
    When you run the query by itself outwith the macro picking a value for test what what happens

    here are the queries in SQL Form just copy and paste these into new queries
    What message comes up?

    INSERT INTO [Withdrawn Documents]
    SELECT [Master Document List].*
    FROM [Master Document List]
    WHERE [Master Document List].Number =[myText]

    And the Delete Query
    DELETE Master Document List.*, [Master Document List].[Number]
    FROM Master Document List
    WHERE [Master Document List].[Number])=[myText];

    THese should ask you for MyText enter a valid primary key
    do the queries run they should do if they dont its time to investigate that

    Did the macro stop on docmd.RunSQL MySQL

    i Presume that the text you have is highlighted in yellow is that command,
    if you type it in does it come up with the correct option i.e. after you type in docmd and . is one of the options run SQL if so thet can't be the problem. and we can Investigate that.

    We will get this sorted for you, what should have been an easy question has turned into a bit of a beast hasn't it

    anything else you can think of that will help solve this will help imensly

    Dave

    just a thought you are refrencing the correct primary key arn't you it might jusdt be the query that youve filled your form with contains another primary Key rather than the one you think you might be refrencing, Ive done things like that before

  14. #14
    Join Date
    Nov 2004
    Location
    Crick, just outside Rugby
    Posts
    42

    Thumbs up

    Hi Dave,

    Thanks for being so patient with a complete muppet!

    I've put the new code into the query and run it....... it pops up a message box to enter parameter value 'myText'.
    I entered the document number (which is the primary key) and........ wait for it............. it told me.............. that is was going to append 1 row(s) !!!!!!!

    Yipppppeeeeeeeeee......

    I confirmed, and it copied it into the Withdrawn Documents table as I wanted.

    Next question...
    Do I put the Delete code into the same query? or should I have a separate one? If separate, how do I run both with one simple 'click'?

    I'm so chuffed I could hug you!.... Lucky you're in Cheltenham. By the way I'm from Stroud.... small world isn't it?

    Thanks so much for your help

    Sherri

  15. #15
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Sherri,

    right here we go
    neither we are going to run the queries in the code as above, we are going right back to the code now,
    we will build this up a little at a time

    on your form get to the properties of your button that you want to run your queries from.
    go to the events and got to the on clcik box and click the ... button
    this will either take you to your code if it's there or ask you what type of event you wan't.

    Choose code,
    Now were going to build this up slowley

    clear out all code if any from the event and put this into the box
    Code:
        Dim myText As String
        
        myText = txtPrimaryKey.Value
        MsgBox myText
    now run your form choose several values and see if the number in the messagebox shows the number your looking for i.e. the primay key of the table you want to move records out of, if not post back and we will figure out what the problem is

    next change the code to this

    Code:
        Dim myText As String
        Dim mySQL As String
        
        DoCmd.SetWarnings True
        
        myText = txtPrimaryKey.Value
        mySQL = "INSERT INTO [Withdrawn Documents] " & _
                "SELECT [Master Document List].*" & _
                " FROM [Master Document List]" & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
        
        DoCmd.RunSQL mySQL
    after you click the button this should now tell you that it will insert 1 record i.e. the record whoose primary key matches the textbox txtPrimaryKey

    next we will test the delete query so change the code to this
    Code:
    Private Sub CmdArchieve_Click()
        Dim myText As String
        Dim mySQL As String
        
        DoCmd.SetWarnings True
        
        myText = txtPrimaryKey.Value
        mySQL = "DELETE [Master Document List].*, [Master Document List].Number, * " & _
                " FROM [Master Document List] " & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
        
        DoCmd.RunSQL mySQL
        
    End Sub
    and click the button on your form this should now tell you that it is about to delete 1 row, if so this is working

    next put the code in like this

    Code:
    Private Sub CmdArchieve_Click()
        Dim myText As String
        Dim mySQL As String
        
        DoCmd.SetWarnings True
        
        myText = txtPrimaryKey.Value
        
        mySQL = "INSERT INTO [Withdrawn Documents] " & _
                "SELECT [Master Document List].*" & _
                " FROM [Master Document List]" & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
        
        DoCmd.RunSQL mySQL
        mySQL = "DELETE [Master Document List].*, [Master Document List].Number, * " & _
                " FROM [Master Document List] " & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
       
        DoCmd.RunSQL mySQL
        
    End Sub
    as long as your pointing to a different record this will tell you that it will insert 1 row click ok then it will tell you that it will delete 1 row click ok

    finally we will put our query together like this
    Code:
    Private Sub CmdArchieve_Click()
        Dim myText As String
        Dim mySQL As String
        
        DoCmd.SetWarnings False
        
        myText = txtPrimaryKey.Value
        
        mySQL = "INSERT INTO [Withdrawn Documents] " & _
                "SELECT [Master Document List].*" & _
                " FROM [Master Document List]" & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
        
        DoCmd.RunSQL mySQL
        mySQL = "DELETE [Master Document List].*, [Master Document List].Number, * " & _
                " FROM [Master Document List] " & _
                " WHERE [Master Document List].Number =" & _
                 myText & ";"
       
        DoCmd.RunSQL mySQL
        
        DoCmd.SetWarnings True
        
        Form.Requery
    End Sub
    and this should work ive added the Form.Requery to what was above hopefully there will be no problems in any stage but if so just post back

    Dave

    I live in cheltenham now but just a few weeks ago i was in Aberdeen, im working in cirrencester at the mo.

Posting Permissions

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