How to make a Data Report in VB formated like an Access Label Wizard? RESOLVED

[Zealot]

Could someone tell me how to get the Data Report to format the output like labels in two columns?
In Access there are label wizards that do this for me, but I'm using VB now and the way it is it fills the report page in only one column, and in the last label it chops the data into half in the first page and half in the second page.

[sertev]

As far as I know it is very hard to get the VB data report to do exactly what you want. This is because it seems to be very limited. If there is any one out there that can prove me wrong please do as it would save me allot of work.

Your best option to save many a head ache, would be to create the report in Access and then call it from vb. You can either open the report in an Access Shell an then call the print function or you can output the report to a Snapshot Viewer file and add a Snapshot Active X control to a form with the path of the saved file.


Try looking up 'OutputTo' and 'OpenReport' in the Access help to get some more information.

Hope this helps

[ralph]

Custom built recordsets are the ace in the hole

[Zealot]

Originally posted by sertev
Your best option to save many a head ache, would be to create the report in Access and then call it from vb. You can either open the report in an Access Shell an then call the print function or you can output the report to a Snapshot Viewer file and add a Snapshot Active X control to a form with the path of the saved file.


Try looking up 'OutputTo' and 'OpenReport' in the Access help to get some more information.

But sertev, I can alredy do that with this code:

Dim acc As New Access.Application
    With acc
        .OpenCurrentDatabase "C:\Database.mdb"
        DoCmd.OpenReport "ReportName", acViewNormal, "Report Filter"
        .DoCmd.Maximize
        .Visible = True
    End With

My problem is that is leaves Access open, and I can't possibly let that happen. Is there a way that I can open the Report without opening Access?

[Zealot]

Originally posted by ralph
Custom built recordsets are the ace in the hole

Ralph I need more than that to understand you. You've been helping me a lot with the Data Reports, and I thank you for that. But still, it's not enough, for I'm stuck in this project; my first professional project (as you might have been suspicious already ) wich should be presented next week to the client!

For example, I would like to put in the labels "Mr." or "Mrs." before the name of the person by reading the gender field. The problem is that the rptTextBox must have a different datamember than the rest of the textboxes, but it seems that a Data Report's Controls only allows binding to 1 Data member!
How the heck will I get out of this one?

Not to mention the "tip" you gave in another thread about adding another column in the Data Report! I can't understand it! And I can't find any useful info beside you, ralph!

[sertev]

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase currentDir, False
objAccess.DoCmd.OutputTo 3, reportName, "Snapshot Format (*.snp)", "c:\somefile.snp", False
objAccess.Quit 2
Set objAccess = Nothing


This outputs the report to a Snapshot Viewer Format without actually making Access viewable to the user. When it finishes the output, it closes Access. All you have to do is add a Snapshot Active X to a form and set the path to the exported file.

This works great and the user does not even know that it has come straight from Access.

What do you think?

[Zealot]

It's looking good from what you're telling me, but what's the name of the Active X to add?

Oh and... I can still use the "Print" button from the Report, right?

[ralph]

Zealot,
To include Mr/Mrs fields, the gender rptTextbox's datafield should be set to the gender, right? What does that have to do with the report's datamember?

As for the "extra column" stuff, I'll explain later, but perhaps the Access report way will work before then anyway?

[ralph]

To follow up about the gender field. It's true that each section of the report has only one datamember (though a report can have multiple sections).

I wasn't sure what you meant "rptTextbox must have a different datamember". Why must? Couldn't the Command (on which the datamember is based) include a select ... join to include the gender field in the commandText so that it is part of the same datamember? I'm guessing since I don't know how you've structured things, like is the gender field in a different table from the others?

[Zealot]

Ralph, yes, I could use the same command IF you could light up my mind how to put that in SQL terms.

I'm at home right now, but I'll check on this more precisely tomorrow. But I think it's all about putting it in proper SQL actually. From what I've tried today I wasn't able to put an IIf() to work in the SQL.

[ralph]

Now, you mention IIF? what's in what table? Can't do sql if don't know where data is?

like, if it's in the same table.
Select gender, firstname, etc. from table1

If different tables.
Select gender, firstname, etc from table1 inner join table2 0n table1.ID = Table2.ID

[Zealot]

I can have it on same table.
So what I initially tried (in an Access query - it's faster ) was:
SELECT Gender FROM Table Where IIF(Gender, "Dear Mr.", "Dear Mrs.")

It just gives me the field Gender without any change.
The computer is right of course. But there must be something narrowing my mind that's not allowing me to breakthrough.

[ralph]

Oh, ok. I think it's more like
SELECT IIF(Gender, "Dear Mr.", "Dear Mrs.") As
Gender FROM Table Where

I'm working out an extra column example for you.

[sertev]

There should already be a version of snapshot viewer on your computer. It comes packaged with Office (I think). If not you can go to this page and download it

http://support.microsoft.com/default...EN-US;q175274&

After you have finished installing it. Start up your project in VB and then go to the 'Project' menu. Then click on 'Components'. This will bring up the components window. Scroll all the way down to the bottom untill you find 'Snapshot Viewer Control'. Tick the box and the click on 'OK'.

You are set to go. Add the control to you form. Then in the 'From Load Sub' add the following code:

frmReports.SnapshotViewer1.SnapshotPath = App.Path & "\report.snp"

This loads the file.

Then you can make a command button for the print method. Add this code to the command button:

frmReports.SnapshotViewer1.PrintSnapshot

This print the file.

Hope this helps.

[ralph]

A way to do it in vb (the explanation you wanted):
Here's an example to get a twin set of columns in a vb report with a custom rs. I modified the original demo I gave you. Assumes a copy of Nwind.mdb is in the C dir.

For further details on coding/specifying field attributes (under section Methods/Append method):
http://www.crackinguniversity2000.it...ollection.html

[Zealot]

Look what I get sertev:

Run time error 2282
The formats that allow to export data as Microsoft Excel, format rich-text, text of the MSDOS or as files of HTML are not present in the register of the Windows. Execute the configuration program to reinstall the Microsoft Access or, if you are familiar to the definitions of the register, try to correct them manually. For more information on the Register, it makes click on ' Help '.

Babelfish translation! And tweaked by me!

Anyway here I go in the quest for the missing Office 97 CD...


Ralph, I'm going to try sertev's tip until I have no way of achieving it. But thank you for posting an example. It's great to have a plan B.

[sertev]

Could be a problem with Access 97. I don't know if it works with 97. Maybe someone out there can tell me. I have only tested it on 2000.

I don't see that it could be the problem though as Snapshot Viewer has been distributed with Office for Donkey years!!!

Give it a reinstall. See what happens. If you still have probs give me a yell and I will track it down!!!

Did you download Snapshot viewer from the microsoft website?

Good Luck!!

[Zealot]

I just tried to see if there was anything left to install from the Office 97 CD. And there isn't! Only the Archive and Outlook aren't installed, so I really don't know what can I do to make it work!

Edit: And yes, I used the link you provided, since this pc didn't have the component. In fact, since we're speaking of components you didn't need to be so precise about the steps needed to add a component! Though this might be my first professional application, I've built some OCXs myself!

[sertev]

Sorry Mate,


Did not mean to ruffle your feathers. I work on the fact that when you say something, give as much detail as possible.......saves having to ask a second question.

When does the error occur?

What happens if you change the output to lets say Excel?

[Zealot]

Hey don't say you're sorry! I'm im no way offended!

Let me try that Excel one.
Be right back.

[Zealot]

It works with this line:

objAccess.DoCmd.OutputTo acOutputReport, "R Label Mailing", acFormatXLS, "Mail.xls", True

About the time I get the error, it's when it reads the above line in the snapshot format. Could it be that there's something else to install?

[sertev]

That is a good thing.

What is the code that is generating the error?

[Zealot]

Sorry, posted above.

[sertev]

Try this for me...

objAccess.DoCmd.OutputTo 3, "R Label Mailing", "Snapshot Format (*.snp)", "c:\Mail.snp", False

and if that does not work then try using True.

[Zealot]

Same error as always.
I'm going to reinstall Access. I have nothing to lose other than time...

[sertev]

Zealot,

Just as a matter of interest. Before you go and reinstall access,

Open up your report in Access and try and export it that way.

I think that the problem is that the snapshot viewer is not installed properly.

If there is no Snapshot Format in the save as type then this is where your snake lies.

Give it a go and see what you come up with.

[Zealot]

Sertev I got it! (Ithink)

It's that ass called Office 97!
I checked wich converters were installed and only the Excel and the HTML were. So from the several filters VB offers me to export, only with the Excel and the HTML I was successful!

So the question is:
Can someone with Office 2000 send me the files I need (and tell me how to add them to the registry)? Not an easy task ahead...

Of course, I could try to install Office 2000, but how will it work in the clients' computers?

[sertev]

Did a quick search on the Microsoft site. You will need to install the Office 97 or Access 97 Service pack.

Go here and have a look

http://support.microsoft.com/default...;en-us;Q172348

This is the site for the Service Pack....It is 24mb......hope you have cable internet

http://office.microsoft.com/download...f97detail.aspx

See how it goes

[Zealot]

Yes, I do. Though it's just downloading at 10 KB/sec!
Thank you once again sertev.
Edit: Hehe I need to install the SR 1 first.

[Zealot]

Thank you everybody! It works!
Sorry for all this time in silence, but since I had so much to do I went right into it!

Thank you sertev! Thank you ralph!

[sertev]

How did you go?

Any luck?

[Zealot]

Yes, the screenshots are working properly.
Unfortunatly they are a bit too slow...
I'm considering in putting some form with a label saying "Loading" or something.

[Zealot]

Sertev I can't put it to work on anyone else's machine!
I get the Run-time error '429', "ActiveX component can't create object"!

I went to Microsoft's support site, and at this link there is a workaround, but I can't interpret it and put it to work!

Can someone help? Please?

[Zealot]

I used late Binding and installed the Snapshot viewer in the machine with Office 2000. Thank you everyone!