loop through record set

[kevcar40]

Hi
i have created a recordset
Set rs = DB.OpenRecordset("Qry_get_email", dbOpenDynaset,
dbSeeChanges)
the result is a list of files

what i would like to do is
loop throught the recordset
and return the name of each file to a text box one after the other
i.e
textbox1.value = rs.value record one
textbox1.value = rs.value record two
and so on

i have never tried and getting really lost

any help ideas

thanks

kevin

 

[Jack Leach]

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Qry_get_email")

'make sure there's records
If rs.Recordcount <> 0 Then
'position cursor on first record
rs.MoveFirst
'loop the recordset
While Not rs.EOF
'add the item to the textbox string
Me.txtBoxName = Me.txtBoxName & "; " & rs("FieldName")
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing



hth - be sure to add in handling for nulls.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[kevcar40]

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Qry_get_email")

'make sure there's records
If rs.Recordcount <> 0 Then
  'position cursor on first record
  rs.MoveFirst
  'loop the recordset
  While Not rs.EOF
    'add the item to the textbox string
    Me.txtBoxName = Me.txtBoxName & "; " & rs("FieldName")
    rs.MoveNext
  Wend
End If
rs.Close
Set rs = Nothing

hth - be sure to add in handling for nulls.  

--
Jack Leachwww.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931)








- Show quoted text -

sorry i think i mislead you
your code works fine
but what was after is :-

i would like to load the value of record 1 into the textbox, i am
then going to do something with it
i would then like to load the value of record 2 into the text box then
do something with it
and so on

thanks

kevin

 

[BruceM via AccessMonster.com]

Is there a reason you don't want to base a form on the recordset? That way
you could just go from one record to the next, making changes as you go.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Qry_get_email")

[quoted text clipped - 45 lines]

- Show quoted text -

sorry i think i mislead you
your code works fine
but what was after is :-

i would like to load the value of record 1 into the textbox, i am
then going to do something with it
i would then like to load the value of record 2 into the text box then
do something with it
and so on

thanks

kevin

 

[kevcar40]

Is there a reason you don't want to base a form on the recordset?  Thatway
you could just go from one record to the next, making changes as you go.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Qry_get_email")

[quoted text clipped - 45 lines]

- Show quoted text -

sorry  i think i mislead you
your code works fine
but what  was after is :-

i  would like to load the value of  record 1 into the textbox, i am
then going to do something with it
i would then like to load the value of record 2 into the text box then
do something with it
and so on

kevin

what i am looking to do is loop throught the record set
read contents of a field called fldname (this field holds the
location/name of a document)
i want to print this document
then move to the next record and repeat the procedure

kevin

 

[Jack Leach]

Hmmmm, ok... I guess I didn't read the original post well enough.

I'm trying to think why you would need to load these values into a textbox
before doing something with them? In any case, what do you need to do with
each value that's being pulled? Are they similar tasks, with only minor
variable differences that can be run through a 'globalized' function? Or do
you need to do something altogether different with each value?

I can't seem to come up with a good reason why someone would need to do
something completely different with each record... but if that is the case
then you're going to have a tough time automating it. If, in fact, this is
your goal, then next question (providing there's not a better way to handle
the entire situation) is whether this is a one-time thing or a recurring
thing.

If this is a one-time thing you are probably better off just doing it
manually. If it's a a recurring thing, then you may be able to set up a
module with a public function and a series of private ones to handle each
case. A module level Select Case setup, if you will...

Public Function ProcessValue(strValue As String)
Select Case strValue
Case "This": pfDoThis
Case "That": pfDoThat
Case "SomethingElse": pfDoSomethingElse
End Select
End Function
Private Function pfDoThis()
...
End Function
Private Function pfDoThat()
...
End Function
Private Function pfDoSomethingElse()
...
End Function
etc etc

Then in your loop you would call it like so:

While Not rs.EOF
ProcessValue rs("Fieldname")
rs.MoveNext
Wend



While this *may* accomplish something along the lines of what you're look
for, I want to warn again that this is not generally the best way to handle a
case. Usually, when we start doing things like this, it means that somewhere
else something is not set up ideally (generally this traces back to
Normalization problems). With a good, clean and solid data foundation we
should never really find ourselves having to go to such extremes.


How about some more info on what the ultimate task is? Maybe there's a much
easier way...


hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

well that's easy enough.... rather than putting the value in a textbox, run a
print command in the loop... (go to mvps.org/access/api and see the 18th one
in the list... shell execute... pass the lpOperation parameter as "print"
instead of "open", and call the fHandleFile function from your loop)

While Not rs.EOF
fHandleFile rs("fldname")
DoEventsoEventsoEvents
rs.MoveNext
Wend

this assumes the field "fldname" contains the complete path to the file.

Let us know if you need help with this ShellExecute thing...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Is there a reason you don't want to base a form on the recordset? That way
you could just go from one record to the next, making changes as you go.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Qry_get_email")
[quoted text clipped - 45 lines]

- Show quoted text -

sorry i think i mislead you
your code works fine
but what was after is :-

i would like to load the value of record 1 into the textbox, i am
then going to do something with it
i would then like to load the value of record 2 into the text box then
do something with it
and so on

kevin

what i am looking to do is loop throught the record set
read contents of a field called fldname (this field holds the
location/name of a document)
i want to print this document
then move to the next record and repeat the procedure

kevin

.

 

[kevcar40]

well that's easy enough.... rather than putting the value in a textbox, run a
print command in the loop... (go to mvps.org/access/api and see the 18th one
in the list... shell execute... pass the lpOperation parameter as "print"
instead of "open", and call the fHandleFile function from your loop)

While Not rs.EOF
  fHandleFile rs("fldname")
  DoEventsoEventsoEvents
  rs.MoveNext
Wend

this assumes the field "fldname" contains the complete path to the file.

Let us know if you need help with this ShellExecute thing...

--
Jack Leachwww.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931)

Is there a reason you don't want to base a form on the recordset?  That way
you could just go from one record to the next, making changes as you go.
kevcar40 wrote:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Qry_get_email")
[quoted text clipped - 45 lines]
- Show quoted text -
sorry  i think i mislead you
your code works fine
but what  was after is :-
i  would like to load the value of  record 1 into the textbox, iam
then going to do something with it
i would then like to load the value of record 2 into the text box then
do something with it
and so on
thanks
kevin

what i am looking to do is loop throught the record set
read contents of a field called fldname  (this field holds the
location/name of a document)
i want to print this document
then move to the next record and repeat the procedure

.- Hide quoted text -

- Show quoted text -

thank you very much for your time
the textbox will hold the whole file path and document name
what i want to do is print the document,
then move to the next document in the recordset
print it and same for each document till end of file

thanks

kevin

 

[kevcar40]

well that's easy enough.... rather than putting the value in a textbox,run a
print command in the loop... (go to mvps.org/access/api and see the 18th one
in the list... shell execute... pass the lpOperation parameter as "print"
instead of "open", and call the fHandleFile function from your loop)

While Not rs.EOF
  fHandleFile rs("fldname")
  DoEventsoEventsoEvents
  rs.MoveNext
Wend

this assumes the field "fldname" contains the complete path to the file..

Let us know if you need help with this ShellExecute thing...

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931)

Is there a reason you don't want to base a form on the recordset?  That way
you could just go from one record to the next, making changes as you go.
kevcar40 wrote:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Qry_get_email")
[quoted text clipped - 45 lines]
- Show quoted text -
sorry  i think i mislead you
your code works fine
but what  was after is :-
i  would like to load the value of  record 1 into the textbox,i am
then going to do something with it
i would then like to load the value of record 2 into the text box then
do something with it
and so on
thanks
kevin
--
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/20100...quoted text -
- Show quoted text -
what i am looking to do is loop throught the record set
read contents of a field called fldname  (this field holds the
location/name of a document)
i want to print this document
then move to the next record and repeat the procedure
kevin
.- Hide quoted text -

- Show quoted text -

thank you very much for your time
the textbox will hold the whole file path and document name
what i want to do is print the document,
then move to the next document in the recordset
print it and same for each document till end of file

thanks

kevin- Hide quoted text -

- Show quoted text -

i have loaded the function
i am now getting a comple error saying argument not optional

 

[Dirk Goldgar]

i have loaded the function
i am now getting a comple error saying argument not optional

Actually, that function would have to be modified to print instead of open
the file. Here's a function you can paste into the same module you created
for fHandleFile, and use to print files:

'------ start of code ------
Function fPrintFile(stFile As String)

' This function uses ShellExecute to print, rather than
' open, the file.

Dim lRet As Long, varTaskID As Variant
Dim stRet As String

'First try ShellExecute
lRet = apiShellExecute(hWndAccessApp, "print", _
stFile, vbNullString, vbNullString, 0&)

If lRet > ERROR_SUCCESS Then
stRet = vbNullString
lRet = -1
Else
Select Case lRet
Case ERROR_NO_ASSOC:
stRet = "Error: No associated application. Couldn't print!"
Case ERROR_OUT_OF_MEM:
stRet = "Error: Out of Memory/Resources. Couldn't print!"
Case ERROR_FILE_NOT_FOUND:
stRet = "Error: File not found. Couldn't print!"
Case ERROR_PATH_NOT_FOUND:
stRet = "Error: Path not found. Couldn't print!"
Case ERROR_BAD_FORMAT:
stRet = "Error: Bad File Format. Couldn't print!"
Case Else:
End Select
End If
fPrintFile = lRet & _
IIf(stRet = "", vbNullString, ", " & stRet)

End Function
'------ end of code ------

Print your files from the recordset using a statement like this:

fPrintFile rs("fldname")

 

[kevcar40]

Actually, that function would have to be modified to print instead of open
the file.  Here's a function you can paste into the same module you created
for fHandleFile, and use to print files:

'------ start of code ------
Function fPrintFile(stFile As String)

    ' This function uses ShellExecute to print, rather than
    ' open, the file.

    Dim lRet As Long, varTaskID As Variant
    Dim stRet As String

    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, "print", _
            stFile, vbNullString, vbNullString, 0&)

    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                stRet = "Error: No associated application.  Couldn't print!"
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources.. Couldn't print!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't print!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't print!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't print!"
            Case Else:
        End Select
    End If
    fPrintFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)

End Function
'------ end of code ------

Print your files from the recordset using a statement like this:

    fPrintFile rs("fldname")

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

ok thanks for reply getting there

i have modified the module as you suggested

the code is looping 3 times (number of files)
but not printing
so here we go

the recordset result is
C:\TEMP\Test.doc
C:\TEMP\Test 1.doc
C:\TEMP\Test 2.doc

the code under the command button is

Private Sub Command9_Click()
On Error GoTo err_Command9_Click
Dim rs As Recordset
Dim DB As Database
Set rs = CurrentDb.OpenRecordset("tbl_emailsBase")
MsgBox "!" ' testing the code gets here
While Not rs.EOF
MsgBox "7" ' testing the code gets here
fPrintFile rs("fldname")
DoEvents: DoEvents: DoEvents
rs.MoveNext
Wend
MsgBox "3" ' testing the code gets here
Exit_Command9_Click:
Exit Sub
err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub
So i know the code looped through the code

the module is as follows

Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

'===================================== start of code
==========================
Function fPrintFile(stFile As String) ' This function uses
ShellExecute to print, rather than open, the file.
Dim lRet As Long, varTaskID As Variant
Dim stRet As String

lRet = apiShellExecute(hWndAccessApp, "print", stFile,
vbNullString, vbNullString, 0&)


MsgBox "Fprint 1" ' testing code
If lRet > ERROR_SUCCESS Then
MsgBox "Fprint 1.1" ' testing code
stRet = vbNullString
lRet = -1
MsgBox "Fprint 10" ' testing code
Else
Select Case lRet
Case ERROR_NO_ASSOC:
stRet = "Error: No associated application. Couldn't
print!"
Case ERROR_OUT_OF_MEM:
stRet = "Error: Out of Memory/Resources. Couldn't
print!"
Case ERROR_FILE_NOT_FOUND:
stRet = "Error: File not found. Couldn't print!"
Case ERROR_PATH_NOT_FOUND:
stRet = "Error: Path not found. Couldn't print!"
Case ERROR_BAD_FORMAT:
stRet = "Error: Bad File Format. Couldn't print!"
Case Else:
End Select
MsgBox "Fprint 11" ' testing code
End If

fPrintFile = lRet & IIf(stRet = "", vbNullString, ", " & stRet)

MsgBox "Fprint 2" ' testing code
End Function
'===================================== end of code
==========================

when i run the message sequence is

!
7
fprint 1
fprint 11
fprint 2
7
fprint 1
fprint 11
fprint 2
7
fprint 1
fprint 11
fprint 2
3

any ideas where i going wrong on this

thanks again

kevin

 

[Dirk Goldgar]

Kevin -

Those results imply that fPrintFile is encountering an error, but you aren't
printing out the error message. Temporarily change this line:

fPrintFile rs("fldname")

to this:

Debug.Print fPrintFile(rs("fldname"))

Then run the function and look in the Immediate Window to see what the
function returns as an error message.

By the way, Is "fldname" really the name of the field in your table that
contains the file paths to print? If not, change that to the correct field
name.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)


ok thanks for reply getting there

i have modified the module as you suggested

the code is looping 3 times (number of files)
but not printing
so here we go

the recordset result is
C:\TEMP\Test.doc
C:\TEMP\Test 1.doc
C:\TEMP\Test 2.doc

the code under the command button is

Private Sub Command9_Click()
On Error GoTo err_Command9_Click
Dim rs As Recordset
Dim DB As Database
Set rs = CurrentDb.OpenRecordset("tbl_emailsBase")
MsgBox "!" ' testing the code gets here
While Not rs.EOF
MsgBox "7" ' testing the code gets here
fPrintFile rs("fldname")
DoEvents: DoEvents: DoEvents
rs.MoveNext
Wend
MsgBox "3" ' testing the code gets here
Exit_Command9_Click:
Exit Sub
err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub
So i know the code looped through the code

the module is as follows

Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

'===================================== start of code
==========================
Function fPrintFile(stFile As String) ' This function uses
ShellExecute to print, rather than open, the file.
Dim lRet As Long, varTaskID As Variant
Dim stRet As String

lRet = apiShellExecute(hWndAccessApp, "print", stFile,
vbNullString, vbNullString, 0&)


MsgBox "Fprint 1" ' testing code
If lRet > ERROR_SUCCESS Then
MsgBox "Fprint 1.1" ' testing code
stRet = vbNullString
lRet = -1
MsgBox "Fprint 10" ' testing code
Else
Select Case lRet
Case ERROR_NO_ASSOC:
stRet = "Error: No associated application. Couldn't
print!"
Case ERROR_OUT_OF_MEM:
stRet = "Error: Out of Memory/Resources. Couldn't
print!"
Case ERROR_FILE_NOT_FOUND:
stRet = "Error: File not found. Couldn't print!"
Case ERROR_PATH_NOT_FOUND:
stRet = "Error: Path not found. Couldn't print!"
Case ERROR_BAD_FORMAT:
stRet = "Error: Bad File Format. Couldn't print!"
Case Else:
End Select
MsgBox "Fprint 11" ' testing code
End If

fPrintFile = lRet & IIf(stRet = "", vbNullString, ", " & stRet)

MsgBox "Fprint 2" ' testing code
End Function
'===================================== end of code
==========================

when i run the message sequence is

!
7
fprint 1
fprint 11
fprint 2
7
fprint 1
fprint 11
fprint 2
7
fprint 1
fprint 11
fprint 2
3

any ideas where i going wrong on this

thanks again

kevin