Solved

Print single Record

Posted on 2014-03-31
18
336 Views
Last Modified: 2014-04-02
Hi,

Grateful for assistance.

I'm trying to carry out the following routine.

I have a database with many records in the Main Table. One of the fields is named offer. The default value for it is "yes".

However during a particular process this field for a particular record is amended to read "no" through the use of a form.

The routine I would like to offer is to
1. print out that record after the change.
2. export that amended record in csv format to a predetermined folder.

I was wondering whether this can be done in situ or whether it is preferable to make a copy of that record in a New Table, then make the adjustment to it, do the export and then update the Main Table with the amended record from this New Table.

Grateful for ideas.

I am using A03
0
Comment
Question by:PipMic
  • 9
  • 5
  • 4
18 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
Comment Utility
Do you have a report that shows the data you want? If so, and if that field is the ONLY one that would be set to  False:

DoCmd.Openreport "YourReportName", , , "YourField=True"

If there are other criteria, then you'd have to amend the WHERE argument to include those.

Regarding Exporting it:

The simplest way is to create a query that returns that record, and then export that. For example, if you have a query named "qryExport", you'd do this:

Dim qdf As DAO.QueryDef
Set qdf = Currentdb.QueryDefs("qryExport")
qdf.SQL = "SELECT * FROM YourTable WHERE YourField=True"

'/ now export the query
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport", "C:\SomeFolder\MyFile.cvs"
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
Comment Utility
Don't forget to force the record to save in the button's click event or the report/export will not show the updated value.

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

There are several ways to control criteria for reports and my choice is almost always the method suggested by Scott.  This allows you to run the entire report if you just open the report directly or to select a specific record by including a where argument.

Having to also export to Excel or a text file tilts me toward a different method and that is to reference a form field.  The query itself would include criteria that references a control on the form:

Where somefield = Forms!yourform!somefield

This will allow you to use the same query for the Excel export as you use for the report's RecordSource.

If you only ever run this report and this export from one form, what I have suggested is good enough.  If you need the flexibility to run the report/export from multiple places then locking the criteria to a specific form will not work.  So, in applications where I need this flexibility, I create a hidden form that has controls to hold whatever selection criteria I might need.  Each place in the app where I have a button click to run a report/export, I place the criteria value in the appropriate control of the hidden form and then all the queries refer to the hidden form.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Forms are not optimized for printing and usually give poor results as well as use entirely too much ink.  Take the time to create a query and a report.
0
 

Author Comment

by:PipMic
Comment Utility
Hi thanks,

No I dont have a report.

My idea (if it is of use) was to amend the record in a form and then have a button on the form to print that record only.

I would then have another button that would export that record in csv format to another folder.

In fact come to think about it I would like a routine that
1. Prints the record in a form as a report.
2. exports the record visible in the form as a csv file to a particular folder.

Thanks
0
 
LVL 84
Comment Utility
You don't print Forms. You print reports. You can force a form to print, but it will print everything in the current recordset, plus you have no way to control margins, etc etc.

You could create another form and print that - but why not do it the right way and create a report.
0
 
LVL 84
Comment Utility
In fact come to think about it I would like a routine that
We've given you the methods to do that, so try putting that to use and let us know if you run into troubles.
0
 

Author Comment

by:PipMic
Comment Utility
Hi,

Somehow I need to capture that record!

Grateful if someone could provide sample code.

:(
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Go back to Scott's original answer.  He showed you an OpenReport with a WHERE argument.  THAT is how you capture the record!!!!

It will look something like:

DoCmd.Openreport "YourReportName", , , "YourKey = " & Me.YourKeyControl


You need to substitute your report name, your column name and your control name.  AND, if the key field is text rather than numeric, you'll need to escape the value with quotes.

DoCmd.Openreport "YourReportName", , , "YourTextKey = '" & Me.YourKeyControl & "'"
0
 

Author Comment

by:PipMic
Comment Utility
Hi,

Im sending what I have so far.

I have the query and ive isolated the record in the report.

I could always make a table based on that query again and then export the record.

Sounds a bit messy but I could always try that!!
test-record.mdb
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:PipMic
Comment Utility
0
 

Author Comment

by:PipMic
Comment Utility
ive now managed this with Form1

How can I make the CSV with what I have?
test-record.mdb
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Please go back and read  -- Posted on 2014-03-31 at 15:39:46 ID: 39967526 -- where I explained how to do it.  Since the Transfer methods do not offer a WHERE argument, you have to include criteria in the query itself.
0
 

Author Comment

by:PipMic
Comment Utility
in 39967526 you suggest that i have to save the record first.... with the microsoft method i can print the record once the amendment is done and then print the record.....

In my test db, once I close the form the record would be saved with the amendment done!!

Anyways Im still trying to understand 39967526. Ill give it another bash later. Thanks anyway.
0
 
LVL 84
Comment Utility
just found this....
Which is exactly what Pat and I have been trying to tell you. Honestly, it's like sometimes people just don't believe us!!

Have you tried the TransferSpreadsheet, or perhaps TransferText, methods as we suggested? The method I suggested is very straight forward, as is the hidden form method suggested by Pat.
0
 

Author Comment

by:PipMic
Comment Utility
Scott, no need to get like that.

I do believe you but sometimes its not that easy to follow and if I'm being told to try out the methods (which I haven't understood) then I need to do other research and get back.

But thanks anyway. By the way I haven't understood the hidden form thing. But no worries , I'll try some other way. I wouldn't want to annoy anyone.

 (Even if I do pay for a service!!!!)
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You don't need the hidden form to do the task at hand.  That was an idea for a larger scope of output options.  I was trying to tell you what I do to handle various custom outputs.

You can't use any of the export methods without first saving the record.  All the export methods go back to the table either directly or via a query to retrieve the data they are exporting.  There is nothing built in that will export the current record of a form.

If you wanted to do this without saving the record first, you could not use the built-in export methods but you could write code yourself to open a text file and write to it (look up Open, Print, and Write to see examples).  Or you could use OLE automation code to open Excel and populate a workbook directly.

All in all, I'd save the record and go with the one-line export based on a query that gets its criteria from the form you are exporting from.

You may pay for a service but we do not get paid to provide it.
0
 

Author Comment

by:PipMic
Comment Utility
Well I thought this forum provided a service at a fee to the customer i.e. me

If you prefer not reply you are more than well in your right not to answer any of my questions especially if you are not getting paid for it. Even so the fact that you don't get paid does not give you the right to answer back in that manner.

Anyway, I did not not start this silly spat, I merely asked a question. There was no need for anyone to get stroppy with me just because I researched part of the answer. I was being asked to try a method which frankly was not well explained (in my view)  so I came back to the forum with the MS link and a sample database that I tried out. Hoping in all honesty for further feedback.

That was all. Oh and by the way I did go back and save the record (thanks for the tip) and also found a way to automate the whole process including the export.
0
 

Author Closing Comment

by:PipMic
Comment Utility
No comment
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now