Copy and append (1 Viewer)

[Joe8915]

I am trying to accomplish this in a macro. I know I was able todo this in older versions of access.

I want to get data from a query and paste and append into a table. I thought would be a real easy task. When I started to design the macro this is far as I got:

Qry Name: GetData
Table Name Storedata


Macro:
1st OpenQuery
Query Name: GetData
View: Datasheet
Data Mode: Read Only
2nd
RumMenuCommand
Command: SelectAllReocrds
3rd
?
Command: Copy
4th
Opentable
Table Name Store Data
5th
?
Command: Paste and append

The ? marks I cannot find any options in the macro actions catalog.

Thanks for taking the time to read and view...........O yes Merry Christmas and Happy New Year

 

[Joe8915]

I figure it out use the append querry and it work OK. Is there any way I can not have the pop ups appear such as:
"You are about to run an append query that will modify data in your table" and "You are about to append 0 row(s)".

 

[Pat Hartman]

Look for either SetWarnings or Warnings. Be VERY careful to turn warnings back on immediately after running the append query. If you leave them turned off, Access will not prompt you to save objects you have modified and will discard your design changes. I always turn the Hourglass on when I turn warnings off so I have a visual clue that they are off should I ever forget to turn them back on. Then turn the Hourglass off after you turn warnings back on.

PS - macro questions are best posted in the macro forum.

 

[Joe8915]

Pat, thanks for the hot tip. You are so correct I should have posted this in the macro forum.

I have a few issues that I am trying to resolve. I am using the append query, which is working ok, but just trying to make it a little more user friendly…………. Less clicking and typing.

I will attach the db and can see a little more clearly on what is going on.

1st issue
Selecting “Add a Shop”
Receiving a pop up “You are about to run an append query that will modify data in your table”
Need to eliminate the pop up.Solved

2nd Issue
After typing in the value another pop up occurs “You are about to append 1 row(s)
Need to eliminate the pop up. .Solved

3rd Issue
Table appears that shows all the correct information.
Would like to bypass showing the table and going direct to the Main Form “Maintain Shops” and show the results there.

4th Issue
In order to see the latest entry I have to select the functional key “F5” to bring in the latest entry. Trying to eliminate in selecting the “F5” functional key.
I tried the refresh macro but that did not work.

5th issue
Receiving a pop up “Enter Shop code”. Is there any way I can use my com bow Box to meet the criteria “Enter Shop code”. For example if I select “bad” from my “Shop list”. I don’t want to type in the pop up “bad”

Bottom line trying to populate using shop code and bring in the other 2 fields, state and description.

Once again thanks for taking the time to read and reviewing my issues and any comments are welcome. I would also entertain any other ideas on how to accomplish same results

 

[DavidAtWork]

3rd issue, does your macro open the table you want to bypass showing, if so use the Close command after appending new records, then add another command to open the Main Form “Maintain Shops”
4th issue, "In order to see the latest entry", do you mean you want to display the newly appended data and go to the last entry?, if so can you not sort this table in Descending order (sort by autonumber PK or a timestamp field) and just refresh/requery or in your macro use the GoToRecord method and First/Last
5th issue, just point fields in your query's criteria line at each of the relevant combo box(es)

David

 

[Joe8915]

Dave, thanks for the reply and comments.
The 3rd issue is not running on a macro, it when I run the append query then the table opens automatically
4th issue, I mean I would like to see All the entries including the last I entered, but not using the “F5” functional key on key board. I could not make the refresh to work, I will give another look and see where the problem is.
5th Issue, just might not be possible to do. I will re-think that problem again.
Dave if have the time , review the attach db and can see where the issues are.

Once again thanks for taking the time to review and make comments.

 

[DavidAtWork]

"The 3rd issue is not running on a macro, it when I run the append query then the table opens automatically"
are you using VB to run this query, if so change the mode from:
DoCmd.OpenQuery "appendQuery", acViewPreview
to:
DoCmd.OpenQuery "appendQuery", acViewNormal