I have a database using several forms to maintain some training information on. All of the tables are driven by the social security number (only unique field per individual). I want to generate a transcript from one of the open forms for the individual record I am working with, using the SSN that is displayed on the form. The cmd button calls a macro that executes a series of queries to build the transcript prior to executing the report.

My problem is, I am stuck with the syntax or process in getting the value of the SSN in the listbox/textbox that needs to drive the macro/query process. Can somebody point me in a direction? I am attaching a copy of the database so people can see where I am. If there is a better way to accomplish what I am trying to do, I am certainly open to suggestions.

Thanks.

I'd love to help if I can (even if it does look like you're Army), but I may not be understanding the issue entirely.

First, I don't see a command button on frmMain which is kind of where I might expect it.

If the problem is just a formatting one, then the solution should be easy. One possible source of the problem is that it looks like you probably used the wizard to set up the Input Mask for the field fldSSN in the table tblMain. Right now, it's set to 000\-00\-0000;0;_ If, however, you change it to 000-00-0000;;_ then it will not store the hyphens (so entering 123-45-6789 will save it as 123456789). That might eliminate formatting problems. Of course, code can be used to generate or remove hyphens as appropriate.

Perhaps if you tell me which button you click on I can click it and see the exact error you're getting, but the only button I see is on the form frmForces and there is no macro or code associated with it.

Actually, I am in State Military, Tennessee to be exact.

I started using the switchboard wizard and created an option to generate transcripts for an entire state. The process would be the same for the SSN (It didn't even occur to me the SSN mask might be my problem).

You are correct, the individual transcript will be drawn from the individual record (I hadn't gotten that far yet).

The current macro I have set up is for the state level set. If you run the switchboard and select the option for transcript, you should see the command button for generating the state transcripts. In this case, my single record for GSDF. I can't seem to get GSDF to filter to the macro series of queries.

Hmm, maybe I don't have the same copy of the DB as you. When I run the switchboard, the only 2 options are "Individual Training Records" and "Add Additional Other Trainnig Course." Sorry if I'm misunderstanding you!

You have the same one. I just checked. Executing frmForces should show the button for generating the transcripts. The cmd Button properties should show it calling the macro mcrTranscript in the event procedure. I had a second dummy record in using a different "force". But when the macro would run, it would pull records for all forces and not limit it to the force selected in the form list box. I pulled "my" record out of the database prior to uploading since I didn't want my SSN out public. You will need to create a second dummy record in tblMain using a different "force" to see the problem.

Okay, must be Monday that's got me all confused. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

I guess part of the problem was that nothing on the switchboard opened frmForces and the button on frmForces was not linked to the macro--it was linked to VB code but with nothing in it. Anyway, having linked the macro and added a dummy record under the Alaska State Defense Force, when I run it with the ASDF selected I get both my dummy record (which I would expect) and the other record that was already in there under the GSDF (which I would not expect if I was printing a transcript of everyone in the ASDF only).

If you trace through all of the queries it appears that qryCourse1 is the query that adds the suspect record so you may want to inspect that query.

Overall, though, I can't help but wonder if perhaps there is a cleaner, easier way to do business. Perhaps I do not understand the purpose of all the queries, and I will admit I've always preferred VB over macros.

If the final output is a report, then why not just send the criteria to the report and let the report handle any formatting that you need? You could use the macro for OpenReport and fill in the Where condition based on the input (for example, fldForce = Forms("frmForces").fldForce).

Well, hopefully that helps some, though perhaps I continue to spin both our wheels (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)