how do I automatically increment a cell value in access

[Guest]

Greetings all!

I have a large Powerpoint file with 1100 slides. I have created an Access
table to keep track of the slide titles and numbers. However, we often insert
new slides to the Powerpoint file and then have to manually update the
numbers.

My questions
- Is there a way to have a function that will automatically update when I
add a new slide? (Autonumber won't work, since we are often adding slides to
the middle of the Powerpoint file)

- Is there a way to export slide titles into Excel or access - like a "mail
merge" but from Powerpoint into Access?

thanks!

Chris T

 

[Guest]

Chris,

I don't know about exporting from PowerPoint to Excel or Access but I have
coded your solution to insert the slide.

This is the code:
Sub Insert_Slide(strNewTitle As String, intNewNumber As Integer)

Dim db As Database
Dim recSlideShow As Recordset
Dim strSQL As String
Dim intCurrPosition As Integer ' holds the current slide number the code is
working with
Dim result As Integer

Set db = CurrentDb
strSQL = "SELECT slide_number, slide_title FROM tblSlideShow ORDER BY
slide_number;"
Set recSlideShow = db.OpenRecordset(strSQL, dbOpenDynaset)
recSlideShow.FindFirst "slide_number = " & intNewNumber
If recSlideShow.NoMatch = True Then
result = MsgBox("There is currently no slide with this number in the
show. " & _
"Title and Number will be simply added to the table.", vbOKCancel)
If result = 0 Then ' User responded OK...
recSlideShow.Edit
recSlideShow("slide_number") = intNewNumber
recSlideShow("slide_title") = strNewTitle
recSlideShow.Update
Else ' User doesn't want the new slide added
GoTo Exit_Insert_Slide
End If
Else ' found matching slide number, will execute the insertion process
recSlideShow.MoveLast
intCurrPosition = recSlideShow("slide_number") + 1
Do Until intCurrPosition = intNewNumber
recSlideShow.Edit
recSlideShow("slide_number") = intCurrPosition
recSlideShow.Update
recSlideShow.MovePrevious
intCurrPosition = intCurrPosition - 1
Loop
recSlideShow.AddNew
recSlideShow("slide_title") = strNewTitle
recSlideShow("slide_number") = intNewNumber
recSlideShow.Update
MsgBox "Slide Inserted.", vbOKOnly, "Action Complete"
End If
Exit_Insert_Slide:
recSlideShow.Close
db.Close
Set recSlideShow = Nothing
Set db = Nothing
End Sub


You will need a form that has two text boxes on it. One that the user can
enter the new slide's title into (name the textbox “txtEnterTitleâ€) and
another that the user can enter the number position desired (named
“txtEnterTitleâ€). Then, you insert a button (let’s call that
“but_InsertSlideâ€) on the form that uses VBA to call the above subroutine.
It would look like this:
Private Sub but_InsertSlide_Click()

Call Insert_Slide(Me.txtEnterTitle, Me.txtEnterNumber)

End Sub

Then, you need to insert this code either in the code module of the form
that you are working on or in a general module.

If you have any questions, let me know. If you’d like, you can send me your
e-mail address and I can send you the entire form (I created it to make sure
it worked).

 

[Guest]

--
CT

Chris,

I don't know about exporting from PowerPoint to Excel or Access but I have
coded your solution to insert the slide.

This is the code:
Sub Insert_Slide(strNewTitle As String, intNewNumber As Integer)

Dim db As Database
Dim recSlideShow As Recordset
Dim strSQL As String
Dim intCurrPosition As Integer ' holds the current slide number the code is
working with
Dim result As Integer

Set db = CurrentDb
strSQL = "SELECT slide_number, slide_title FROM tblSlideShow ORDER BY
slide_number;"
Set recSlideShow = db.OpenRecordset(strSQL, dbOpenDynaset)
recSlideShow.FindFirst "slide_number = " & intNewNumber
If recSlideShow.NoMatch = True Then
result = MsgBox("There is currently no slide with this number in the
show. " & _
"Title and Number will be simply added to the table.", vbOKCancel)
If result = 0 Then ' User responded OK...
recSlideShow.Edit
recSlideShow("slide_number") = intNewNumber
recSlideShow("slide_title") = strNewTitle
recSlideShow.Update
Else ' User doesn't want the new slide added
GoTo Exit_Insert_Slide
End If
Else ' found matching slide number, will execute the insertion process
recSlideShow.MoveLast
intCurrPosition = recSlideShow("slide_number") + 1
Do Until intCurrPosition = intNewNumber
recSlideShow.Edit
recSlideShow("slide_number") = intCurrPosition
recSlideShow.Update
recSlideShow.MovePrevious
intCurrPosition = intCurrPosition - 1
Loop
recSlideShow.AddNew
recSlideShow("slide_title") = strNewTitle
recSlideShow("slide_number") = intNewNumber
recSlideShow.Update
MsgBox "Slide Inserted.", vbOKOnly, "Action Complete"
End If
Exit_Insert_Slide:
recSlideShow.Close
db.Close
Set recSlideShow = Nothing
Set db = Nothing
End Sub


You will need a form that has two text boxes on it. One that the user can
enter the new slide's title into (name the textbox “txtEnterTitleâ€) and
another that the user can enter the number position desired (named
“txtEnterTitleâ€). Then, you insert a button (let’s call that
“but_InsertSlideâ€) on the form that uses VBA to call the above subroutine.
It would look like this:
Private Sub but_InsertSlide_Click()

Call Insert_Slide(Me.txtEnterTitle, Me.txtEnterNumber)

End Sub

Then, you need to insert this code either in the code module of the form
that you are working on or in a general module.

If you have any questions, let me know. If you’d like, you can send me your
e-mail address and I can send you the entire form (I created it to make sure
it worked).

Andrea

Wow, thanks! ...I'm actually not a very experienced programmer, (I've taken
Java and Perl, but don't use them often, and haven't done any VBA programming
on my own), so I want to clarify:

- Basically I need to be able to change one number in a row in an Access
table and "automatically add 1" to the numbers in the whole column below the
one where I inserted the number. I think this is difficult because Access
always adds from the end, so how can I tell it to add from the middle...

- Does your script allow me to dfo that? if so, thanks so much! If not,
thanks as well, but I'll keep looking for the solution...

Thanks!

CT

 

[Guest]

Chris,

My code pulls up the recordset of the entire table, put in order by the
slide number. Then it loops through the records, starting at the bottom,
incrementing the slide number by one. This ends when it gets to the point
that you want to insert the new slide, thus leaving the gap. After the loop
ends, the new slide is added.

What I would suggest is that you make a copy of your Access file, add the
stuff I've described, and test it. It should work for you. I created a
little sample database here to test it and it ran fine.

Let me know how it goes.

 

[David C. Holley]

PowerPoint does support VBA. Have you considered snooping around the
PowerPoint Object Model? It shouldn't be that difficult to create a SUB
in PowerPoint that automatically updates the Access database.