How do I create a "run query" macro?

[Guest]

I would like to run an update query immediately after entering a record via a
form. The form has a 'save' button.

How can I create a macro to run the update query when I save the record entry?

thanks

Pat

 

[Steve Schapel]

Pat,

I think you could just make an Update Query to do what you want, and
then add an OpenQuery action to the macro on your 'Save' button to
runthe update. I think you would have a Criteria in the primary key
field of the table that points to the current record on the form, using
syntax like...
[Forms]![NameOfForm]![YourID]

Just curious, Pat... This is a very unusual thing to do. You have just
entered a new record, and then you immediately want to update it? Can
you say what this is all about?

 

[Guest]

Hi Steve.

I am taking mulitple fields from one table (records entered by a form) and
through an update query that concantenates the data into two fields (one
English, one French), stores that data in a second table. The reason for
this is to eliminate time and data entry errors. My client will be entering
url data in the form. Some of the entries could be long, especially the
French, lots of room for error.

There are static fields that dont change, so the form is set up with default
values that the user doesnt see. The final output, after running through the
update query, is a string of html that a web coder can just grab from the
second table and paste into a web document.

I wanted the second table to update automatically (through the update query)
because I don't want the client accessing that query. That is why I was
afraid to use an OpenQuery macro.

Does this make sense now? Hopefully it can be done.

thanks

Pat

Pat,

I think you could just make an Update Query to do what you want, and
then add an OpenQuery action to the macro on your 'Save' button to
runthe update. I think you would have a Criteria in the primary key
field of the table that points to the current record on the form, using
syntax like...
[Forms]![NameOfForm]![YourID]

Just curious, Pat... This is a very unusual thing to do. You have just
entered a new record, and then you immediately want to update it? Can
you say what this is all about?

--
Steve Schapel, Microsoft Access MVP

I would like to run an update query immediately after entering a record via a
form. The form has a 'save' button.

How can I create a macro to run the update query when I save the record entry?

thanks

Pat

 

[Steve Schapel]

Pat,

Thanks for the further explanation.

Yes, it can be done. Just as I mentioned before. Mind you, on the
basis of what you have told us, I would expect it to be an Append Query
rather than an Update Query that you require. An Update Query would
imply altering the values of an already existing record in the second
table, whereas it sounds like you want to add a record to the second
table, based on the newly entered record on the form. Either way, I do
not understand your comment about being afraid to use a macro with
OpenQuery - this is exactly the applicable way to do it.

--
Steve Schapel, Microsoft Access MVP

Hi Steve.

I am taking mulitple fields from one table (records entered by a form) and
through an update query that concantenates the data into two fields (one
English, one French), stores that data in a second table. The reason for
this is to eliminate time and data entry errors. My client will be entering
url data in the form. Some of the entries could be long, especially the
French, lots of room for error.

There are static fields that dont change, so the form is set up with default
values that the user doesnt see. The final output, after running through the
update query, is a string of html that a web coder can just grab from the
second table and paste into a web document.

I wanted the second table to update automatically (through the update query)
because I don't want the client accessing that query. That is why I was
afraid to use an OpenQuery macro.

Does this make sense now? Hopefully it can be done.

thanks

Pat

Pat,

I think you could just make an Update Query to do what you want, and
then add an OpenQuery action to the macro on your 'Save' button to
runthe update. I think you would have a Criteria in the primary key
field of the table that points to the current record on the form, using
syntax like...
[Forms]![NameOfForm]![YourID]

Just curious, Pat... This is a very unusual thing to do. You have just
entered a new record, and then you immediately want to update it? Can
you say what this is all about?

--
Steve Schapel, Microsoft Access MVP

I would like to run an update query immediately after entering a record via a
form. The form has a 'save' button.

How can I create a macro to run the update query when I save the record entry?

thanks

Pat

 

[Guest]

Hi Steve. I didn't think of using an append query...that might just be the
solution. About the open query macro, I didn't want the client to be able to
open that query. I will change the query to an append and see how that works/
Thanks very much.

Pat,

Thanks for the further explanation.

Yes, it can be done. Just as I mentioned before. Mind you, on the
basis of what you have told us, I would expect it to be an Append Query
rather than an Update Query that you require. An Update Query would
imply altering the values of an already existing record in the second
table, whereas it sounds like you want to add a record to the second
table, based on the newly entered record on the form. Either way, I do
not understand your comment about being afraid to use a macro with
OpenQuery - this is exactly the applicable way to do it.

--
Steve Schapel, Microsoft Access MVP

Hi Steve.

I am taking mulitple fields from one table (records entered by a form) and
through an update query that concantenates the data into two fields (one
English, one French), stores that data in a second table. The reason for
this is to eliminate time and data entry errors. My client will be entering
url data in the form. Some of the entries could be long, especially the
French, lots of room for error.

There are static fields that dont change, so the form is set up with default
values that the user doesnt see. The final output, after running through the
update query, is a string of html that a web coder can just grab from the
second table and paste into a web document.

I wanted the second table to update automatically (through the update query)
because I don't want the client accessing that query. That is why I was
afraid to use an OpenQuery macro.

Does this make sense now? Hopefully it can be done.

thanks

Pat

Pat,

I think you could just make an Update Query to do what you want, and
then add an OpenQuery action to the macro on your 'Save' button to
runthe update. I think you would have a Criteria in the primary key
field of the table that points to the current record on the form, using
syntax like...
[Forms]![NameOfForm]![YourID]

Just curious, Pat... This is a very unusual thing to do. You have just
entered a new record, and then you immediately want to update it? Can
you say what this is all about?

--
Steve Schapel, Microsoft Access MVP

Pat wrote:
I would like to run an update query immediately after entering a record via a
form. The form has a 'save' button.

How can I create a macro to run the update query when I save the record entry?

thanks

Pat

 

[Steve Schapel]

Pat,

The normal usage of an OpenQuery macro is to run an Action Query (e.g.
Append Query, Update Query, Make-Table Query). In which case, this does
not expose your data to the user. I suppose an OpenQuery macro applied
to a Select Query would open the query datasheet, and I agree that this
is inadvisable.