Access Module Code: rst.movenext I assume... (Resolved in the nick of time)

[Matt_T_hat]

Presumably it is perfectly simple to open an SQL statement OR a Query and then move through it record by record until EOF thus doing something with each value.

In this case I shall be wanting to do stuff to a word document dependent on the data.

My question just boils down to this:

I understand that I will be using this structure

If not EOF then
Do
Open the SQL/Query (somehow) into some object or other
result = function_to_do_stuff(object.rst.field("foo"))
result = function_to_do_stuff(object.rst.field("bar"))
object.rst.movenext
loop untill EOF
end if

BUT what syntax, words, objects etc am I to use... basically I have no idea of the correct use of the language in this instance.

Could someone post some actual code in order to get me going in the right direction?

BTW: the code will run in a module

[edit]Or a pointer to an article or anything just need to start[/edit]

[Matt_T_hat]

here for example is a sample I picked up a while ago:

doc.Bookmarks("OrderDetails").Select
Do
    With objWord.Selection
        .TypeText vbTab
        .TypeText rstOrder.Fields("Quantity")
        .TypeText vbTab
        .TypeText rstOrder.Fields("ProductName")
        .TypeParagraph
    End With
    rstOrder.MoveNext
Loop While Not (rstOrder.EOF)

this was part of a bookmarks useage tutorial but the writter assumed that the user would know how to Dim and initialise the object... but I don't know.

[adocwra]

this is an example it will not work without database name and proper sql statement
i am assuming you are using access


Public cn As New ADODB.Connection
Public rstOrder As New ADODB.Recordset


Private Sub cmdOpenRS_Click()

On Error GoTo AdoError


Dim strConnect As String
Screen.MousePointer = vbHourglass
'place your database here
'Check to be sure the connection is not already opened:
Path = "C:\orders.mdb"
If cn.State = 1 Then
cn.Close
Set cn = Nothing
End If
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";"
' Set Conn = Server.CreateObject("ADODB.Connection")

cn.Open strConnect

'Check to be sure a Recordset is not already opened:
If rstOrder.State = 1 Then
rstOrder.Close
Set rstOrder = Nothing
End If

With rstOrder
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CacheSize = 50
.Source = "Select * from yourtablename"
.ActiveConnection = cn
.Open
End With



doc.Bookmarks("OrderDetails").Select
Do
With objWord.Selection
.TypeText vbTab
.TypeText rstOrder.Fields("Quantity")
.TypeText vbTab
.TypeText rstOrder.Fields("ProductName")
.TypeParagraph
End With
rstOrder.MoveNext
Loop While Not (rstOrder.EOF)


rstOrder.Close
Set rstOrder = Nothing
cn.Close
Set cn = Nothing
Screen.MousePointer = vbNormal

Exit Sub

AdoError:

Dim errLoop As Error
Dim strError As String

myI = 1

StrTmp = StrTmp & vbCrLf & "VB Error # " & Str(Err.Number)
StrTmp = StrTmp & vbCrLf & " Generated by " & Err.Source
StrTmp = StrTmp & vbCrLf & " Description " & Err.Description

' Display Error Information for each Error object.
Set Errs1 = cn.Errors
For Each errLoop In Errs1
With errLoop
StrTmp = StrTmp & vbCrLf & "Error #" & myI & ":"
StrTmp = StrTmp & vbCrLf & " ADO Error #" & .Number
StrTmp = StrTmp & vbCrLf & " Description " & .Description
StrTmp = StrTmp & vbCrLf & " Source " & .Source
myI = myI + 1
End With
Next

MsgBox StrTmp
rstOrder.Close
Set rstOrder = Nothing
cn.Close
Set cn = Nothing

End Sub
'******add a reference to ado in project

[Matt_T_hat]

Brillient you have saved me from death at 1200 (deadline time) I will post here again when I have tried out the code you have posted but in the mean time - T H A N K Y O U !

[Matt_T_hat]

Originally posted by Matt_T_hat
Brillient you have saved me from death at 1200 (deadline time) I will post here again when I have tried out the code you have posted but in the mean time - T H A N K Y O U !

or possibly mostly thankyou... I forgot to mention that this would be running in a module in Access itself... Should still work though I guess...

one would assume that

'place your database here
'Check to be sure the connection is not already opened:
Path = "C:\orders.mdb"
If cn.State = 1 Then
cn.Close
Set cn = Nothing
End If
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";"
' Set Conn = Server.CreateObject("ADODB.Connection")

should be left out...

[adocwra]

inside of access you can use DAO add a reference to the project
just inccase you have problem with ado
Private Sub cmdOpenRS_Click()

On Error GoTo AdoError
Dim strSQL As String

Dim rstOrder As New DAO.Recordset
Dim dbProducts As New DAO.Database



Screen.MousePointer = vbHourglass
strSQL = "Select * from orders"
Set dbProducts = CurrentDb
Set rstOrder = dbProducts.OpenRecordset(strSQL, dbOpenSnapshot)

doc.Bookmarks("OrderDetails").Select
Do
With objWord.Selection
.TypeText vbTab
.TypeText rstOrder.Fields("Quantity")
.TypeText vbTab
.TypeText rstOrder.Fields("ProductName")
.TypeParagraph
End With
rstOrder.MoveNext
Loop While Not (rstOrder.EOF)


rstOrder.Close
Set rstOrder = Nothing
dbProducts.Close
Set dbProducts = Nothing
Screen.MousePointer = vbNormal

Exit Sub

AdoError:

Dim errLoop As Error
Dim strError As String

myI = 1

StrTmp = StrTmp & vbCrLf & "VB Error # " & Str(Err.Number)
StrTmp = StrTmp & vbCrLf & " Generated by " & Err.Source
StrTmp = StrTmp & vbCrLf & " Description " & Err.Description

' Display Error Information for each Error object.
Set Errs1 = cn.Errors
For Each errLoop In Errs1
With errLoop
StrTmp = StrTmp & vbCrLf & "Error #" & myI & ":"
StrTmp = StrTmp & vbCrLf & " Dao Error #" & .Number
StrTmp = StrTmp & vbCrLf & " Description " & .Description
StrTmp = StrTmp & vbCrLf & " Source " & .Source
myI = myI + 1
End With
Next

MsgBox StrTmp
rstOrder.Close
Set rstOrder = Nothing
dbProducts.Close
Set dbProducts = Nothing



End Sub

[Matt_T_hat]

Now that is good. I've just come to say that the modified version I have made fails and behold you have posted an inside access version

Nice one thanks.

BTW: for those without time-telling-devices I am still working past the dead line so I am extra thankfull for every moment I save.

[adocwra]

you're welcome
you still have to get the word object
i didnt do that

[adocwra]

oops i forgot to fix the error statement
just remove the cn,errors statement s

[Matt_T_hat]

I get Invalid Use of New Keyword for:

Dim rstOrder As New DAO.Recordset

I feel I am 99% there......

[adocwra]

take off new it doesnt like it i guess
it will work then

as long as the word object is initialized and you remmed out the error statements
' Display Error Information for each Error object.
'Set Errs1 = cn.Errors
'For Each errLoop In Errs1
'With errLoop
'StrTmp = StrTmp & vbCrLf & "Error #" & myI & ":"
'StrTmp = StrTmp & vbCrLf & " Dao Error #" & .Number
'StrTmp = StrTmp & vbCrLf & " Description " & .Description
'StrTmp = StrTmp & vbCrLf & " Source " & .Source
'myI = myI + 1
'End With
'Next

[Matt_T_hat]

Originally posted by adocwra
take off new it doesnt like it i guess
it will work then

I'd already removed the New from both lines and it worked with that fine, whoever

Set rstOrder = dbProducts.OpenRecordset(strSQL, dbOpenSnapshot)

always results in error handleing being called.

strSQL = "[WORD_Short]"

gives the name of a query... but it says it can not find the thing... hmm says I.

Thoughts?

BTW: we have mannaged to get far enough with everything else to buy further deadline grace.

[adocwra]

are you using a query or sql statement

[adocwra]

Dim qry As QueryDef
Set dbProducts = CurrentDb
Set qry = dbProducts.QueryDefs("qrycust")
this opens up a query

[Matt_T_hat]

Originally posted by adocwra
Dim qry As QueryDef
Set dbProducts = CurrentDb
Set qry = dbProducts.QueryDefs("qrycust")
this opens up a query

Ok... right my mistake thanks.

I'll let you know how this pan's out

[Matt_T_hat]

but I now face a new problem

Private Function DoLoop(BM_Loco As String, LongVer As Boolean) As Boolean
Dim strSQL As String
Dim rstOrder As QueryDef
Dim dbProducts As DAO.database
Dim ThisIsIt
Dim DoResult As Boolean
 
On Error GoTo AdoError
Screen.MousePointer = vbHourglass
DoResult = False
 
      
    'The Long Version
    '================
      If LongVer = True Then
            strSQL = "[WORD_Long]"
            Set dbProducts = CurrentDb
            Set rstOrder = dbProducts.QueryDefs(strSQL)
            Do
                CopyThis rstOrder.Fields("Long_Note")
                PasteThis BM_Loco
                rstOrder.MoveNext
            Loop While Not (rstOrder.EOF)
            DoResult = True
      Else
'Etc....

I am told that rstorder.movenext is "method or data member not found". The problem is I am now about as far out on a limb as I can get and tredding water as far as ability to understand what I am dealing with. I have little doubt as to my ability to get there sooner or later but I fear it could be later...

I guess this is where I am told "Duh - You cant **** you have to #### instead if you want to $$$$$$$"

Sigh.

I think I do not undertand these objects all that well.

[Matt_T_hat]

next I tried

If Not (rstOrder.EOF) Then rstOrder.MoveNext

this leads me to think that I am using the wrong language to deal with my open query as it objected to the EOF in the If Not...

[adocwra]

i forget to tell you to add the querydef to a recordset
[code]
Dim qry As QueryDef
Dim rst As Recordset

Set qry = db.QueryDefs(strSQL)
'if you have to pass params
' qry.Parameters("pStartDt") = fStartDt
' qry.Parameters("pEndDt") = fEndDt
Set rst = qry.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Do
CopyThis rst.Fields("Long_Note")
PasteThis BM_Loco
rst.MoveNext
Loop While Not (rst.EOF)
DoResult = True

[Matt_T_hat]

It all looks so shokingly easy when someone explains it.



Thankyou.

[Matt_T_hat]

Private Function DoLoop(BM_Loco As String, LongVer As Boolean) As Boolean
Dim strSQL As String
Dim qry As QueryDef
Dim rstOrder As Recordset 'QueryDef
Dim dbProducts As DAO.database
Dim ThisIsIt
Dim DoResult As Boolean
 
On Error GoTo AdoError
Screen.MousePointer = vbHourglass
DoResult = False
      
         If LongVer = True Then
           'Do long stuff with word
        Else
      'The Short Version
            strSQL = "[WORD_Short]"
            Set qry = db.QueryDefs(strSQL)
            Set dbProducts = CurrentDb
            Set rstOrder = qry.OpenRecordset(dbOpenSnapshot, dbReadOnly)
            Do While Not (rstOrder.EOF)
                ThisIsIt = rstOrder.Fields("Name") & vbTab & _
                rstOrder.Fields("Price") & " + VAT at 17.5%"
                TypeThis ThisIsIt, BM_Loco
                rstOrder.MoveNext
            Loop
            DoResult = True
      End If
  
    rstOrder.Close
    Set rstOrder = Nothing
    cn.Close
    Set cn = Nothing
    
    Screen.MousePointer = vbNormal
 
GoTo exitme         'skip the error block
AdoError:
Screen.MousePointer = vbNormal
FrErr "Access-to-Word_AutoBM (" & DoResult & ")"
On Error GoTo exitme
    rstOrder.Close
    Set rstOrder = Nothing
    cn.Close
    Set cn = Nothing
exitme:
DoLoop = DoResult
End Function

I find that this bombs to the on error section which errors and thus quits it.

Set qry = db.QueryDefs(strSQL)

[Matt_T_hat]

I am definitly going to need some tutorials on this subject.

If you have a moment after spotting where I've cocked up (if I don't guess first) I could do with a little explination.

EDIT:

Set dbProducts = CurrentDb

This looks like it's serplus to requirments.

[adocwra]

off the top of my head i see



Set qry = db.QueryDefs(strSQL)
Set dbProducts = CurrentDb


it should be

Dim db As DAO.database

set db=currentdb
then db.querydef(strsql)

then i see

strSQL = "[WORD_Short]"

you dont need square brackets
i am assuming strsql="Access Query Name"
strSQL = "WORD_Short"

then i see
Dim dbProducts As DAO.database
and

Dim qry As QueryDef
Dim rstOrder As Recordset 'QueryDef
which should be
Dim qry As DAO.QueryDef
Dim rstOrder As DAO.Recordset 'QueryDef
and you have to make sure you have a reference to DAO

in tools Reference

Microsoft DAO xx.x Object Library


let me look further

[adocwra]

try this it compiles so no code problems but I cant run it without spending a whole bunch of time
let me know

Private Function DoLoop(BM_Loco As String, LongVer As Boolean) As Boolean
Dim strSQL As String
Dim qry As DAO.QueryDef
Dim rstOrder As DAO.Recordset 'QueryDef
Dim db As DAO.Database '.database
Dim ThisIsIt
Dim DoResult As Boolean

On Error GoTo AdoError
Screen.MousePointer = vbHourglass
DoResult = False
      
         If LongVer = True Then
           'Do long stuff with word
        Else
      'The Short Version
            strSQL = "WORD_Short"
            Set db = CurrentDb 'CurrentDb
            Set qry = db.QueryDefs(strSQL)
 
            Set rstOrder = qry.OpenRecordset(dbOpenSnapshot, dbReadOnly)
            Do While Not (rstOrder.EOF)
                ThisIsIt = rstOrder.Fields("Name") & vbTab & _
                rstOrder.Fields("Price") & " + VAT at 17.5%"
               ' TypeThis ThisIsIt, BM_Loco
                rstOrder.MoveNext
            Loop
            DoResult = True
      End If
  
    rstOrder.Close
    Set rstOrder = Nothing
'not used
'    cn.Close
'    Set cn = Nothing
    
    Screen.MousePointer = vbNormal

GoTo exitme         'skip the error block
AdoError:
Screen.MousePointer = vbNormal
'FrErr "Access-to-Word_AutoBM (" & DoResult & ")"
On Error GoTo exitme
    rstOrder.Close
    Set rstOrder = Nothing
'    cn.Close
'    Set cn = Nothing
exitme:
DoLoop = DoResult
End Function

[Matt_T_hat]

Originally posted by adocwra
try this it compiles so no code problems but I cant run it without spending a whole bunch of time
let me know

adocwra go to the top of the class.


Don't I look stupid. I was useing an inconsistant nameing method and I needed a DAO.Recordset to go with the DAO.database where as I had now "DAO.".

The DAO I would not have guessed but it seems so obviouse now and the wrong order with wrong named stuff is just shocking.

I will send myself to stand in the corner!! "F" For you this term Mr Hat!

[adocwra]

if you dont specify dao and in the reference ADO is checked it will assume ADO

I had to do a lot of conversions at work and this was the number one problem

[Matt_T_hat]

Originally posted by adocwra
if you dont specify dao and in the reference ADO is checked it will assume ADO

I had to do a lot of conversions at work and this was the number one problem

Perhaps I need to find out a bit more about this subject... like anything for example.

[adocwra]

i guess ado alphabetically is before dao
do when you declare a variable to a database and a recordset it will assume ado not dao
and the ado has different properties and methods than dao
plus because mdac was installed funky the had to have both for them to work

we had thousands of spreadsheets all over the network that had problems once they upgraded the workstation mdac

but luckily its a simple fix if it doesnt use ado remove it else specifically declare the variable as a dao.database /recordset