Embedding a macro in a Query

THWatson · May 19, 2000

My main database has a field called Envelope #. These are for donations. Some people in the main database have Envelope #s and some don't. Periodically a donation is made by a person who doesn't currently have a number, and we need to assign a new Envelope # to that person. I have a Query that calls up existing Envelope #'s, and the name of the person to whom that number is assigned. This helps the data entry person know what numbers can still be assigned. But...I discovered that she would then assign a new number and key in the name information right in that Query. This, because the fields are linked by relationship, caused a double entry back in the main database. What I would like to do is embed a Message Box macro in a Query, so that every time the Query is run this message will come up, warning the person who enters data not to assign a new number in the Query but only in the main database. But I can't seem to get a Macro to work in a Query. Anybody have a solution? Thanks. Tom

DougP · May 19, 2000

I would do it slightly differnt. Create a form where all of the emplyees are at. Find the one who you are working with. When they come up have a meesage saying "NO ID" etc. then have it say Press a button called "Get next ID" or "Make an ID" The ID counter would be keep in a separate table. So as a new one was added it would incrament to the next number automatically. (see code below) Now when the user is proimpted to Click the Make new ID button it would go to the ID table get the number Add one to it put the new number back in the table for next time. Then also put the current number in the text box on the form called EmployeeID. OK -------------------- Add one to number and re-save ---------     Dim MyDB As Database, MySet As Recordset     Set MyDB = currentdb     Set MySet = MyDB.OpenRecordset("Last-RA-Number&quot

MySet.MoveFirst         MySet.Edit         Temp1 = MySet.Fields("Last_RA_Number&quot

' Note Temp1 contains the number         Temp2 = Temp1 + 1         MySet.Fields("Last_RA_Number&quot

= Temp2         MySet.Update     MySet.Close     MyDB.Close DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

THWatson · May 19, 2000

Thanks, Doug The only difficulty is that this solution won't quite work. The problem is that we have 3 Envelope # series. One series runs from 1 through 400 - these are the main donors. The second series runs from 700 through 799 - these are pre-authorized remittance donors. The third series is 900 and up. These numbers are for very occasional (maybe even only one time) donors. The second and third groups would be no problem to handle the way you suggested, because new Envelope #'s are applied in direct sequence. The difficulty is the first group, those with numbers from 1 through 400. Of these 400, about 275 are applied, and they are not in exact sequence, because some of them have had the same Envelope # for several years. Other numbers have been removed - due to moving away, death, whatever - so there are holes in the sequences. What I plan to do for years 2001 is to change all these numbers so that everything is consecutive, and then your solution will work...but at the moment I am stuck with what we have...so I was hoping I could make this Warning (Message Box warning) that ran when the query was opened. But maybe you can't run a macro in a query, because I can't find reference to that in any manual. Tom

THWatson · May 19, 2000

Doug In my continuing pursuit of running a macro out of a query, here's what I did. There are 3 existing fields that the query calls up. They are LastName FirstNames Envelope # In a fourth field spot in the query design, I added the following Expr1: [DoCmd].[RunMacro]![Macro1] Macro1 runs a Message Box that issues a beep and a message that says "Don't enter envelope #'s here. Do it in the main database!" This works...but it repeats 3 times...in other words, running 4 times altogether. Why? Is there a way to fix it? Tom

DougP · May 20, 2000

If you do everyting with code you have Complete control. Now I may not understand completely what you are doing. Typing on a keyboard is Not the best way to communicate. But after using Access for 7 years I know whatever it is it, it can be done in VBA code. With No Messages, No Prompts just click a button and stand back. I would start out by doing one step at at time and getting it to work perfectly. Then go to the next step. It may have to make 4 loops too but it will do it in the background. And VBA code executes in the blink of an eye. Don't try to write the whole thing and then debug a mess. Learing VBA will take you to the next level in Access. Mastering VBA will allow you to do thing's that other people will gasp in Amazement. DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

THWatson · May 23, 2000

Thanks, Doug All I am trying to do is give the user a message when they run this query. So what I did was create a Macro that ran a Message Box, and now I am running this Macro out of the query. The only trouble is that it's a bit annoying because it runs 4 times. Each of the first 3 times the user has to press the OK button. It works but it's far from efficient...or pretty. Do you have a book on VBA for Access that you would recommend? I have looked locally, but can't seem to find one that offers what I would like...and they are expensive. Tom

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Embedding a macro in a Query

THWatson

Technical User

DougP

MIS

THWatson

Technical User

THWatson

Technical User

DougP

MIS

THWatson

Technical User

Similar threads

Part and Inventory Search

Sponsor