Select separate rows in table and write into Word File via Bookmarks

[FinisherProgrammer21]

Hello guys,



I am relatively new to Access and VBA. With the following code, I managed to write data from a table named "Daten" to a Word File via Bookmarks and save the new created file at the path in the code.
Here is my problem: When I run the code, it does it for ALL users that are in the table but I would like to have the option to select users and do it separetely with them.
The table consists of several attributes like Name, FirstName, Birthday, and so forth. And I managed to create new users with a form(ular).
How can I sort of select the table row and manipulate the code so that I can select the users I want to do it with?

I did a google search and found Select, DoCmd Go To. Can anyone help? Thank you for everything!

Greetings,
FinisherProgrammer21

Option Compare Database




Public Sub ExportNameToWord()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.Recordset
    
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("C:\Users\Desktop\Testdocument.docx")
    Set rs = CurrentDb.OpenRecordset("tbl_Daten")
    
  
    If Not rs.EOF Then rs.MoveFirst
    
    Do Until rs.EOF
        wDoc.Bookmarks("Name").Range.Text = Nz(rs!Nachname, "")
        wDoc.Bookmarks("Firstname").Range.Text = Nz(rs!Firstname, "")
        wDoc.Bookmarks("Birthday").Range.Text = Nz(rs!Birthday, "")
       
        wDoc.SaveAs2 "C:\Users\Desktop\" & rs!Name & rs!Firstname & "_Testdocument.docx"
        
        wDoc.Bookmarks("Name").Range.Delete wdCharacter, Len(Nz(rs!Name, ""))
        wDoc.Bookmarks("Firstname").Range.Delete wdCharacter, Len(Nz(rs!Firstname, ""))
        wDoc.Bookmarks("Birthday").Range.Delete wdCharacter, Len(Nz(rs!Birthday, ""))
        
        
        rs.MoveNext
    Loop
    
    wDoc.Close False
    wApp.Quit
    
    Set wDoc = Nothing
    Set wApp = Nothing
    Set rs = Nothing
End Sub

[moke123]

Instead of using a table in your open recordset, use a query with a where clause.

StrSql = "Select * from tbl_Daten where PersonID = " & ? 
Set rs = CurrentDb.OpenRecordset(strSql),dbFailOnError

Adjust strSql as needed

[orange]

One way would be to have a form with a combobox and a command button.
The combobox would have as its rowsource the data you want from your tbl_daten.
The button when clicked would process the selected record and write it to your wordfile.

You would modify your code to be a function.

[FinisherProgrammer21]

Hello moke123,

I can't quite follow. With the where Clause, you would just define the criteria of the results of the query, right? So like range > xy but in my case, it's just arbitrarily select a person in case there needs to be an output.

In your solution, there is no option to single handedly select a person, if I'm right?

I don't mean to offend you, I'm just a newbie and ask if I understood correctly. Thanks!

[FinisherProgrammer21]

Hello orange,

if I implement the button, do I immediately have to assign VBA Code to the button in Access? I'm asking because I have never done that so far.

What steps do I need to do in order to do what you suggested?

Thanks!

[orange]

I'll be away most of the day so do not have time to work through a solution.

Research the terms in blue that I provided in my other post #3. Look for youtube videos on
form with combobox.
You could also use the after update event of the combobox to invoke your function.
As moke said you should use a query as the recordset in your data selection. You would select the record from tbl_daten that matches the selection in the combobox.

I'm sure others will offer advice or examples.
You may also find info in the Similar Threads at th bottom of this and related pages.

[Gicu]

You could add a boolean (Yes\No) field to your Daten table named DatenSelect or similar, add it to your form (as a check-box), select your records then do your Word export using a query like this: "SELECT Daten.* FROM Daten WHERE DatenSelect=True;".

Cheers,
Vlad

[moke123]

You could add a boolean (Yes\No) field to your Daten table named DatenSelect or similar, add it to your form (as a check-box), select your records then do your Word export using a query like this: "SELECT Daten.* FROM Daten WHERE DatenSelect=True;".

Cheers,
Vlad

The one caveat to that method is if it's a shared database. Should 2 users be making selections at the same time they would cross with each other. I would use a secondary local database with a table to make selections.

[FinisherProgrammer21]

I'll be away most of the day so do not have time to work through a solution.

Research the terms in blue that I provided in my other post #3. Look for youtube videos on
form with combobox.
You could also use the after update event of the combobox to invoke your function.
As moke said you should use a query as the recordset in your data selection. You would select the record from tbl_daten that matches the selection in the combobox.

I'm sure others will offer advice or examples.
You may also find info in the Similar Threads at th bottom of this and related pages.

============================

Hello orange,

I did use your suggested way and I now managed to insert a SubForm into my Form called "Data Search by Combobox". In the Form Head, I managed to implement a ComboBox named "cboVorname" and with the following code, I managed to get the search function going:

 
private Sub cboVorname_AfterUpdate()
Dim Person as String
Person = "Select * from tbl_Stammdaten where ([ID] = " & Me.cboVorname & ")"
Me.Dateneingabe_Subform.Form.RecordSource = Person
Me.Dateneingabe_Subform.Form.Requery

Now, here is my last problem to all this:

In the detail section of the form "Date Search by Combobox", I managed to implement a button called "Word Export".
How do I adjust my code from above to make the button create a Word-File only for what the combobox filters and shows in the detail section?

Thank you guys! I would be very very grateful if you could help me on this one.
I really tried, for several hours but I just can't get the code going.

Greetings,
FinisherProgrammer21

[orange]

I think the following will work, BUT I'm not a word vba person, nor have I seen your database.

1.- Adjust the sub you showed earlier to become

Public Sub ExportNameToWord(sSelectedPerson as String)
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.Recordset
    
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("C:\Users\Desktop\Testdocument.docx")
    'Set rs = CurrentDb.OpenRecordset("tbl_Daten") '''' THIS LINE IS REPLACED WITH THE NEXT LINE
    Set rs = CurrentDb.OpenRecordset(sSelectedPerson)
'sSelectedPerson is thevalue you selected from the combobox and your related SQL (Person)
  
    If Not rs.EOF Then rs.MoveFirst
    
    Do Until rs.EOF
        wDoc.Bookmarks("Name").Range.Text = Nz(rs!Nachname, "")
        wDoc.Bookmarks("Firstname").Range.Text = Nz(rs!Firstname, "")
        wDoc.Bookmarks("Birthday").Range.Text = Nz(rs!Birthday, "")
       
        wDoc.SaveAs2 "C:\Users\Desktop\" & rs!Name & rs!Firstname & "_Testdocument.docx"
        
        wDoc.Bookmarks("Name").Range.Delete wdCharacter, Len(Nz(rs!Name, ""))
        wDoc.Bookmarks("Firstname").Range.Delete wdCharacter, Len(Nz(rs!Firstname, ""))
        wDoc.Bookmarks("Birthday").Range.Delete wdCharacter, Len(Nz(rs!Birthday, ""))
        
        
        rs.MoveNext
    Loop
    
    wDoc.Close False
    wApp.Quit
    
    Set wDoc = Nothing
    Set wApp = Nothing
    Set rs = Nothing
End Sub

2. Add the last line below to your AfterUpdate to invoke the Export to word.

private Sub cboVorname_AfterUpdate()
Dim Person as String
Person = "Select * from tbl_Stammdaten where ([ID] = " & Me.cboVorname & ")"
Me.Dateneingabe_Subform.Form.RecordSource = Person
Me.Dateneingabe_Subform.Form.Requery
Call ExportNameToWord(Person)  'Add this line to invoke the Export to word !!!!!!!!!!

Note: Try this on a copy of your database. Do a backup first -just in case I've misunderstood.

It appears that you are only selecting 1 person at a time --is this correct?

[FinisherProgrammer21]

I think the following will work, BUT I'm not a word vba person, nor have I seen your database.

1.- Adjust the sub you showed earlier to become

Public Sub ExportNameToWord(sSelectedPerson as String)
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.Recordset
    
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("C:\Users\Desktop\Testdocument.docx")
    'Set rs = CurrentDb.OpenRecordset("tbl_Daten") '''' THIS LINE IS REPLACED WITH THE NEXT LINE
    Set rs = CurrentDb.OpenRecordset(sSelectedPerson)
'sSelectedPerson is thevalue you selected from the combobox and your related SQL (Person)
  
    If Not rs.EOF Then rs.MoveFirst
    
    Do Until rs.EOF
        wDoc.Bookmarks("Name").Range.Text = Nz(rs!Nachname, "")
        wDoc.Bookmarks("Firstname").Range.Text = Nz(rs!Firstname, "")
        wDoc.Bookmarks("Birthday").Range.Text = Nz(rs!Birthday, "")
       
        wDoc.SaveAs2 "C:\Users\Desktop\" & rs!Name & rs!Firstname & "_Testdocument.docx"
        
        wDoc.Bookmarks("Name").Range.Delete wdCharacter, Len(Nz(rs!Name, ""))
        wDoc.Bookmarks("Firstname").Range.Delete wdCharacter, Len(Nz(rs!Firstname, ""))
        wDoc.Bookmarks("Birthday").Range.Delete wdCharacter, Len(Nz(rs!Birthday, ""))
        
        
        rs.MoveNext
    Loop
    
    wDoc.Close False
    wApp.Quit
    
    Set wDoc = Nothing
    Set wApp = Nothing
    Set rs = Nothing
End Sub

2. Add the last line below to your AfterUpdate to invoke the Export to word.

private Sub cboVorname_AfterUpdate()
Dim Person as String
Person = "Select * from tbl_Stammdaten where ([ID] = " & Me.cboVorname & ")"
Me.Dateneingabe_Subform.Form.RecordSource = Person
Me.Dateneingabe_Subform.Form.Requery
Call ExportNameToWord(Person)  'Add this line to invoke the Export to word !!!!!!!!!!

Note: Try this on a copy of your database. Do a backup first -just in case I've misunderstood.

It appears that you are only selecting 1 person at a time --is this correct?

==================



Hey orange, I did as you suggested and it gets me the error:

"Sub or Function not defined"

What could be the reason?

Greetings,
FinisherProgrammer21

[orange]

Did you change the name of the Sub?

Is the Sub located in a standard module?

What is
tbl_Stammdaten vs tbl_Daten ?

Attached is a mockup

[FinisherProgrammer21]

Did you change the name of the Sub?

Is the Sub located in a standard module?

What is
tbl_Stammdaten vs tbl_Daten ?

Attached is a mockup

Hello orange, many thanks for the endless support! I solved it with your and the other guys' help
So glad there are people working cohesively and are willing to contribute to other people's problems.
Don't wanted to be rude and say thank you in a genuine way, even if a little late. I was completely focused on another task. Sorry!

This is one solved

[orange]

Good stuff!