Help with my code

G

Guest

Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from access
form.

I have searching for a while now to see any sample code to have this work,
but none seems available.

could you kindly look into this code and let me know where did I go wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String, strPN As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " & strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " & _
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
 
G

Guest

Hi

I did not get an error, but it will not display all records. say if there is
two records it will not display both records. It will only display 1st record
and repeat it.

norhaya
Alex Dybenko said:
Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


norhaya said:
Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from access
form.

I have searching for a while now to see any sample code to have this work,
but none seems available.

could you kindly look into this code and let me know where did I go wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String, strPN As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " & strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " & _
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
 
A

Alex Dybenko

Then try to debug your code - check how many records recordset returns, if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


norhaya said:
Hi

I did not get an error, but it will not display all records. say if there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
Alex Dybenko said:
Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


norhaya said:
Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String, strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " & _
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one records.
Can you see the code if there is anything is missing. Do u know any link or
sample code where I could view?

Thanks for the help,

Alex Dybenko said:
Then try to debug your code - check how many records recordset returns, if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


norhaya said:
Hi

I did not get an error, but it will not display all records. say if there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
Alex Dybenko said:
Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String, strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " & _
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
in general, as I see, your code looks ok, difficult to say without having
your data. try to stop at:
Set rs = dbs.OpenRecordset(strSQL)
then in debug window (ctrl+G) type:
?strSQL
it will print SQL
copy it and paste into a new query SQL and run it

look - how many records it produces?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



norhaya said:
Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any link
or
sample code where I could view?

Thanks for the help,

Alex Dybenko said:
Then try to debug your code - check how many records recordset returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


norhaya said:
Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " &
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Pardon me for jumping in, but I don't see anywhere in your code that you
move to the next record. It looks to me as if you look at the first record
multiple times.

I may be missing something, but I think you need a rs.MoveNext inside the
loop.

norhaya said:
Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any link
or
sample code where I could view?

Thanks for the help,

Alex Dybenko said:
Then try to debug your code - check how many records recordset returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


norhaya said:
Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " &
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi

thank you and will try it at work. PS: when you say paste to a new query
mean that I paste on the previous query or another new line. where can i
upload a file so that you can see my data?

Alex Dybenko said:
Hi,
in general, as I see, your code looks ok, difficult to say without having
your data. try to stop at:
Set rs = dbs.OpenRecordset(strSQL)
then in debug window (ctrl+G) type:
?strSQL
it will print SQL
copy it and paste into a new query SQL and run it

look - how many records it produces?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



norhaya said:
Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any link
or
sample code where I could view?

Thanks for the help,

Alex Dybenko said:
Then try to debug your code - check how many records recordset returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " &
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi John

here is my original code; thank you and look forward to your feedback

Dim dbs As DAO.Database
Dim strSQL As String
Dim strjpl1 As String
Dim strsubject As String
Dim strpn As String, strpo_qty As String, strdesc As String
Dim strpartsline As String

strpn = Me.PN
strdesc = Me.DESCRIPTION
strpo_qty = Me.PO_QTY
strjpl1 = Me.JPL1
strsubject = "Testing Looping Through All records"

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strjpl1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
rs.MoveFirst
strpartsline = strpartsline & strpn & " " & strdesc & " "
& strpo_qty & vbCrLf & vbCrLf
rs.MoveNext
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, strsubject,
strpartsline, -1
rs.Close
Set rs = Nothing

John Spencer said:
Pardon me for jumping in, but I don't see anywhere in your code that you
move to the next record. It looks to me as if you look at the first record
multiple times.

I may be missing something, but I think you need a rs.MoveNext inside the
loop.

norhaya said:
Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any link
or
sample code where I could view?

Thanks for the help,

Alex Dybenko said:
Then try to debug your code - check how many records recordset returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " &
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

John's right: you are looking at the first record multiple times.

That's because you've got rs.MoveFirst inside your loop. As well, b =
rs.RecordCount isn't necessarily going to give you an accurate count: you
usually need to move to the end of the recordset before RecordCount is
correct. However, you don't really seem to need to know the recordcount in
your code. Try replacing:

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
rs.MoveFirst
strpartsline = strpartsline & strpn & " " & strdesc & " "
& strpo_qty & vbCrLf & vbCrLf
rs.MoveNext
Next b
Else
MsgBox "The recordset contained no Records"
End If

with

Set rs = dbs.OpenRecordset(strSQL)
If Not rs.EOF Then
Do While rs.EOF = False
strpartsline = strpartsline & strpn & " " & strdesc & " "
& strpo_qty & vbCrLf & vbCrLf
rs.MoveNext
Loop
Else
MsgBox "The recordset contained no Records"
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


norhaya said:
Hi John

here is my original code; thank you and look forward to your feedback

Dim dbs As DAO.Database
Dim strSQL As String
Dim strjpl1 As String
Dim strsubject As String
Dim strpn As String, strpo_qty As String, strdesc As String
Dim strpartsline As String

strpn = Me.PN
strdesc = Me.DESCRIPTION
strpo_qty = Me.PO_QTY
strjpl1 = Me.JPL1
strsubject = "Testing Looping Through All records"

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strjpl1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
rs.MoveFirst
strpartsline = strpartsline & strpn & " " & strdesc & " "
& strpo_qty & vbCrLf & vbCrLf
rs.MoveNext
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, strsubject,
strpartsline, -1
rs.Close
Set rs = Nothing

John Spencer said:
Pardon me for jumping in, but I don't see anywhere in your code that you
move to the next record. It looks to me as if you look at the first record
multiple times.

I may be missing something, but I think you need a rs.MoveNext inside the
loop.

norhaya said:
Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any link
or
sample code where I could view?

Thanks for the help,

:

Then try to debug your code - check how many records recordset returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " &
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Alex

I tried but it doesnt work. It still give me 3 records but display 1 record
(current record), repeating same record.

even with the rs.movenext, doesnt seem to work.

hmmm...it's cracking my head now.

Alex Dybenko said:
Hi,
in general, as I see, your code looks ok, difficult to say without having
your data. try to stop at:
Set rs = dbs.OpenRecordset(strSQL)
then in debug window (ctrl+G) type:
?strSQL
it will print SQL
copy it and paste into a new query SQL and run it

look - how many records it produces?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



norhaya said:
Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any link
or
sample code where I could view?

Thanks for the help,

Alex Dybenko said:
Then try to debug your code - check how many records recordset returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " &
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Douglas

Thank you, I tried but still give me same results. it loops but not getting
the next record though. it will show same records twice.


Douglas J Steele said:
John's right: you are looking at the first record multiple times.

That's because you've got rs.MoveFirst inside your loop. As well, b =
rs.RecordCount isn't necessarily going to give you an accurate count: you
usually need to move to the end of the recordset before RecordCount is
correct. However, you don't really seem to need to know the recordcount in
your code. Try replacing:

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
rs.MoveFirst
strpartsline = strpartsline & strpn & " " & strdesc & " "
& strpo_qty & vbCrLf & vbCrLf
rs.MoveNext
Next b
Else
MsgBox "The recordset contained no Records"
End If

with

Set rs = dbs.OpenRecordset(strSQL)
If Not rs.EOF Then
Do While rs.EOF = False
strpartsline = strpartsline & strpn & " " & strdesc & " "
& strpo_qty & vbCrLf & vbCrLf
rs.MoveNext
Loop
Else
MsgBox "The recordset contained no Records"
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


norhaya said:
Hi John

here is my original code; thank you and look forward to your feedback

Dim dbs As DAO.Database
Dim strSQL As String
Dim strjpl1 As String
Dim strsubject As String
Dim strpn As String, strpo_qty As String, strdesc As String
Dim strpartsline As String

strpn = Me.PN
strdesc = Me.DESCRIPTION
strpo_qty = Me.PO_QTY
strjpl1 = Me.JPL1
strsubject = "Testing Looping Through All records"

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strjpl1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
rs.MoveFirst
strpartsline = strpartsline & strpn & " " & strdesc & " "
& strpo_qty & vbCrLf & vbCrLf
rs.MoveNext
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, strsubject,
strpartsline, -1
rs.Close
Set rs = Nothing

John Spencer said:
Pardon me for jumping in, but I don't see anywhere in your code that you
move to the next record. It looks to me as if you look at the first record
multiple times.

I may be missing something, but I think you need a rs.MoveNext inside the
loop.

Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any link
or
sample code where I could view?

Thanks for the help,

:

Then try to debug your code - check how many records recordset returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display 1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text from
access
form.

I have searching for a while now to see any sample code to have this
work,
but none seems available.

could you kindly look into this code and let me know where did I go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " " &
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
if db is not very big - then zip it and send to alexdyb AT hotmail DOT com,
I will look at it

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


norhaya said:
Hi

thank you and will try it at work. PS: when you say paste to a new query
mean that I paste on the previous query or another new line. where can i
upload a file so that you can see my data?

Alex Dybenko said:
Hi,
in general, as I see, your code looks ok, difficult to say without having
your data. try to stop at:
Set rs = dbs.OpenRecordset(strSQL)
then in debug window (ctrl+G) type:
?strSQL
it will print SQL
copy it and paste into a new query SQL and run it

look - how many records it produces?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



norhaya said:
Hi Alex

I debugged, I got 3 records. My data has 2 records. but when I loop, it
shows only 1 records. I tried all ways to loop but still shows one
records.
Can you see the code if there is anything is missing. Do u know any
link
or
sample code where I could view?

Thanks for the help,

:

Then try to debug your code - check how many records recordset
returns,
if
only one - then check SQL statement

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

I did not get an error, but it will not display all records. say if
there
is
two records it will not display both records. It will only display
1st
record
and repeat it.

norhaya
:

Hi,
did you get an error when run this code? where?

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi

Please help me with my code

I am trying to email all data in my query in a formatted text
from
access
form.

I have searching for a while now to see any sample code to have
this
work,
but none seems available.

could you kindly look into this code and let me know where did I
go
wrong.

I really do appreciate your help very much.

norhaya


Private Sub Command645_DblClick(Cancel As Integer)

Dim dbs As Database
Dim strSQL As String, strJPL1 As String, strSubject As String,
strPN
As
String, strDesc As String
Dim strPartsLine As String
Dim strShipQty1 As Integer

strPN = Me.PN
strDesc = Me.DESCRIPTION
strShipQty1 = Me.SHIP_QTY1
strJPL1 = Me.JPL1
strSubject = "Testing Looping Through All records"
strPartsLine = strPartsLine & strPN & " " & strDesc & " " &
strShipQty1
& vbCrLf & vbCrLf

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[JPL1]) ='" & strJPL1 & "'"
strSQL = strSQL & "order by ([2004 SHIP].[JPL1]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & rs.Fields("PN") & " "
&
_
rs.Fields("DESCRIPTION") & " " & rs.Fields("SHIP Qty1") & _
vbCrLf & vbCrLf
Next b
Else
MsgBox "The recordset contained no Records"
End If
Beep
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc,
strSubject,
strPartsLine, -1
rs.Close
Set rs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Send email message from Access 1
Loop Help 4
retrieve multiple data for one criteria - urgent help. 1
Help with my code Exporting to multiple excel spreadsheets 3
my VBA code to calculate median values is broken. :( 3
Help with code 1
Combo box help 2
Question for John Vinson 5

Top