How would you do this

[Joe8915]

This should be pretty simple to do.

I have a query, to which I have information in. All I want to do is copy the records out of the query and paste them into a table. The table is in the same database as the query. I need a macro to do this. I know I should be able to do it it a macro..................right?

Now I am not good at codes, but willing to give it a try again.

Any help would be gladly welcome

Thanks All

 

[Mile-O]

Use an append query instead to transfer your data in the query to your table.

Open the query design,

Goto the menu, I think it's under Query and select Append Query.

This asks you to select the table you'd like append the query results to.


Once this is done you can then run your query and it will insert your query results into your table.




You'll find, however, that you will be given to informative message by Access saying:

You are about to append x records to the table.

and:

You successfully appended x records to the table.

...or something to that effect.


To get rid of these there are three ways and I'll disregard one now: under the options, you can disable (I think it's) warning messages. Not good to do!

The other two options are:

A) A macro:

i) create a new macro;
ii) Select, in the following order the three lines:

SetWarnings
OpenQuery
SetWarnings

iii) under the first set warnings line, set the warnings to false
iv) under the open query, select your append query;
v) under the second set warnings line, set the warnings to true


This should work fine but using a macro has its limitations in that they don't allow for error handling/trapping which brings us to:

B) VBA:

I'm guessing you'd want to open the query (append the details) on the click of a button, so here's an example

Private Sub cmdYourCommandButton_Click()
    
    On Error Goto Err_ErrorHandler

    With DoCmd
        .SetWarnings False
        .OpenQuery "qryYourQuery"
        .SetWarnings True
    End With

Exit_ErrorHandler:
    Exit Sub

Err_ErrorHandler:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler

End Sub

Hope this all helps...

 

[Joe8915]

Boy that was a quick............... I will give it a try.


thanks

 

[Joe8915]

OK That work great........... and many thanks. Now next question is How can I overwrite the records that are in the table. Right now I am just appending the records and the table of course is getting bigger. Is it possible to do this??????


Many thanks and the quick reply

 

[Joe8915]

Dang just ran into another problem.

Some how I am not getting all the records. It will only paste about 450 records at a time, of course I have close to 500.

Any ideas on this

 

[MattS]

How can I overwrite the records that are in the table.

Do you mean delete everything in the table before adding your new records? If so, simply add the following before the .OpenQuery line in the code:

.RunSQL "Delete * From tblYourTable"

Regarding adding the records, if it's not adding all of them then there's probably some kind of validation or primary key violation (i.e. trying to add text to a numeric field). Try running it without turning the warnings off - if this is the case you should get a message box saying "Access could not add X records because of ..........."

HTH.

 

[Joe8915]

Ok Guys, thanks for everything, all is good and well.

Everything works great, worked out al the little bugs and runs like a charm. I now have it where it will email every week.

Thanks to all