473,320 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Export access form to ppt

I am toying with the idea of exporting a form (which is by filter) into a ppt presentation. I am currently printing to pdf and there is nothing really wrong with it; other than the seeing half the page empty at the bottom. Anyhow, I found the below code on msdn microsoft. I attempted it with changing employees to match my table, and lastname to match the filtered field. This just gives me a ppt with Hi page 1 and the value within the filered field. (53 slides of this) So, is it possible that something like the below could work by using the form's layout (by the filtered product) without having to copy, paste and manipulate one by one in ppt?



Expand|Select|Wrap|Line Numbers
  1. Sub cmdPowerPoint_Click()
  2.     Dim db As Database, rs As Recordset
  3.     Dim ppObj As PowerPoint.Application
  4.     Dim ppPres As PowerPoint.Presentation
  5.  
  6.     On Error GoTo err_cmdOLEPowerPoint
  7.  
  8.     ' Open up a recordset on the Employees table.
  9.     Set db = CurrentDb
  10.     Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
  11.  
  12.     ' Open up an instance of Powerpoint.
  13.     Set ppObj = New PowerPoint.Application
  14.     Set ppPres = ppObj.Presentations.Add
  15.  
  16.     ' Setup the set of slides and populate them with data from the
  17.     ' set of records.
  18.     With ppPres
  19.         While Not rs.EOF
  20.             With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
  21.                 .Shapes(1).TextFrame.TextRange.Text = "Hi!  Page " & rs.AbsolutePosition + 1
  22.                 .SlideShowTransition.EntryEffect = ppEffectFade
  23.                 With .Shapes(2).TextFrame.TextRange
  24.                     .Text = CStr(rs.Fields("LastName").Value)
  25.                     .Characters.Font.Color.RGB = RGB(255, 0, 255)
  26.                     .Characters.Font.Shadow = True
  27.                 End With
  28.                 .Shapes(1).TextFrame.TextRange.Characters.Font.Size = 50
  29.             End With
  30.             rs.MoveNext
  31.         Wend
  32.     End With
  33.  
  34.     ' Run the show.
  35.     ppPres.SlideShowSettings.Run
  36.  
  37.     Exit Sub
  38.  
  39. err_cmdOLEPowerPoint:
  40.     MsgBox Err.Number & " " & Err.Description
  41. End Sub
Feb 1 '14 #1
50 9955
zmbd
5,501 Expert Mod 4TB
SO, If I am following you, you'd like a single Powerpoint slide that acts like an Access form?

We also need to know which version of Office you are using as there are some drastic changes in the automation code between versions.
Feb 1 '14 #2
Access 2010:

I was thinking to have each record to show on a slide, with the same format as the form and by the selected filter. So, if I selected TAC, all records from TAC would be in the ppt. There would be no empty white at the bottom as in the pdfs. It seems even though I have the margins set, etc...white takes up a lot of space.

Did you mean by "acting like a form" that others could edit the fields? That would be an intersting concept.
Feb 1 '14 #3
zmbd
5,501 Expert Mod 4TB
I wouldn't let powerpoint act as the front-end to a database.
You can however set a reference in Powerpoint to the DAO model and then open a recordset based on a stored query (or for that fact, most likely a dynamic one).

I have a "connect-4/Blackout" style game I built in powerpoint for a class I teach to third year students. It was not the easiest thing to build as there isn't a lot of information on the internet about using VBA in powerpoint; however, I started out with the database concept until I ran into the fact that it would be running on both PC's and Macs - still doable, but a real pain.

So to the point:
Trying to get your Access form exported to powerpoint. I've not seen an easy method.

So a workaround... depending on your database file:
- If it is small then we could use the single slide approach where we open the recordset within powerpoint and then populate the slide controls with information from the database. We'd use some action buttons to tie into the "next" "previous" functionality (mind you, I'll be learning some of this with you at the same time (^_^) ) to move to the correct database record. If it's a small enough recordset - might just read into an array and step thru the array.

OR

- A variation on the code you already have in your post: instead of inserting a slide as in the code you have, start out with a single master slide formatted as you desire, then from within access, use the duplicate method ( Slide.Duplicate Method (PowerPoint) Office 2010 ) to duplicate this master slide for each record of interest and enter the record's information into the controls. There's a way to name each control and we refer to the slide by its index within the slides collection. So we duplicate the master, change it's controls displayed text, and move on. Can use the default slide P&N-Action buttons etc... If you start out with everything fairly close to begin with then you can finetune the presentation within PP.

IN either case, the code is going to be fairly complex, I'd say on a 10 point scale with People like Adezii, Rabbit, TheSmileyCoder, and last but not least Neopa closer to that 9/10 range, me around a 5 to 8 and then a 2nd year college Compsci around 4/5 - this rates around the 5 to 6 range.
Feb 2 '14 #4
I wouldn't want anyone to be able to modify via ppt. Just a show n tell the data type of thing. Ppt by death HAHA! Is 53 records a small enough recordset? By filter there are 4 sections with 14, 2, 6 and 31 records.

If this is complex for y'all, then this is defintely way above me as I don't know much about sql/vba. I've been "learning on task" and am challenged by what is easy for most folks. I do have a lot of heart, I absorb quite a bit, and get through it, but unsure I know enough for this as complex as it sounds. I would need hand holding.

If you are willing to tackle it and have patience with me, I am good to try.
Feb 2 '14 #5
zmbd
5,501 Expert Mod 4TB
K:

Let start out with getting that first slide to be properly formatted as in either way you want to go, death by a thousand slides or death by bludgening with the same slide, the method I suggested will depend on this slide.

Once you are happy with it then decide which way you want to run, with the recordset within PPT or the slide duplication via ACC.

Once you have these things done and the choice made, we'll proceed from there.

B.O.L
Feb 2 '14 #6
:)

So, first slide I entered a title and subtitle.

Second Slide: absolutely blank (since the form already contains the section/poc- no need to put a title)

With the filter set to a section, I would like that recordset to go into the ppt.
Feb 2 '14 #7
zmbd
5,501 Expert Mod 4TB
So, first slide I entered a title and subtitle.
Great, no problem

Second Slide: absolutely blank (since the form already contains the section/poc- no need to put a title)
If this is the slide we're going to display the data with, then you need to build this form. Populate it with the text boxes, animations etc... the more of the framework you have in place for this form the better.

We'll go in and name the controls after the slide is properly configured.

With the filter set to a section, I would like that recordset to go into the ppt
Yes, I got that point. We can do that a number of ways and the method will depend somewhat on which path you want to take... the death by slide engulfment or death by the record.
Feb 3 '14 #8
Ok. I have slide 2 ready. It has 10 textboxes to display the data from access. There are other textboxes used as labels.
Feb 3 '14 #9
zmbd
5,501 Expert Mod 4TB
Great,
In powerpoint 2010
Show the Slide we're going to use as or display master
Ribbon, Home Tab
Editing Group
Select drop down
Show selection pane

In the pane that shows up you can double click the name and change it to something useful. Say from "TextBox 1" to "txt_fname" etc... can't tell which is what, simple, on the slide simply double click into the textbox that you want to name... the index bar in the Selection pane will move to that item in the list.

We need to decide which direction to go.
This way we can refer to the controls via the name instead of the index.

Ok, things are about to get really busy here in the lab so be there will be a delay in replies for the near future... (^_^)
Feb 3 '14 #10
Thank you for easy instructions. Done with that part.

I do appreciate your time. Really :) I understand busy.
Feb 3 '14 #11
zmbd
5,501 Expert Mod 4TB
I haven't forgotten you. (^_^)
Really unusually busy day today in the lab.

The next step we need to look at is how to create the presentation.

So long as the database is available in the same folder/thumbdrive it should be easy to do and we'll move into the PPT side of the VBA, or if you would rather deal with just the PPT file then our next move will be into the Access side of VBA.

OK, I'll be offline at one of my remote labs for the next several hours.
Feb 4 '14 #12
Yay, I wasn't forgotten ;)

I have them both in the same folder. In the future, I may have to upoload to a shared network drive.

Hope all went well at the remote sites.
Feb 5 '14 #13
zmbd
5,501 Expert Mod 4TB
powerpoint
<alt><F11>
The VBA editor window will open.
Default should be a white pane on the left and a grey area on the right.
<Menu><Insert>{Module}
A new white pane should open to the right and in the left pane the tree should have a new node named "Modules" with a branch named "Module1"
On the right, first line should be:
Option Explicit

If not insert it.
If not then also (you may want to check this anyway):
<Menu><Tools><Options>
[Editor tab]
Uncheck auto syntax check - error lines by default turn red anyway.
Checkmark everything else on this tab-page.
[General tab]
Error Trapping: Should be break on unhandled errors

select the following and copy, paste it in this module.
Expand|Select|Wrap|Line Numbers
  1. Sub zshowmenames()
  2.     Dim zpres As Presentation
  3.     Dim zslides As Slides
  4.     Dim zslide As Slide
  5.     Dim zshapes As Shapes
  6.     Dim zshape As Shape
  7.     Set zpres = ActivePresentation
  8.     Set zslides = zpres.Slides
  9.     Debug.Print "Slide Count: " & zslides.Count
  10.     For Each zslide In zslides
  11.         Set zshapes = zslide.Shapes
  12.         Debug.Print String(20, "-")
  13.         Debug.Print "Name", "SlideID", "SlideIndex", "SlideNumber"
  14.         Debug.Print zslide.Name, zslide.SlideID, zslide.SlideIndex, zslide.SlideNumber
  15.         For Each zshape In zshapes
  16.             Debug.Print zshape.Name & ",";
  17.         Next
  18.         Debug.Print "..."
  19.         Set zshapes = Nothing
  20.     Next
  21.     Set zslides = Nothing
  22.     Set zpres = Nothing
  23. End Sub
<menu><tools><debug>{Compile...}

SAVE THE FILE (^_^)
SaveAs a MACRO ENABLED file.

<ctrl><g>
In the immediate window that opens type the following and press enter: zshowmenames

You will something like:
Expand|Select|Wrap|Line Numbers
  1. zshowmenames
  2. Slide Count: 44
  3. --------------------
  4. Name          SlideID       SlideIndex    SlideNumber
  5. Slide1         256           1             1 
  6. Title 1,Subtitle 2,Action Button: Forward or Next 4,TextBox 3,...
  7. --------------------
  8. Name          SlideID       SlideIndex    SlideNumber
  9. Slide2         257           2             2 
  10. Tile50,Tile40,Tile30,Tile20,Tile10,Tile00,Tile51,Tile41,Tile31,Tile21,Tile11,... 
  11. (omitted remaining)
click in the immediate window
<ctrl><a>
<ctrl><c>

Come back to the post here, click the [CODE/] button,
and paste the selected text between the code tags.

Sounds like my remote lab needs another visit... sigh.
In the mean time I'll be thinking about the connection string to the database.
Feb 5 '14 #14
Here ya go.

Expand|Select|Wrap|Line Numbers
  1. zshowmenames
  2. Slide Count: 2
  3. --------------------
  4. Name          SlideID       SlideIndex    SlideNumber
  5. Slide26        281           1             1 
  6. Rectangle 4,Rectangle 5,Gruppieren 8,Text Box 3,Date Placeholder 7,Rectangle 4,...
  7. --------------------
  8. Name          SlideID       SlideIndex    SlideNumber
  9. Slide36        291           2             2 
  10. txtPOC,Picture 2,Rectangle 5,Rectangle 4,Picture 10,Label_title,txt_question,txt_q#,txt_num,txt_denom,txt_results,Label_Goal,txt_goal,Label_data,Label_PS,Label_CA,txt_PS,txt_CA,txt_Sec,...
Feb 5 '14 #15
zmbd
5,501 Expert Mod 4TB
(( 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
File Type: zip Bytes_Thread_954564_V201402191502.zip (195.8 KB, 204 views)
Feb 6 '14 #16
I don't see an attachment. Please help.
Feb 7 '14 #17
zmbd
5,501 Expert Mod 4TB
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 :,-(
Feb 7 '14 #18
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!
Feb 8 '14 #19
zmbd
5,501 Expert Mod 4TB
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
Feb 8 '14 #20
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.
Feb 10 '14 #21
zmbd
5,501 Expert Mod 4TB
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.
Feb 10 '14 #22
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:
Expand|Select|Wrap|Line Numbers
  1. 'To use the following, you must set a reference to the access ace library:
  2. '{tools}{references}/"Microsoft Office 14 Access database Engine Object Library"
  3.  
  4. 'I unchecked the below since the ref library is set as above.
  5. Public zDB As DAO.Database
  6. Public zRS As DAO.Recordset
Expand|Select|Wrap|Line Numbers
  1.     'This is here for late binding, if you have the MSO14ACCDEObj reference set comment this
  2.  
  3. 'Commented the const line as above since ref is set
  4. '    Const dbOpenSnapshot = 4
Expand|Select|Wrap|Line Numbers
  1. 'Changed to name of my db
  2.     zstrDatabaseName = "PAD_DQ.accdb"
Expand|Select|Wrap|Line Numbers
  1.     'This is here for late binding, if you have the MSO14ACCDEObj reference set comment this out
  2.  
  3. 'commented out since ref is set
  4. '    Set zaccess = CreateObject("DAO.DBengine.120")
  5. '    Set zDB = zaccess.OpenDatabase(zstrFileToOpen)
I changed the below as a test and think I follow- I can add my other fields as necessary
Expand|Select|Wrap|Line Numbers
  1.     zstrSQL = "SELECT [POC], [q#]" & _
  2.         ", [question], [inputnum]" & _
  3.         " From tbldqp" & _
  4.         " Where( [question] <> "" );"
The below is a bit long since I kept your code and commented them out

Expand|Select|Wrap|Line Numbers
  1. Sub enterthenewrecord()
  2. '    Dim zvEmail As Variant
  3. '    Dim zvLastname As Variant
  4. '    Dim zvFirstname As Variant
  5.     '
  6. Dim zvLiable As Variant
  7. Dim zvQ As Variant
  8. Dim zvQuestion As Variant
  9.  
  10. '    zvEmail = zRS![people_email]
  11. '    zvLastname = zRS![people_lastname]
  12. '    zvFirstname = zRS![people_firstname]
  13.     '
  14. zvLiable = zRS![Liable]
  15. zvQ = zRS![Q]
  16. zvQuestion = zRS![Question]
  17.     '
  18. '    If zvEmail & "" = "" Then zvEmail = "--"
  19. '    If zvLastname & "" = "" Then zvLastname = "--"
  20. '    If zvFirstname & "" = "" Then zvFirstname = "--"
  21.     '
  22. If zvLiable & "" = "" Then zvLiable = "--"
  23. If zvQ & "" = "" Then zvQ = "--"
  24. If zvQuestion & "" = "" Then zvQuestion = "--"
  25.     '
  26.     'Now note: I could have done this entire thing with an array and loop counters
  27.     'however, to keep things somewhat simple, I've opted for the long method
  28.     'just to demonstrate the main concepts.
  29. '    theseshapes.Item("txt_people_pk").TextFrame.TextRange.Text = zRS![People_pk]
  30. '    theseshapes.Item("txt_people_FirstName").TextFrame.TextRange.Text = zvFirstname
  31. '    theseshapes.Item("txt_people_LastName").TextFrame.TextRange.Text = zvLastname
  32.     '
  33. theseshapes.Item("txtPOC").TextFrame.TextRange.Text = zRS![Liable]
  34. theseshapes.Item("txt_Q").TextFrame.TextRange.Text = zvQ
  35. theseshapes.Item("txt_question").TextFrame.TextRange.Text = zvQuestion
  36. '    theseshapes.Item("txt_people_Email").TextFrame.TextRange.Text = zvEmail
  37. '    theseshapes.Item("tbl_people_info").Table.Cell(2, 2).Shape.TextFrame.TextRange.Text = zRS![People_pk]
  38. '    theseshapes.Item("tbl_people_info").Table.Cell(3, 2).Shape.TextFrame.TextRange.Text = zvFirstname
  39. '    theseshapes.Item("tbl_people_info").Table.Cell(4, 2).Shape.TextFrame.TextRange.Text = zvLastname
  40. '    theseshapes.Item("tbl_people_info").Table.Cell(5, 2).Shape.TextFrame.TextRange.Text = zvEmail
Feb 13 '14 #23
zmbd
5,501 Expert Mod 4TB
Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1. zvQ = zRS![Q] 
And you don't have the hash here (^_^)

I'm not sure about what you are saying in the remainder.
Feb 13 '14 #24
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)
:(
Feb 13 '14 #25
zmbd
5,501 Expert Mod 4TB
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 OnSlideShowPageChange(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 OnSlideShowPageChange - first slide, and alter the code slightly for record check.
Feb 13 '14 #26
zmbd
5,501 Expert Mod 4TB
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.
Feb 13 '14 #27
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.
Feb 13 '14 #28
I get a compile error: ambiguous name detected: zDB
Feb 13 '14 #29
zmbd
5,501 Expert Mod 4TB
OK, You've got to be careful here:

Before all of the sub()s we have the following:
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. '
  3. 'Setup to use the application events assumes a classmodule named clsApplicationEvents
  4. 'has event procedures written in it - in this case, I have a call to the cleanup object code
  5. 'as the the ending slide code isn't always running.
  6. Public z_events As New clsApplicationEvents
  7. '
  8. 'Setup some things
  9. Public thispresentation As Presentation
  10. Public firstslide As Slide
  11. Public thisslide As Slide
  12. Public theseshapes As Shapes
  13. '
  14. 'setup for late binding of the DAO database later
  15. Public zaccess As Object
  16. Public zDB As Object
  17. Public zRS As Object
  18. '
  19. 'To use the following, you must set a reference to the access ace library:
  20. '{tools}{references}/"Microsoft Office 14 Access database Engine Object Library"
  21. 'Public zDB As DAO.Database
  22. '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.
Feb 13 '14 #30
The only thing I could find was in 2 subs but don't think this is what you are speaking about. No other DAO.database found except in that first part.

Sub OnSlideShowPageChange(ByVal SSW As SlideShowWindow) AND Sub SetupLinkToDatabase()
Expand|Select|Wrap|Line Numbers
  1. Dim zstrDatabaseName As String
In p.30, I have what I commented out with " ' ", should I go back and not comment them out and remove the MSAccess reference?
Feb 14 '14 #31
zmbd
5,501 Expert Mod 4TB
I tell you what, when I run into things like this near the start of the project I tend to go back to ground zero.

So, back-up your current PPTM
Using a copy of your file, go in and delete ALL of your code.
Don't worry about the action buttons at this point.
Save and close for now

Taking the longer path
Now what I would do is open the PPTM I sent you
<alt><F11> to open the VBE
Right click on Module1 and export to a place you can find it
Right click on clsApplicationEvents and export to the same place.
Close my PPTM

Now reopen your PPTM copy
<alt><F11> to open the VBE
<ctrl><m> to import clsApplicationEvents FIRST
<ctrl><m> to import Module1 second.
Save
Go back to your slides and check that the action buttons are properly assigned to the VBA subs.
Save

Now beacause I am fairly certain that the latebinding will work, leave all of the General/Declaration section alone.


Go to Sub SetupLinkToDatabase() and set:
Expand|Select|Wrap|Line Numbers
  1. zstrDatabaseName = "Bytes_Thread_954564_ConnectToAccess2010_DB.accdb"
to your database

Change your "zstrSQL = " to match what you have

Go to Sub enterthenewrecord() and change the control names to match what you have in the slides.

Menu/Debug/Compile
Let's make sure that this works first.
You may have to start the slide show and go forward and back etc as I've mentioned

(...)
What I have now learned is that Sub OnSlideShowPageChange(ByVal SSW As SlideShowWindow) method that I used in 97, and 2000, etc... has been... well... taken out back and shot by Microsoft. It's still there, and it still works; however that is for legacy support and somewhat unreliable.

So, three workarounds.
  1. use an acction button to initate things.
  2. insert the duplicate first slide
  3. create an add-in

I started with and tested Tested option 1 with my PPTM:
I made a copy of Slide1, removed all of the text etc... leaving just the background. Set the transition to none, set the duration to 1 second, set the advance on mouseclick to true, set the advance timer to 0:00.05

Adjusted the code as follows:
Add
Expand|Select|Wrap|Line Numbers
  1. Public slidecoderan As Integer
to the top section

Altered
Expand|Select|Wrap|Line Numbers
  1. Sub OnSlideShowPageChange(ByVal SSW As SlideShowWindow)
  2.     Dim zstrCurrentPresentationPath As String
  3.     Dim zstrDatabaseName As String
  4.     Dim zstrFileToOpen As String
  5.     Dim zstrActOnThisFile As String
  6.     '
  7.     Select Case SSW.View.CurrentShowPosition
  8.         Case SSW.Presentation.SlideShowSettings.StartingSlide
  9.             'just here as place holder
  10.             'moved all the code from here to Case 2
  11.         Case 2
  12.             '
  13.             'I know, boolean, however, the unassigned boolean doesn't trip as either true nor false
  14.             'found that out the hard way
  15.             If slidecoderan < 1 Then
  16.                 Set thispresentation = ActivePresentation
  17.                 Set thisslide = thispresentation.Slides.Item(SSW.View.CurrentShowPosition)
  18.                 Set firstslide = thispresentation.Slides.Item(SSW.View.CurrentShowPosition)
  19.                 '
  20.                 'Hide the first shape until things are connected
  21.                 With firstslide.Shapes.Item("z_ab_nextslide")
  22.                     .Visible = False
  23.                     .ShapeStyle = msoShapeStylePreset37
  24.                 End With
  25.                 '
  26.                 'setup the connections.
  27.                 Call InitApplicationEvents
  28.                 Call SetupLinkToDatabase
  29.                 '
  30.                 slidecoderan = 1
  31.             End If
  32.         Case SSW.Presentation.SlideShowSettings.EndingSlide
  33.             'for some reason this event is not running?
  34.             Call cleanuptheobjects
  35.             MsgBox Prompt:="(...).EndingSlide event triggered", Title:="OnSlideShowPageChange"
  36.         Case Else
  37.             Set thisslide = thispresentation.Slides.Item(SSW.View.CurrentShowPosition)
  38.             Set theseshapes = thisslide.Shapes
  39.             Call cleartherecords
  40.             Call enterthenewrecord
  41.     End Select
  42. End Sub
Then add the following to cleanuptheobjects()
Expand|Select|Wrap|Line Numbers
  1. If slidecoderan > 0 Then slidecoderan = 0
otherwise there is a chanc that the slidecoderan will retain the 1 value preventing the code from working should you [Esc] and come back to the presentation without first closing the file.

This seems to work fairly well and keeps the remaining code intact.

Using the Action button.
What I would do here is build on the class module the events that are being handled within the above code and then the action button would basically call Sub InitApplicationEvents() and code to move to the next slide while the class init code would handle things that the above code handles during startup.
The add-in, while not hard, is just one more file to make sure is with your presentation...


(I'll upload this newer changes to replace the former in a little while Post #16 = Bytes_Thread_954564_V201402141451.zip)
Feb 14 '14 #32
Compiled with no errors. yay!
(I did not set my reference to MSO 14.0 access db engine object. I will do so if/when you instruct me to do so)

I followed the instructions. Where exactly did you want me to put the below into the sub cleanuptheobjects? After end with?
Expand|Select|Wrap|Line Numbers
  1.     If slidecoderan > 0 Then slidecoderan = 0
The slides whiz by and there are no popups at the end for "done" or "goodbye" so they are not being called? In normal view, there is still no data.

I will await your posting. If I can't stay awake, I will check back tomorrow. :)
Feb 14 '14 #33
zmbd
5,501 Expert Mod 4TB
( Post #16 = Bytes_Thread_954564_V201402141451.zip)
Has all of the changes and the work around.

""done" or "goodbye""
Are message boxes in my posted PPTM so if the code is called they should pop-up.

You will not see any changes nor connection in "normal" view, only when the slide show is running [F5] will the code work.
Feb 15 '14 #34
I can't figure out what I am doing wrong. I only am using your files and the arrow is red the first time around. I have to close out the ppt and re-open since the arrow disappears on the second go around. I must be missing something somewhere. :(
Feb 15 '14 #35
zmbd
5,501 Expert Mod 4TB
Are you getting an macro warning?
Are you getting a sandbox/can't be edited warning?
Open PP w/o any files


Ribbon>File>Options
customize ribbons
Right pane
Look for "Developer"
Select this and [OK]
This will put a tab on PP-Ribbon that will make it easier to get to the VBA

Go back to the main window, you should see the "Developer" tab in the ribbon, click in:

"Macro Security"

THis will open the trust center to the Macro Security level:
Macro Settings
I always have this set to "Disable all macros with Notification." Please change to this setting.
The default is often "Disable all macros without notification"
I do not use the signed nor enable all. One, because either the macros come from someone I know and trust already; thus, with notification I will enable them and because enabling all without knowing what they do is just crazy talk!

While we're here let's take a look at:

Trusted Locations
Make note of one of the folder paths and locations
See if you have any "Normal" type locations such as "My Documents"
- If not, don't add any just yet
- IF you do, then copy the PPTM and th ACCDB that was in the zip file to the location and open them from there.

and

Protected View
All of these should be checked by default.
This will force you to explicitly allow editing and other data execution. Just another layer of protection.

Click OK thru these changes.

Close and reopen PP (shouldn't be required; however, ... )
Open the PPTM from the newest zip file
You should get several popup warnings at this point.
Make sure you select enable macros and enable editing.

This is the point where security can be a nessary pain in the rear (^_^)
Feb 15 '14 #36
No warnings and security settings are in order. I'm going to delete all files and begin again.
Feb 16 '14 #37
I deleted everything and am wondering if you could please post the file with the data table. Please?
Feb 17 '14 #38
zmbd
5,501 Expert Mod 4TB
This file should have it in there:
( Post #16 = Bytes_Thread_954564_V201402141451.zip)

Including the duplicate slide workaround and associated code changes.
Feb 17 '14 #39
ok - I tried on 2 different computers, both with access 2010 and I went ahead and tried with access 2013 and to no avail. :(

Also the access files only contain the people data and not the data table, but I used the people table field names.
Feb 18 '14 #40
zmbd
5,501 Expert Mod 4TB
Then I'm not sure what is happening other than you need to go thru the VBA security settings as I have outlined in Post #36

I have downloaded ( Post #16 = Bytes_Thread_954564_V201402141451.zip) back to my PC

It contains:
Macro Enabled PowerPoint file named:
Bytes_Thread_954564_ConnectToAccess2010.pptm

This should have 4 slides.
Slide one and Slide two are basically twins for layout and design.
Slide 1: Text has description of what is going on with it
Slide 2: Text has description of what is supposed to happen when the presentation is ran. There is an action button, using the theme it is red, named z_ab_nextslide
Slide 3: Presents the data from the database (described below)
It has a series of actions buttons, text boxes, and a table. This slide is set to have no transition time and transistion on mouse click is disabled.
The text boxes of interest are named:
txt_people_pk
txt_people_FirstName
txt_people_LastName
txt_people_Email

The table is named: tbl_people_info

The actions buttons have the following icons, names, and associated VBA script
Icon-Stop-Left; z_ab_firstrecord; sub ShowFirstRecord()
Icon-Left; z_ab_previousrecord; sub ShowPreviousRecord()
Icon-Right; z_ab_nextrecord; sub ShowNextRecord()
Icon-Stop-Right; z_ab_lastrecord; sub ShowLastRecord()
Icon-BigI; z_ab_gotofinalslide; hyperlink to next slide

Slide 4: Simple slide with some text describing the actions that should happen.

VBA:
Module1: This is where all of the main action codes should be running from

clsApplicationEvents: This has the class module that handles the cleanup code.

--
Access Database File named:
Bytes_Thread_954564_ConnectToAccess2010_DB.accdb

The database file has:
tbl_people
[people_pk] autonumber
[people_FirstName] text(255)
[people_LastName] text(255)
[people_email] text(255)

Form:
frm_tbl_people

Module1:
zdoloop()
Ignore this loop. It is there from another example and has nothing to do with this project. Sorry, I should have deleted it.


When you open the PPTM file:
Hopefully you are presented with as series of prompts asking you if you want to enable macros and telling you that editing has been disabled. You will have to enable macros and allow editing.

You should then start out in the normal editing view.
[F5]
From there the first slide will transistion quite quickly to the second slide, hopefully the action button will be visible and green.
If you see the action button; however, it is red, then, click once in the slide, not on the arrow, using the keyboard right arrow to the next slide, Click on each of the record movement buttons. Using the keyboard, left arrow.
Tell me what happens as this point.
If the arrow turns green then my duplicate slide workaround didn't work and we'll have to go back to the designin board and implement things a tad differently.
Feb 18 '14 #41
Ok - arrow is initially red. Using the keyboard arrows going forward and back turn the arrow green. (pretty cool trick, but not intentional) After that, data populates.
Feb 19 '14 #42
zmbd
5,501 Expert Mod 4TB
This has to do with the events being depreciated.
Drat.
Here's another neat trick.
Close program
Open the program - nothing else
Now <alt><f11> to open the VBA-Editor
Now open the .pptm
[F5]
Seems that once VBA is started the application events work.

So... we can rework this so that we pull all of the start-up junk into an action button on the first slide and then advance once the db is connected, open the vba first, or take the slides route.

We start the ISO audit tomorrow; thus, my time is very limited today... see what you can accomplish.
Feb 19 '14 #43
whoah - it works that way. I will research.
Feb 19 '14 #44
zmbd
5,501 Expert Mod 4TB
OK, One last time to get this to work nicely, then it's death by the 57 slide method!

I have downloaded ( Post #16 = Bytes_Thread_954564_V201402191502.zip) back to my PC

It contains:
Macro Enabled PowerPoint file named:
Bytes_Thread_954564_ConnectToAccess2010_3.pptm

This should have 4 slides.
Slide one and Slide two are basically twins for layout and design.

Slide 1: Text has description of what is going on with it.
The actions buttons have the following icons, names, and associated VBA script
Icon-right; z_ab_intnextslide; sub starththeshow()
Using the theme, it starts grey, turns yellowish, runs the code, and if all goes well, resets.

Slide 2: Text has description of what is supposed to happen when the presentation is ran.
The actions buttons have the following icons, names, and associated VBA script
Icon-right; z_ab_nextslide; hyperlink to next slide.
Using the theme, it starts red, turns and if all goes well from the first slide it is green


Slide 3: Presents the data from the database (described below)
It has a series of actions buttons, text boxes, and a table. This slide is set to have no transition time and transistion on mouse click is disabled.
The text boxes of interest are named:
txt_people_pk
txt_people_FirstName
txt_people_LastName
txt_people_Email

The table is named: tbl_people_info

The actions buttons have the following icons, names, and associated VBA script
Icon-Stop-Left; z_ab_firstrecord; sub ShowFirstRecord()
Icon-Left; z_ab_previousrecord; sub ShowPreviousRecord()
Icon-Right; z_ab_nextrecord; sub ShowNextRecord()
Icon-Stop-Right; z_ab_lastrecord; sub ShowLastRecord()
Icon-BigI; z_ab_gotofinalslide; sub clsnend()

I've also added some logic to the show next button in that the nextrecord button will turn z_ab_gotofinalslide green (using theme) upon showing the last record of the recordset and then cycling back to the start.

Slide 4: Simple slide with some text describing the actions that should happen.

VBA:
Module1: This is where all of the main action codes should be running from
This has had a view minor things added and changed. Namely the code that handles the slide change events, and a few other tweeks to get the application level events to tigger more reliably.

clsApplicationEvents: This has the class module that handles the cleanup code.

--
Access Database File named:
Bytes_Thread_954564_ConnectToAccess2010_DB.accdb

The database file has:
tbl_people
[people_pk] autonumber
[people_FirstName] text(255)
[people_LastName] text(255)
[people_email] text(255)

Form:
frm_tbl_people

When you open the PPTM file:
Hopefully you are presented with as series of prompts asking you if you want to enable macros and telling you that editing has been disabled. You will have to enable macros and allow editing.

You should then start out in the normal editing view.
[F5]
From there the first slide will sit until you click on the icon-right, the second slide will replace the first and it will hopefully appear that the action button is visible and green. In reality, the slide changed and the two action buttons are in effect overlayed; thus, the illusion that the buttons changed.

Tell me what happens as this point.
Feb 19 '14 #45
zmbd
5,501 Expert Mod 4TB
From PM::: I’ve removed the banging of head on wall sounds from the posts
(@,,O)
osmosisgg
Both files are in the same folder. I can run macros on this computer (settings enables). (on a enterprise computer- that is)

I changed zstrDatabaseName to my db name.

I changed the lines to reflect a few of my fields in my table
Expand|Select|Wrap|Line Numbers
  1.  zstrSQL = "SELECT [Liable], [Q]" & _
  2.         ", [Question]" & _
  3.         " From tbl_Archive_DQ " & _
  4.         " Where( [Liable] = 'TAC'  );"
Is this the right syntax? The [Liable] field is not a primary key- [z{O - admits and deeply regrets the lack of primary key, we'll not place her in the stockade today}]

I left the .item as yours cause I was trying to minimally destroy/mutilate the code and figured it was just for my testing purposes. I want to add the other fields eventually.
Expand|Select|Wrap|Line Numbers
  1.     Set zshape = theseshapes.Item("tbl_people_info")
I changed zvEmail to zvLiable, zvLastname to zvQ, and zvFirstname to zvQuestion in the sub enterthenewrecord.

This is straight from the one posted yesterday that works EACH and EVERY time I try it, not like the past ones. Now that yours is working beautifully, mine reeks of [insert bad word here]. :/

OK, I work for Honey and Chocolate... and I get to eat first BEFORE you give yourself the concussion.


Let's take a look at that SQL string
On the code line right below it type the following:
Expand|Select|Wrap|Line Numbers
  1. debug.print zstrSQL
Run the slide show
Run the BigI on slide3
<ctrl><g> to open the immediate window.
You should have something like:
SELECT [Liable], [Q], ...

Copy this text from the window
Open your database
Open a new query in design mode
Switch to SQL view
Paste your string
Run (Click on the ! in the ribbon)
See what happens

Now, I cheat with the SQL, I build it using the visual editor when I can (^_^)

So, build the query in Access so that it returns what you want, then switch to the SQL view and do copy, paste it into the PPTM vba code, place the quotes so that the string is formed correctly (remember: Text will need ' ' in the WHERE clause ie:
Expand|Select|Wrap|Line Numbers
  1. "SELECT ... FROM ... WHERE( "[FIELD]= 'searchtext');
(SQL View: Open a query or start new, right click in an empty part of the the tables area, in the popup select sql view - there are other ways such as the ribbon and the applicaiton window bottom right corner icons)
Feb 20 '14 #46
LOL! Thanks :) The last updated powerpoint by the wonderful zmbd worked. Upon trying to insert/update the same ppt with my data, it would not work. I updated one of my access db's to NOT use Q# (yeah one of many I created)- it doesn't work in VBA as mentioned in the above. In short, please pay attention to which one you update so you can reference the correct db data as I made the mistake of not referencing the appropriate updated db.
Feb 20 '14 #47
zmbd
5,501 Expert Mod 4TB
YEA! Now we only need to hear that your finished PPTM works and is also a work-of-art!

-> No really, I think the next step is to get the SQL to be somewhat interactive, no? This was something talked about in the OP with the filter.

My thought here, referring to my last PPTM_V3
When the AB in Slide1 is clicked, the event will run the startup code that sets up the remaining application level events and connects to the database - but not the record set yet.

Once the database is connected, there is the ability to create a form in the PPTM via the VBE. We can use this form; a combobox linked to one of the tables in your database as its row source, and then build our SQL for the record set based on the return just as any other parameter based query within Access.

Once I get some things caught up this morning, I'll take a look at the situation.

In the meantime, make a copy of my working PPTM.
Open the VBE
Menu>Insert>UserForm

The very familiar userform GUI editor opens
Play around with the controls etc... so that when we start with the head-on-wall action again we'll be at least on the same wall...

…haven't seen that chocolate nor honey yet... what, no express service? (^_^)
Feb 21 '14 #48
@zmbd
Ok I will get familiar with it. I would like to avoid head banging- the 80s are over even though they were the best years lol.

oh- oops you mean I wasn't suppsoed to eat the treats for you? hehehheeee
Feb 21 '14 #49
zmbd
5,501 Expert Mod 4TB
So I had thought combox and use the database table as the rowsource as one would do in Access; however, even with a late binding I can not get the combobox to work that way within PowerPoint :( .
So, I'm still looking at this part of the project.
In the meantime, at least you can hard code the query. (^_^)
Feb 25 '14 #50

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Josh Strickland | last post by:
I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. ...
16
by: David Lauberts | last post by:
Hi Wonder if someone has some words of wisdom. I have a access 2002 form that contains 2 graph objects that overlay each other and would like to export them as a JPEG to use in a presentation....
3
by: sam | last post by:
Can you teach me how to export access file to excel file in VB.net? Thank in advance.
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
1
by: chaphets | last post by:
access 2003 Need help to export access form into html or xml documents. Advice appreciated, thanks
1
by: ielamrani | last post by:
Hi, I have the following code which works fine. It export current access form record to an excel sheet: DoCmd.OutputTo acOutputForm, "frmsubformtest", acFormatXLS, "C:\Details.xls", True Is it...
16
by: John | last post by:
I am looking for VBA code that will work with Access 2003 to enable dragging and dropping a file/folder name from Windows XP Explorer into an Access form's text box. This is a common functionality...
0
by: JFKJr | last post by:
Hello everyone! I am trying to export Access table data into Excel file in such a way that the table field names should be in the first line of each column in excel file followed by field data, and...
2
by: Susan Littlefie | last post by:
I want to know an easy way to export Access data to Quickbooks.
1
by: achenier | last post by:
Hi, I've been looking for a solution of my VBA code that will allow me to export each of my Access Form to an individual PDF file with the client name. My DB structure is: Id Anunciante ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.