November 23, 2011 at 8:35 am
And now for the public flogging moment of The Thread as I post a semi-technical question that is sure to get me lynched.
We have a Word 2003 document pointing to an Access 2003 database / query. It pulls records that have been marked with a -1 flag. What I'd like to do, but I don't know if it's possible, is put a macro into Word that, as soon as the records are printed, updates the Access database and changes the -1 to 0.
This is a user db only being supported until we upgrade them to a proper database (it's a long project). This change is in response to a user annoyance factor in which the flag isn't getting updated by the users and the mail merge is pulling old data along with the new data. Since the Access db will be decommissioned eventually, I don't want to put too much effort into this, but I do need to at least look at possible solutions.
Thoughts?
November 23, 2011 at 8:39 am
Brandie Tarvin (11/23/2011)
And now for the public flogging moment of The Thread as I post a semi-technical question that is sure to get me lynched.We have a Word 2003 document pointing to an Access 2003 database / query. It pulls records that have been marked with a -1 flag. What I'd like to do, but I don't know if it's possible, is put a macro into Word that, as soon as the records are printed, updates the Access database and changes the -1 to 0.
This is a user db only being supported until we upgrade them to a proper database (it's a long project). This change is in response to a user annoyance factor in which the flag isn't getting updated by the users and the mail merge is pulling old data along with the new data. Since the Access db will be decommissioned eventually, I don't want to put too much effort into this, but I do need to at least look at possible solutions.
Thoughts?
Access is a spreadsheet, right? It certainly isn't a database:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2011 at 8:40 am
You can write Macros for Word? I really had no clue that it could be done. I thought only excel supports that. I learn something new everyday.
-Roy
November 23, 2011 at 8:54 am
Jim Murphy (11/22/2011)
The other organizer of our SQLSaturday in Austin (#97) just compiled the speaker feedback evals. I came in 3rd overall! I'm pretty pleased since it was my first time presenting at a SQLSaturday.http://sqlserverio.com/2011/11/22/sqlsaturday-97-speaker-recap/
Congrats. I can't say I'm surprised that you did well. You definitely have the personality and skills to be a top-notch presenter.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2011 at 8:59 am
Brandie Tarvin (11/23/2011)
And now for the public flogging moment of The Thread as I post a semi-technical question that is sure to get me lynched.We have a Word 2003 document pointing to an Access 2003 database / query. It pulls records that have been marked with a -1 flag. What I'd like to do, but I don't know if it's possible, is put a macro into Word that, as soon as the records are printed, updates the Access database and changes the -1 to 0.
This is a user db only being supported until we upgrade them to a proper database (it's a long project). This change is in response to a user annoyance factor in which the flag isn't getting updated by the users and the mail merge is pulling old data along with the new data. Since the Access db will be decommissioned eventually, I don't want to put too much effort into this, but I do need to at least look at possible solutions.
Thoughts?
I used to write pretty complex VBA macros for WordPerfect. In the 90s. Would have been easy for me to suggest something at that time, since I did manage some mail-merge lists through that kind of integration.
Might be easier to make the query mark the rows, rather than a macro in the document. But since modern implementations of Word allow .NET integration, what you want has got to be possible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2011 at 9:14 am
Brandie Tarvin (11/23/2011)
And now for the public flogging moment of The Thread as I post a semi-technical question that is sure to get me lynched.We have a Word 2003 document pointing to an Access 2003 database / query. It pulls records that have been marked with a -1 flag. What I'd like to do, but I don't know if it's possible, is put a macro into Word that, as soon as the records are printed, updates the Access database and changes the -1 to 0.
This is a user db only being supported until we upgrade them to a proper database (it's a long project). This change is in response to a user annoyance factor in which the flag isn't getting updated by the users and the mail merge is pulling old data along with the new data. Since the Access db will be decommissioned eventually, I don't want to put too much effort into this, but I do need to at least look at possible solutions.
Thoughts?
Anything is possible.
It might depend on a couple of things.
As you pull each record in to the merge document, couldn't you just execute an update query for that record in conjuction with printing?
Or depending on how records are added / flagged, just give the user some functionality that would allow them to run a delete query where they would be prompted for some parameter to limit this as needed.
Say the records have a row number, and they know the max row number they are pulling in for the merge.
November 23, 2011 at 10:21 am
Brandie Tarvin (11/23/2011)
And now for the public flogging moment of The Thread as I post a semi-technical question that is sure to get me lynched.We have a Word 2003 document pointing to an Access 2003 database / query. It pulls records that have been marked with a -1 flag. What I'd like to do, but I don't know if it's possible, is put a macro into Word that, as soon as the records are printed, updates the Access database and changes the -1 to 0.
This is a user db only being supported until we upgrade them to a proper database (it's a long project). This change is in response to a user annoyance factor in which the flag isn't getting updated by the users and the mail merge is pulling old data along with the new data. Since the Access db will be decommissioned eventually, I don't want to put too much effort into this, but I do need to at least look at possible solutions.
Thoughts?
If you are allowed to change the database I would add an extract datetime column and update it along with the flag it in the proc followed by extract using the date.
This allows extracting the data again using the datetime or resetting it with the flag to allow proc to update/extract again.
Ooops! just saw 'Access' mentioned, so the proc idea is no good but a similar approach using querys might be possible.
Far away is close at hand in the images of elsewhere.
Anon.
November 23, 2011 at 10:24 am
Slow day (on the thread) so I figured I would share here:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2011 at 10:28 am
Jason, I hope that after this you will get a peaceful, really warm Thanksgiving with your family.
November 23, 2011 at 10:48 am
Revenant (11/23/2011)
Jason, I hope that after this you will get a peaceful, really warm Thanksgiving with your family.
He will as long as there aren't any almonds. 😛
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2011 at 11:01 am
Jack Corbett (11/23/2011)
Revenant (11/23/2011)
Jason, I hope that after this you will get a peaceful, really warm Thanksgiving with your family.He will as long as there aren't any almonds. 😛
I'm thinking almonds are going to be kept at a distance for a looooong time.
Thanks Revenant and Jack.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2011 at 12:36 pm
Roy Ernest (11/23/2011)
You can write Macros for Word? I really had no clue that it could be done. I thought only excel supports that. I learn something new everyday.
Ethan Frome Word Virus.....:-P
The whole Office Suite can support VBA code.
Can be good and bad.
November 23, 2011 at 12:48 pm
Brandie Tarvin (11/22/2011)
Kit G (11/22/2011)
But in Houston, and in Florida to a slightly lesser degree, 45-55 is the speed you go to merge into traffic going 65-75. Drives me nuts!Preach it, Sister! - The Choir
Seems to happen frequently here in Colorado as well. Strange considering how many transplants come for California.
November 23, 2011 at 1:06 pm
@Brandie,this is somewhat easy to do.
I don't know how you do the merge at the moment or when you really want to set the print flag.
You can simply do a button in a menu that updates the flags, or maybe do it in the print event. Simple update statement with parameter.
I think this needs to be done RBAR. Don't set the flag untill the print is successful. Let me know if you need more help.
November 23, 2011 at 7:13 pm
Jim Murphy (11/22/2011)
The other organizer of our SQLSaturday in Austin (#97) just compiled the speaker feedback evals. I came in 3rd overall! I'm pretty pleased since it was my first time presenting at a SQLSaturday.http://sqlserverio.com/2011/11/22/sqlsaturday-97-speaker-recap/
Very cool! Nicely done, Mr. Murphy!
--Jeff Moden
Viewing 15 posts - 32,146 through 32,160 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply