Solved

Print single Record

Posted on 2014-03-31
18
339 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
ID: 39967451
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 35

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 39967526
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 35

Expert Comment

by:PatHartman
ID: 39967531
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:PipMic
ID: 39967523
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
ID: 39967545
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
ID: 39967549
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
ID: 39967552
Hi,

Somehow I need to capture that record!

Grateful if someone could provide sample code.

:(
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39967646
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
ID: 39967733
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
 

Author Comment

by:PipMic
ID: 39967827
0
 

Author Comment

by:PipMic
ID: 39967859
ive now managed this with Form1

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

Expert Comment

by:PatHartman
ID: 39967942
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
ID: 39968489
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
ID: 39968904
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
ID: 39968978
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 35

Expert Comment

by:PatHartman
ID: 39969611
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
ID: 39969849
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
ID: 39972491
No comment
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Data Type Mismatch in Criteria - Access 6 28
How can I Flash a mandatory field in Access Form? 13 45
Exporting Access Tables as CSV 3 23
Calculation in Access 5 22
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

813 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

10 Experts available now in Live!

Get 1:1 Help Now