Append and delete query through marco

[Guest]

I have a table called landlord and archive of the landlord table. I created
append query to retrieve one record from archive to landlord through using
[Please enter landlord ID] in landlord ID field criteria and a delete query
to delete that record from the archive.

Next step create a marco to open both the append (first) and delete query
(second) to prompt me for the ID to be enter, however, I want query or macro
to use the previous ID number entered into append critera into delete query
criteria therefore do not need to enter the number again into the prompt,
only once.

Simply I want the prompt appearing once when the marco executed for the ID
to be entered for both append and delete, not twice.

 

[Guest]

Thank you, it works.

I would do this using a form, have a text box for the number and a command
button that will run your macro. then all you would need to do is set the
criteria of your queries to the text box in your form, make sure that u do
not close the form on the macro before you run the queries or you will have
an error.

eg. [forms]![Myform]![mytextbox]

This whole process could work better and faster using VB.

Hth

Stu

I have a table called landlord and archive of the landlord table. I created
append query to retrieve one record from archive to landlord through using
[Please enter landlord ID] in landlord ID field criteria and a delete query
to delete that record from the archive.

Next step create a marco to open both the append (first) and delete query
(second) to prompt me for the ID to be enter, however, I want query or macro
to use the previous ID number entered into append critera into delete query
criteria therefore do not need to enter the number again into the prompt,
only once.

Simply I want the prompt appearing once when the marco executed for the ID
to be entered for both append and delete, not twice.

 

[Guest]

I would do this using a form, have a text box for the number and a command
button that will run your macro. then all you would need to do is set the
criteria of your queries to the text box in your form, make sure that u do
not close the form on the macro before you run the queries or you will have
an error.

eg. [forms]![Myform]![mytextbox]

This whole process could work better and faster using VB.

Hth

Stu

 

[Steve Schapel]

Stu,

Excellent advice, if I may say so. Except...

This whole process could work better and faster using VB.

.... is not correct.

 

[Guest]

Hi Steve, i am fairly new to Access and and very new to vba however, I have
used a similar process to this using vba and I found it worked extremley fast
and efficiently, it eliminated the need for 2 physical queries to be created
and also allowed me to add in error handling. therefore I felt it was
quicker and neater.

Stu

 

[Steve Schapel]

That's cool, Stu. Using "physical queries" will normally be faster
(though normally imperceptibly so) compared with running SQL in code.
And running the process in macros will not perform worse that code.
Don't get me wrong - VBA is great, and that's how I do most of my work,
it's just that it can't be claimed to perform better than macros, that's
all. Error handling is another matter, available in macros in Access
2007 but not in prior versions, so this will be important if you are
going to write trouble-prone procedures.