Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Print single Record

Posted on 2014-03-31
18
Medium Priority
?
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 4
18 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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 39

Assisted Solution

by:PatHartman
PatHartman earned 250 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 39

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 85
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 85
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 39

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 39

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 85
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 39

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

604 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