Help with recordsets - DAO

[TryingMyBest]

Hope you can help with this one....I'm sure you can!

In my database I want to open a word document that has a table populated from data depending on the current record that's open.

The menu item to open the document and populate some bookmarks is as follows:

Function ShowNCRProjectTeamReport()

'On Error Resume Next

Dim objWord As Object
Dim Reference As Object
Set Reference = [Forms]![frmNonConformities]![txtPSTName]

If IsNull(Reference) Then
MsgBox "Error"
Else
Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    objWord.Documents.Add Template:=(CurrentProject.Path & "\NCRTemplateTemp.dot")
    objWord.ActiveDocument.Bookmarks.Item("ProjectTeamName").Range.Text = Reference
    objWord.ActiveDocument.Bookmarks.Item("ProjectTeamName1").Range.Text = Reference
    objWord.ActiveDocument.Bookmarks.Item("ProjectTeamName2").Range.Text = Reference
    objWord.ActiveDocument.Bookmarks.Item("WBSCode").Range.Text = DLookup("WBSCode", "NonConformanceReport")

End If
End Function

This is ok and opens the document, populating the necessary fields...except for the wbscode one but I know what's wrong with that...I've been messing!

Within the word document I picked up the following code from a link on this forum...

Private Sub Document_New()
Dim dbe As DAO.DBEngine
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strDBName As String

'On Error Resume Next

strDBName = "U:\APT\Release 3\Audit_Planning_Tool.mdb"

Set dbe = CreateObject("DAO.DBEngine.36")
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)
Set rst = dbs.OpenRecordset("NonConformanceReport")

Selection.GoTo What:=wdGoToBookmark, Name:="NCRNumber"
With rst
    Do While Not .EOF
    Selection.TypeText Text:=![NCRNumber]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![Description]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![DeficiencyLevel]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![AgreedAction]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![ActionOwner]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![AgreedCompletionDate]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![Progress]
    Selection.MoveRight Unit:=wdCell
    .MoveNext
    Loop
    .Close
End With
Selection.SelectRow
Selection.Rows.Delete
End Sub

If the database is closed then the code runs and doesn't return any records as the query is based on a field on a form. If the database is open then the code stops with an error to the effect that the database is locked.

I pretty sure this is because it's trying to open the database rather than use the already open database....so what is the code to create a recordset from a query that is already populated with the data?

Cheers
Jo

 

[TryingMyBest]

Anybody?

Can anybody help with this?

 

[tkpstock]

I'm wondering

I'm inexperienced at Word VBA, but the code

Selection.SelectRow
Selection.Rows.Delete

Seems to delete the only row you've created - I don't see that when you move to a new record, you start a new row. So it appears that your code deletes the only row you have.

I may be way off base here...

 

[TryingMyBest]

Thanks for your reply...I appreciate you looking at this.

That piece of code is simply to delete the surplus row in the word table once the loop has ended. I think perhaps the problem lies with the way in which the recordset is created but not having ever used recordsets I'm just not sure.

Any more pointers?

Jo

 

[tkpstock]

Where in the code...

When you move to a new record, you should start a new row in the word document, right? Where does this happen? Does it happen automatically?

 

[TryingMyBest]

Private Sub Document_New()
Dim dbe As DAO.DBEngine
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strDBName As String

'On Error Resume Next

strDBName = "U:\APT\Release 3\Audit_Planning_Tool.mdb"

Set dbe = CreateObject("DAO.DBEngine.36")
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)
Set rst = dbs.OpenRecordset("NonConformanceReport")

Selection.GoTo What:=wdGoToBookmark, Name:="NCRNumber"
With rst
    Do While Not .EOF
    Selection.TypeText Text:=![NCRNumber]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![Description]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![DeficiencyLevel]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![AgreedAction]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![ActionOwner]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![AgreedCompletionDate]
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:=![Progress]
    [COLOR=Red]Selection.MoveRight Unit:=wdCell[/COLOR]
    .MoveNext
    Loop
    .Close
End With
Selection.SelectRow
Selection.Rows.Delete
End Sub

It happens at the last MoveRight command (highlighted red above)....you know how TAB in word creates a new row when at the end of the row?

Jo

 

[tkpstock]

So, it does happen automatically - that was my question. It appears that your recordset reference is good. You may want to try the following

Set rst = dbs.OpenRecordset("NonConformanceReport", dbOpenSnapshot)
rst.MoveLast ' Fills the recordset
rst.MoveFirst

Maybe add the code

msgbox rst.RecordCount

to see how many rows are returned from the recordset...

Don't know if that'll help or not... jus trying to help.

 

[TryingMyBest]

Got it!!!!!!

With a bit of messing I've found that the problem is my query. I tried to create a table in Excel with the idea that I would just create an object within my word document and the query returned the same error before it was even built.

Further messing shows that the problem is the criteria used in the query for some reason Word and Excel don't like this even though Access returns the correct results. When I removed the criteria it worked ok but I got all of the records in my table....not what was wanted.

So I have changed my select query to a make table query and referenced the new table from the word template. It seems to run ok.

Thanks for all your help but it seems the problem wasn't the recordset after all.

Jo

 

[modest]

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

set db = CurrentDb
set qdf = db.QueryDefs("query name here")
set rs = qdf.OpenRecordset

This bases a recordset on a query, as you previously requested.

 

[TryingMyBest]

Thanks Modest

Thanks for your help modest....as ever! I'll stick with the solution I've found for this one but will bear your advice in mind for future recordsets.

Thank you