UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Save To Pdf Macro Problem, Access 2016    
 
   
noobdba
post Jun 21 2019, 02:09 PM
Post#1



Posts: 19
Joined: 21-June 19



I'm building a database where the user can save a form record as a pdf in the form of a macro. Yet it's saving all records instead of just one. Is there anyway to do one record per page?

Thanks for reading!
Go to the top of the page
 
MadPiet
post Jun 21 2019, 02:19 PM
Post#2



Posts: 3,204
Joined: 27-February 09



Why not build a report, and pass that ID of the current record in the Open event, and then save that to PDF?
Go to the top of the page
 
noobdba
post Jun 21 2019, 02:30 PM
Post#3



Posts: 19
Joined: 21-June 19



I'm unfamiliar with building a report from a form or an Open event (at least, I've never created an open event).
Go to the top of the page
 
MadPiet
post Jun 21 2019, 02:36 PM
Post#4



Posts: 3,204
Joined: 27-February 09



Build the report without filters. Pass the filters when you open the report.

CODE
Public Sub OpenFilteredReport()
    Dim strFilter As String
    strFilter = "[CustomerCity]='Houston'"   'or you could call a function here that built the filter dynamically
    DoCmd.OpenReport "MyReport", acViewPreview, , strFilter, acWindowNormal
End Sub

Go to the top of the page
 
noobdba
post Jun 21 2019, 02:48 PM
Post#5



Posts: 19
Joined: 21-June 19



*sigh* I was worried you'd give me code. The Access class didn't really cover vba or understand the syntax or how to use it.
Go to the top of the page
 
GroverParkGeorge
post Jun 22 2019, 11:02 AM
Post#6


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

Actually, you can probably do the same thing with a macro. Unfortunately, macros have limited capabilities compared to VBA so most significant Access development depends on learning and using VBA.

The key thing here is a clear understanding of the objects involved and the process by which one creates the end product, i.e. the pdf with the data from one record.

First, data is stored in tables. So, when you create a report or use a form, what happens is that Access retrieves the data from a table or tables and presents it to the user via the form or the report. That means, in part, that the WAY you present the data in any given form or report is independent of the data content. You don't actually create a pdf from the "form"; you select one record using the form and tell the report to use that record.

The report is designed exactly the way you want the pdf to appear, placement and layout of controls and labels for the data, any colors or images needed and so on.

Pieter has shown you one way to do this with a procedure in VBA. There will be a more detailed approach because that example has one value hard-coded into the filter "Houston". You'll need to do that dynamically in the form. But the rest is appropriate.

I don't use macros unless it's absolutely unavoidable, but I think you should be able to do this using one because the steps aren't overly complicated. On the other hand, if you have no experience with VBA, maybe we ought to verify your macro building experience?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jun 22 2019, 02:35 PM
Post#7


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


Well, in the absence of a followup reply, I went ahead and cobbled together a little demo. It's rudimentary, of course, but it shows you a couple of things to consider in your design.


Attached File  ReporttoPDFDemo.zip ( 36.83K )Number of downloads: 5

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
hgraham
post Jun 22 2019, 10:08 PM
Post#8



Posts: 9
Joined: 9-May 19



This is wonderful, thank you for posting this with the great demo file! I have a question, can this go one step further and be auto attached to an e-mail so that one could just push a button and it neatly send it off?
Go to the top of the page
 
GroverParkGeorge
post Jun 22 2019, 10:10 PM
Post#9


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


That might be a step too far for a macro, I'm afraid. However, it is quite possible to attach a pdf to an email and send it. I would definitely recommend VBA for that task, though.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
hgraham
post Jun 23 2019, 01:53 AM
Post#10



Posts: 9
Joined: 9-May 19



I thought that would be the case. I have been messing around with some VBA trying to get it to work for the past three days with no luck.
Go to the top of the page
 
noobdba
post Jun 23 2019, 05:55 AM
Post#11



Posts: 19
Joined: 21-June 19



Hello GroverParkGeorge

Thanks for the replies. Unfortunately, I wasn't at a computer most of the day. It would take me weeks, maybe months for me to learn/use VBA, so I'm left with using macros. Oh, note: I'm building a database for my friend, so bit of a time table here. I'm used to MySQl so I feel very much out of my element. I created these custom forms for my friend to use for data entry, but it sounds like I can't convert my form(s) into report(s)?
Go to the top of the page
 
GroverParkGeorge
post Jun 23 2019, 09:36 AM
Post#12


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


It's not a good idea to try to use FORMS--which are designed for on-screen interactions with data--to create REPORTS. It's certainly possible to print a form on a printer, yes. But that's usually an inadequate substitute for the appropriate tool, which is the report. Shortcuts, as they say, tend to make for longer journeys in the end.

I guess if you don't want to invest time in learning how to use the development tools available in Access, an alternative would be to hire out the work. Is that something you're in a position to do instead? Your friend?

I like to use analogies to illustrate basic concepts. What we're talking about here is somewhat akin to trying to build a shed in your backyard using only a screw driver and a pair of pliers. Maybe you can cobble something together with just forms (no reports) and macros, but it's not going to be a desirable product in the end because of the limitations inherent in those tools. They're great at the tasks for which their designed; not so good for other tasks.

Access is very good at linking to many different databases, including the Access ACE engine, SQL Server, MySQL, Oracle, Azure, and so on. The type of tables used to store your data is not the key issue in learning to use Access effectively. The key is learning to use:

a) the data layer, i.e. your tables
b) the interface layer, i.e. your forms and reports
c) the logic layer, i.e. code in macros and VBA.

Without all three, it's not possible to create a useful database application. Access is possibly unique among development tools in that one package contains all of the necessary components, whereas a database tool, like MySQL contains only the tables for storing data and the views and stored procedures for retrieving or managing that data, but nothing with which you can create an interface or interface logic.

So, the task here is to pick a strategy that offers a good chance of success. In this case, that means VBA, I'm afraid.

Again, if the task has a short deadline and your skill set is not yet advanced enough to do it, then the alternative might be to hire in additional help.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
hgraham
post Jun 23 2019, 10:02 AM
Post#13



Posts: 9
Joined: 9-May 19




You can turn a form into a report. Did you make a table before you made the form, or did you go straight to the form? Think of the table like a gas can, it holds all of the gas, to get the gas into the can we can use a funnel, the form is the funnel, if we want to see how mush gas is in the can we look at the gauge on the side of the can, the report is the gauge.

If you have not made a table yet, that is ok, but go ahead and make one now. You will need to make a column for each piece of data that you want on your report, I guess before I go any further, is this stuff that you already know? Not that I'm being lazy, but why write a long message to describe to describe a process if you already know. lol.
Go to the top of the page
 
GroverParkGeorge
post Jun 23 2019, 10:10 AM
Post#14


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


Daniel has a good article on using VBA to send email and email attachments.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jun 23 2019, 10:16 AM
Post#15


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


hGraham, I think what you mean is that you can use the SaveAs method to save a report from a form, not that you can "turn a form into a report". They are two different objects, with different purposes and different properties. Some of those properties are similar (e.g. both forms and reports have data sources), but many of those properties make forms better suited for one purpose while other properties make reports better suited for different purposes. My point is that they are not interchangeable objects.

Again, one can print forms. One can save forms as reports. But that's the kind of short cut that tends not to work out well in the long run.

One can also export reports to pdf format files. I suppose one could also export a form as a pdf format file. But in the end, knowing how to use each tool to its best advantage is the safest strategy.



--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
hgraham
post Jun 23 2019, 02:02 PM
Post#16



Posts: 9
Joined: 9-May 19



Thank you for the link! Where I was going to go with the previous post was to enter the data into the table through the form, and then generate the report from the data in the table. Is this a poor way to do this? If so I have been going about things very wrong. I have been working on a database for about 6 weeks, it has been very slow going, I have been using VBA with very limited knowledge, but it is always fun to learn something new. If I am not doing things correctly, it could account for why it is taking me so long. Thank you for your input, I, and I am sure noobdba cannot tell you how valuable you instruction/imput is. Thank you!

p.s. I hope it isn't bad form to be posting my own questions someone else's thread, if so, I apologize
This post has been edited by hgraham: Jun 23 2019, 02:06 PM
Go to the top of the page
 
GroverParkGeorge
post Jun 23 2019, 04:04 PM
Post#17


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


Right, it seems you're on the proper track.

Data is stored in tables as you say. Persisting data over time.

We interact with that data in forms. Adding, deleting and modifying individual records and fields.

We report on that data with reports. Selecting and formatting data from one or more records for display.

Three objects with three different purposes and properties.

The analogy I tried to offer above is based on the fact that we can, indeed, use objects for purposes other than what they are designed for. We can pound nails with a pair of pliers, but it's not pretty. We can print forms to paper, but it's not as effective as doing it in reports. And so on.

No problem, really with continuing a discussion, except that sometimes a new, less related question might get overlooked if it's tagged onto another discussion.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
noobdba
post Jun 24 2019, 11:16 AM
Post#18



Posts: 19
Joined: 21-June 19



I figured out my print PDF form record. At least it seems to work. See the screenshot.

I created the tables first and then the forms. I re-did the reports to have them build from the tables instead of the forms. Unfortunately, I'm not sure if I can hire an intermediate (or expert) VBA user. I will need to spend time after this is done to learn VBA. Is there a book or online tutorial you'd recommend for VBA beginners? I took a database programming class, but instead of teaching us VBA, we worked on a class project for the school. Great practical experience in normalization, data scrubbing, etc., but all I learned of VBA is that Dim is Dimension and even then--I have no idea how Dim is used.

hgraham -- It's okay to post your questions on this thread. I think it's great for people to take the opportunity to ask others for help when they're stumped on the same or similar problems.
Attached File(s)
Attached File  printpdf.PNG ( 15.77K )Number of downloads: 0
 
Go to the top of the page
 
noobdba
post Jun 24 2019, 11:17 AM
Post#19



Posts: 19
Joined: 21-June 19



GroverParkGeorge -- I am grateful you've taken your time in helping me out.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 05:13 PM