Export access form to ppt

**zmbd** · Feb 6 '14, 10:27 PM

(( Please see this related post describing the current attachment: Post#45 ))
OK,
We may need that information latter if this doesn't fit the bill.

Attached is a zip file with one of my simple test databases and a macro enabled powerpoint file.
This is the single Slide with Database backend option.
(took me awhile to get the right latebinding setup!)

This has been tested under windows7 with Office 2010 and appears to work quite well.

Now, I could have really taken this way into the deep end using an array in the code to handle things however, I thought that I would do things a little more basic in nature and let you decide what to do.

Open the powerpoint, press [F5] to start the show.
You should see an arrow action button on the first slide, IF NOT then an error occured in opening the back end.
The "Big I" on slide two will take you out, 5 seconds later the final slide will close, and once you click to end the show, the cleanup code will run and say goodbye once finished.

Now, ofcourse, these are only intended to get you started. The code should be fairly easy to follow and (IMHO (^_^) ) very well commented.

>>>ONE THING TO NOTE<<<
If you are in a slide show and there is an error in the code, it simply stops.
It doesn't break
Even if you have error trapping code, it doesn't always break as expected. Instead the slide simple sits there.

I am expecting that you are somewhat skilled in powerpoint, if not then there are several tutorial sites out there to work thru.

Any questions please let me know.

Attached Files

Bytes_Thread_954564_V201402191502.zip (195.8 KB, 243 views)

**osmosisgg** · Feb 7 '14, 06:36 AM

I don't see an attachment. Please help.

**zmbd** · Feb 7 '14, 05:09 PM

What, you can't see the attachement...

Look Harder, IT's in the ether...

sigh

looks like I forgot to actually attach it.

!!!SORRY!!!
Mesagoofed
Unfourtunately, that file is on the office PC and I wont have access (no pun.... but funny) to it again until Monday.
Once again, my applogies for the delay :,-(

**osmosisgg** · Feb 8 '14, 06:00 PM

I thought it was due to me being sick that I couldn't see it. I zoomed in and went line by line LOL! I can wait. I plan on getting back under the covers and recouping. Dr's orders.

Enjoy the weekend!

**zmbd** · Feb 8 '14, 07:39 PM

that thing has been working its way thru the office and the schools here too... get-well-soon. I'll have that file uploaded by 7amCST Monday

**osmosisgg** · Feb 10 '14, 04:43 PM

Thank you for posting. I can't seem to open the ppt macro. It says "windows cannot complete the extraction. The destination file could not be created." I have the db and ppt in the same folder. I ran the module and I got the acknowledgement popups.

Any idea? Danke.

**zmbd** · Feb 10 '14, 09:19 PM

OK, a revised file has been attached to the post.

Not sure what had happened there, appears to have been corrupted.

I have downloaded and re-extracted without issue, something I didn't check the first time (really had no reason to).

I also used an archiver instead of windows7 to zip the files.

While in development, the PPTM worked flawlessly. Today however, there seems to be a glitch. The workaround is on slide one of the presentation.

**osmosisgg** · Feb 13 '14, 01:33 PM

Hello again. Sorry it took me a while to recoup. The last zip file, one of the tables were missing in access, so I just copied from the last one over to it.

What I've done:
Access works- I get the popups with the info on records.

I copied over the buttons and the last slide from your ppt. I figured this would keep some of the integrity and make it easier for me to follow; however, the red button is not shown when in slideshow BUT I have the -- in the boxes.

Here is what I did to the code. Maybe you can see where I went and torutured it. I added comments on changes.

I apologize in advance for any bad mannerisms. (just want to cover myself)

PPT:

Code:

'To use the following, you must set a reference to the access ace library:
'{tools}{references}/"Microsoft Office 14 Access database Engine Object Library"

'I unchecked the below since the ref library is set as above.
Public zDB As DAO.Database
Public zRS As DAO.Recordset

Code:

    'This is here for late binding, if you have the MSO14ACCDEObj reference set comment this
    
'Commented the const line as above since ref is set
'    Const dbOpenSnapshot = 4

Code:

'Changed to name of my db
    zstrDatabaseName = "PAD_DQ.accdb"

Code:

    'This is here for late binding, if you have the MSO14ACCDEObj reference set comment this out
    
'commented out since ref is set
'    Set zaccess = CreateObject("DAO.DBengine.120")
'    Set zDB = zaccess.OpenDatabase(zstrFileToOpen)

I changed the below as a test and think I follow- I can add my other fields as necessary

Code:

    zstrSQL = "SELECT [POC], [q#]" & _
        ", [question], [inputnum]" & _
        " From tbldqp" & _
        " Where( [question] <> "" );"

The below is a bit long since I kept your code and commented them out

Code:

Sub enterthenewrecord()
'    Dim zvEmail As Variant
'    Dim zvLastname As Variant
'    Dim zvFirstname As Variant
    '
Dim zvLiable As Variant
Dim zvQ As Variant
Dim zvQuestion As Variant
    
'    zvEmail = zRS![people_email]
'    zvLastname = zRS![people_lastname]
'    zvFirstname = zRS![people_firstname]
    '
zvLiable = zRS![Liable]
zvQ = zRS![Q]
zvQuestion = zRS![Question]
    '
'    If zvEmail & "" = "" Then zvEmail = "--"
'    If zvLastname & "" = "" Then zvLastname = "--"
'    If zvFirstname & "" = "" Then zvFirstname = "--"
    '
If zvLiable & "" = "" Then zvLiable = "--"
If zvQ & "" = "" Then zvQ = "--"
If zvQuestion & "" = "" Then zvQuestion = "--"
    '
    'Now note: I could have done this entire thing with an array and loop counters
    'however, to keep things somewhat simple, I've opted for the long method
    'just to demonstrate the main concepts.
'    theseshapes.Item("txt_people_pk").TextFrame.TextRange.Text = zRS![People_pk]
'    theseshapes.Item("txt_people_FirstName").TextFrame.TextRange.Text = zvFirstname
'    theseshapes.Item("txt_people_LastName").TextFrame.TextRange.Text = zvLastname
    '
theseshapes.Item("txtPOC").TextFrame.TextRange.Text = zRS![Liable]
theseshapes.Item("txt_Q").TextFrame.TextRange.Text = zvQ
theseshapes.Item("txt_question").TextFrame.TextRange.Text = zvQuestion
'    theseshapes.Item("txt_people_Email").TextFrame.TextRange.Text = zvEmail
'    theseshapes.Item("tbl_people_info").Table.Cell(2, 2).Shape.TextFrame.TextRange.Text = zRS![People_pk]
'    theseshapes.Item("tbl_people_info").Table.Cell(3, 2).Shape.TextFrame.TextRange.Text = zvFirstname
'    theseshapes.Item("tbl_people_info").Table.Cell(4, 2).Shape.TextFrame.TextRange.Text = zvLastname
'    theseshapes.Item("tbl_people_info").Table.Cell(5, 2).Shape.TextFrame.TextRange.Text = zvEmail

**zmbd** · Feb 13 '14, 03:19 PM

Code:

 zstrSQL = "SELECT [POC], [q#]" & _

I would remove the "#" it's a token:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

Code:

zvQ = zRS![Q]

And you don't have the hash here (^_^)

I'm not sure about what you are saying in the remainder.

**osmosisgg** · Feb 13 '14, 04:31 PM

First, thanks to the moderator for fixing the "code". I thought I pasted between. :) I got the arrow button! Wooohooo! I fixed the #. I thought I got them all since it did pop as an error.

The remainder was to show where I changed the code to bring in my actual data instead of what you had for your data. There is no data that populates. I don't get the "cleanup done" and "goodbye" anymore either. (I still have "--" in the boxes though)
:(

**zmbd** · Feb 13 '14, 05:45 PM

Open the original PPT I sent
You'll find that there is a class module, that needs to be copied over to your new PPT file.

What has been happening to me as of this last is that for some reason when slideshow starts the "Sub OnSlideShowPage Change(ByVal SSW As SlideShowWindow )" is not running as it should. Hence, the move forwared click the record movement arrows and move back to the begining.

I think what we may want to consider is taking that red arrow on the first slide and re-writing the code so that clicking on this arrow tiggers the code that was running under OnSlideShowPage Change - first slide, and alter the code slightly for record check.

**zmbd** · Feb 13 '14, 05:53 PM

That final block
line 22,23,24 = "--" for now let's make that
= "No Entry" so that we can tell if there's actually anything being pulled.

**osmosisgg** · Feb 13 '14, 06:56 PM

It's still "--". I do have it linked to my db and it is the correct name (and spelling). My first thought was it could be the where statement (#4 above) I wasn't sure if that is correct.

**osmosisgg** · Feb 13 '14, 07:03 PM

I get a compile error: ambiguous name detected: zDB

**zmbd** · Feb 13 '14, 09:00 PM

OK, You've got to be careful here:

Before all of the sub()s we have the following:

Code:

Option Explicit
'
'Setup to use the application events assumes a classmodule named clsApplicationEvents
'has event procedures written in it - in this case, I have a call to the cleanup object code
'as the the ending slide code isn't always running.
Public z_events As New clsApplicationEvents
'
'Setup some things
Public thispresentation As Presentation
Public firstslide As Slide
Public thisslide As Slide
Public theseshapes As Shapes
'
'setup for late binding of the DAO database later
Public zaccess As Object
Public zDB As Object
Public zRS As Object
'
'To use the following, you must set a reference to the access ace library:
'{tools}{references}/"Microsoft Office 14 Access database Engine Object Library"
'Public zDB As DAO.Database
'Public zRS As DAO.Recordset

You can't have the zDB set for both object and dao.database

You can't reuse the zDB within any of the code within the same module.

So if you go and reuse these within the following sub() or Function() you will get the "ambiguous name detected: zDB
" error.

So go back into your sub() and make sure you do not have an other "DIM zDB As DAO.Database" entry.