Full Version: Print macro help
UtterAccess Forums > Microsoft® Access > Access Forms
tryinghard
I need to print selected forms from a query report as an option from an unbound form that starts the query and previews the query results. In addition I would like to limit the prints to the last 3 occurrences. The database is a Quality Control tool tracking Defective Material Records (DMR's) and Corrective Action Records (CAR's). The query now finds all DMR's and CAR's associated with a specific part number.
quest4
I just finsihed one of those beasts. I cover Material Return, Problem Definitons and Corrective Actions. I used oe number, QCANo to control the whole thing. cmdButtons would open the forms and cmdButtons at the bottom could print the reports. A weekly report and graph of the new actions is e-mailed out weekly. It is very vague on what you currently have setup. Before I can do any more I need to know more about the general setup. As far as the last three occurances report, I would leave that in a stand-alone mode, kind of like my weeekly report. hth.

Edited by: quest4 on Wed Nov 24 12:49:16 EST 2004.
Edited by: quest4 on Wed Nov 24 12:49:40 EST 2004.
tryinghard
I am sorry but I don't understand. I tried to search Help for "oe number" and QCANo" and got nowhere. I am a real novice with no training at all. I have been crawling along and should be taking some classes somewhere but I have little time to devote. The data base I have is crude at best. No relationships and all in one table. It has been doing the job but as I am asked to increase it's performance, I have more and more problems. My query is giving me the results of ALL DMR's and CAR's ever written against the part number. My boss wants the forms printed to go with new jobs that come in so processing can be aware of our previous difficulties. This would be done by the office manager from a different computer and she needs things very simple. Is it possible for you to send me a sample I could look at? Thanks for your speedy reply
quest4
Sorry oe should have been one, nimble fingers. QCANo is my name for my PKey, and the controler of all that goes on in the dbase. No problem, we all started that way here, and as for classes, I took them and they really weren't much help. I am going to give you a site which is excellent for reference info, and do keep it:
http://www.microsoft-accesssolutions.co.UK...-contents.htmAs for my dbase it is way to big to post, 5.4MB and no data in it. I do have a strip out version of how to build the maintaince table and a copy of the mains. The main form and subform have tons of code in them and they work with ODBC linked tables, in our SQL dbase on the sever, so there is no way you could open it, it would error out on you everytime. As for the queries it is just wrong criteria, correct that and awy you go. A thoguht for you here. I asked my boss and he said do it as long as I get the desired end results, Can you sa\tart from scratch, a new dbase, with no data, in other words the old data would remain in the old dbase. Is this al clear? hth.
Edited by: quest4 on Wed Nov 24 12:50:08 EST 2004.
tryinghard
I may need to look at creating a new database but I am hesitant until I learn enough to create and define all the needed associations. I look forward to that! Thanks for the help and I have saved the link. If I pasted a macro in a post could you tell me if there are any statements I could alter to possibly get the command to print the forms specified in the query instead of all the DMR forms in the database?
quest4
I will do my best, I am a little weak on them I rarely you them, but there are a lot of members here who probably do. You will probably soon get away from them yourself. Here is and iron clad rule for you: Make sure the you have no spaces or MS specal characters in your names. Also do not use special words either, like date. That will help starting from scratch. Look at the tables I sent you, also notice how they work, especially when opening and notice the cmdButtons as you click next and so on, there is a reason for that. When you are ready, I will explain. Final thought, building that monster took me two months, working on it part time, but I learned alot, and get to know Jack Cowley, he is one of the gurus around here and a good teacher, but he is not the only one, the is alot more al over the place. So post your macro, and lets take a shot at it, and make sure any queries are there also. hth.
tryinghard
Macro to print DMR forms but prints all DMR's. Need to print only DMR's result from query.
Private Sub Print_DMR_Forms_Click()
On Error GoTo Err_Print_DMR_Forms_Click
Dim stDocName As String
Dim MyForm As Form
stDocName = "DMR Form"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acForm, stDocName, True
DoCmd.PrintOut
DoCmd.SelectObject acForm, MyForm.Name, False
Exit_Print_DMR_Forms_Click:
Exit Sub
Err_Print_DMR_Forms_Click:
MsgBox Err.Description
Resume Exit_Print_DMR_Forms_Click

End Sub
Query
OOPs, Don't know how to copy query and paste it here.
Query returns DMR's and CAR's based on form input of part number
quest4
Just go into the query design mode, Right mouse in the open space near the tables and select SQL, copy the code and paste it here. Bingo. I think I may see something that is not quite right but I need the query first, I maybe see things.
quest4
Silly question, but is this the data in the form being sent to the report? Or is more data coming? Is the PartNo the PKey? Or is somthing else? Here is a sample of what I do in the OnClick event:
Dim stDocName AS String
stDocName = "rptQualityControlAction_Individual"
DoCmd.OpenReport stDocName, acNormal, , "[QCANo] =" & Me.QCANo
Remember MRANo is the PKey and controll of the controller of the dbase. This just feed the data in the form to a report to print. Just a little something for you to look at. hth.
Jack Cowley
Quest4 -
Looks like he is printing the form.... You might want to suggest using a report instead and then use the same query for the report as the form...
My uninvited 3 cents worth...
Jack
quest4
As alway Jack much appreciated, that was where I was tring to lead him to. He got stuck with an old useless dbase and his boss wants him to make it fly with the eagles. If you can maybe you can help keep and eye on him, it is similar to what you helped me make. Thanks, as usual.
Jack Cowley
Soar with the eagles, huh? Coolio! If anyone can get him to altitude it is you so hang in there... I will just stay out of the way as you are doing a great job without my meandering through the posts...
ack
quest4
OOps type-o. Should be sore, long day, it is almost Miller time. This Grasshopper will do what he can Master. Well at least he learned to start from scratch, instead of trying to patch up a lame duck. o!
Jack Cowley
LOL! A lame duck, huh? First it was eagles now it is ducks....
hope the weather is fine in Cleveland and that you have a marvelous day tomorrow!
Jack
tryinghard
Happy Thanksgiving to everyone. Your help has been amazing!
tryinghard
you guys are so far over my head. It is going to take me a while just to digest what you have written. I did discover that what I sent is not a macro at all. It figures, Ha! Guest4, I looked at your database and it looks great. Probably more things than I have been asked to provide but also more completeI wouldn't mind sending you a copy of my database for you to look at and laugh. Keep in mind that there are a lot of experiments that could be deleted. It would also help you to see where I am trying to create these print functions. The same form that inputs the part number query. Interested in seeing how badly a true beginner can mess things up?
Jack Cowley
Tryinghard -
You replied to one of my posts so I was notified. If you want Quest4 to get a post you want to Reply to one of his posts and then he will get an email notification and can respond. I should not speak for him, but you can post your db here (compact and zip, 500k max size) and I am sure Quest4 will take a look. I will notify him of your post and he will respond to you...
Jack
PS. No one better laugh at your db because everyone of us have been where you are right now!
quest4
That was only a few minor tables to show you how forms can work, of which Jack helped design. Post if you can, we will only laugh if it flies away or at least tries. It is Miller time for me now, but Jack is hanging around, read what he said previously. I have to go and let a contractor in, have a great TD and I will catch you next week and we can get everything straightened out the, or at least going in the right direction.
tryinghard
Here it is, such as it is.
Jack Cowley
You are trying to print a form and forms do not work well for printing so what you want to do is create a report that looks like your form. To create a report from your form do the following:
When looking at your list of forms right click on "DMR Form".
Click on Save As...
In the next dialog box give the report a name. Then select Report in the lower combo box.
Click OK and now you should have your report.
You do not need a query for your reports if you are going to open the reports using just the part number. Remove the criteria from the query named "Part Number" and then use this code to open your reports:
CODE
    Dim stDocName As String
    stDocName = "Part Number"
    DoCmd.OpenReport stDocName, acPreview, , "[Part Number] = '" & Me.cboPart & "'"

You can continue to use the query if you like as either willl work.
A couple of suggestions: Do not use spaces in object or field names. Use standard naming conventions for your objects. You have a Report and Query both named "Part Number". You should use: qryPartNumber and rptPartNumber. Your WorkCenter field in your table Employees is wrong as all fields should be atomic (One item per field). This data should be in a related table as well as Shift and Postion. Employee Names should be atomic too rather than first and last name in a single field. Table1 is in desparate need of normalization as well...
I hope I haven't ruined your day or your Thanksgiving and I hope that my explanation about the reports made sense.
Jack
tryinghard
I am pleased that you had time to look at it. I knew it was suffering to start with. LMAO!
Thanks
Jack Cowley
You are welcome!
Yes, there is a few minor things that I would suggest you clean up. When I looked at the Record Source for your report it just said 'Part Number'. That could have been a table or a query but if you had named it qryPartNumber then there would have been no question... Just makes it easier for you when you have lots of queries, tables, reports, etc...
Continued success with your project...
Jack
tryinghard
I will and already have started "cleaning up" tables,queries,forms and will continue. Then it may be easier to understand some of the comments better. Thanks again
quest4
Good job as usual Jack. Thanks for filling in, two more weeks of this and then we are done.
Tryinghard, let us know if you have any further questions and done be afraid to a few ideas from those table I sent you. LOL.
Jack Cowley
Thank you Quest4 and you are welcome! I trust the weather is warm and sunny in Cleveland....
ack
tryinghard
I'm not sure if you got my attachment from last week but Jack suggested that I "clean it up". He gave me some suggestions on the labeling of all the tables, Queries, etc.. I have finished cleaning and making things work again. I will send my file and maybe this will make it easier to explain to me how my goals can be better accomplished.
I am still looking to print out each DMR and CAR reported with the "frmPartNumber" query results.
Thanks for helping and hope you had a great Holiday.
quest4
Oh, I discovered a small mistake in those forms I sent you, go into the form's On Load event in the property's pull-down. This would be on the Event tab, comment out the line the line:
SetRecLocks
Commenting out a line of code means it will not execute. Just place a ' in fron of the line of code:
'SetRecLocks
The line will turn green. I did not send that function to you, so if you try and open the form, it just closes right down. I see what was done, someone tried to make the form exactly like the report and probably thought it would then printout that way. We now know that assumption was wrong. You can create the form any way you want as long as all of the data is there. On the new high flying version, you might want to check and see if the is anything scread in the old version. Look over the little forms I sent you and see if you like the way they work. Also read up on "normalization" at that website I gave you eariler. Well that should keep you amused for a few minutes. hth.
tryinghard
I believe I did what you said and the SetRecLocks did turn green but I still cannot view the forms in form view. I don't know what the problem is.
quest4
Make sure you got the right event I try it here and now it works. Here is another copy and I opened each one of them. hth.
tryinghard
I downloaded the new file and none of the forms will open in form view. I looked at the events and they are green but still will not open. Something wrong with my Access?
tryinghard
I downloaded the new file and I still cannot open the forms in form view. I looked at the events and they appear the same as the one I changed, green print. Maybe there is something wrong with my access or something missing?
quest4
Try running Compact & Repair. If that does not work, try this, create a new dbase, DB3. Import all of the tables, forms and module into it and then run Compact & Repair on the new dbase. Remember these procedures, They are what you do with corrupt dbases. hth.
tryinghard
I had to do the import to new database and the forms now appear to work. I am assuming that these forms are solely for updating the tables because when I put in what I think are existing numbers, I get a "compile error", "user defined type not defined" for each of the forms. Is that a correct assumtion?
quest4
They are what I call maintainance tables and form. Usually used in conjunction with comboboxes or for specal control , like the frmAuthorizedUsers. The tblAutoNumbering works with the module and controls the numbering of the main table and its' subtable, which is the work horse and the only real relationship in the dbase. I sent thiose along to give you an idea on how to make your dbase work, one pkey controls everything, in my case it is the QCANo. No separate numbers for DMR and CAR. The main form and table are for information on the incident. The subformand subtable are for parts involved in the incident. QCTypes control the way things will look. It can handle internal issues and returns, as well as complaints. It can link into your business dbase thru ODBC, like ours does. It can e-mail notices, graphs and reports. It can archive old records, but for the life of me I cvan't get it to serve me a cup of coffee in the morning. You can see what is possible, now decide what is right for your situation. Draw it out or use a flo chart, hind out what can go and can you start from scratch like I did. We did keep the old dbase, but in another month or so no one will even use it. Well does this seem claer enough to you? Did I give you enough to think about? hth.
PS Check to see if these references are check in your dbase:
Visual Basic for Apps
MS Access 9.0 Object Library
OLE Automation
MS ActiveX Data Objects 2.1 Library
MS DAO 3.6 Object Library
If you want to eventually use e-mail:
MS CDO 1.21 Library
MS CDO for Windows 2000 Library
You will find the references in the VB editor in the tools pull-down menu.
tryinghard
Thank you again. Is you database accessed from a switchboard? I know there is a lot more to your database than what you sent and I am thinking you have a switchboard where the user can choose what action they are going to take such as with the forms you sent. Are you also using a form with a subform for inputting new data? Are you using the Master field and Child field links at all?
I have already started creating a new database by creating tables trying to reduce the amount of information stored in the main table. No data, just tables. I am truly confused about how the data will be tied together. I made many of the same tables I had and a few more such as I had 5 sections for 5 items. Each had the same type of data but for different conditions. I should be able to use a form with a subform to input the all the required data.
I certainly have a major task ahead of me and my mind is already struggling with HOW! Thanks for your help again and maybe by the end of this I will somehow start to think in the right format that will make these concepts easier for me to digest.
PS; I have all the right references checked in the reference libraries.
quest4
Switchboard, yes, but not the Access Switvhboard, I created a form and added some hyperlinks to what I wanted and that was that, it is much more stable and I can add code to it, but you can also use cmdButtons if you like. That is what runs the forms and a few of the reports. In the main table have a field like lets say QCNo and the is now the main tables PKey, and that is the only number we will use for the DMR and CAR, In the main table just have the info about this action, DateIssued and DateCompleted and CustomerNo and so on and so forth. In the other table, I like to call it a subtable, it makes it easier to ID when you get a lot of table, in there I put the QCNo in the subtable, along with the PartNo and everything related to the Part and problem. Then I go into The Tools pull-down menu and pick relationships. Ithe click on QCNo in the main table and pull it over to the subtableQCNo, a line or link will appear. Right mouse the link and select edit and a form opens. Check the referential integrety and the cascade update and delete. You should now have a one-to-many relationship established. This is what makes things happen. Read Help on one-to-many relationships to get a better understanding, of how relationships work. Oh, in the table, main QCNo in the PKey and in the subtable QCNo should be a long integer. Personally I would make both QCNo long integers and not use the Access anutonumber and use the autonumber function I sent you. Access autonumber can at best be touchy. I will explain later. hth.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.