populating an array but the upper bound does not equal the records in the recordset

do:

rs.MoveLast
rs.MoveFirst

Select allOpen in new window

so it populates everything first

crystal, I have that in a upper portion of the code.
but tried it again..still comes out to 13 ?

would you post the whole function? thanks

relevant code:

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sQuery As String
Dim db2 As New ADODB.Connection
Dim rs2 As New ADODB.Recordset
Dim sQuery2 As String
Dim tp As Long
Dim dbconnstring As String
Dim dbconnstring2 As String


Dim Nf1 As String
Dim cArrSql As Variant
Dim Cd As String
Dim X As Integer
Dim sSpecialChars As String
Dim i As Integer
Dim w As String
Dim Counta As Integer
Dim Countb As Integer
Dim cDo As String
Dim cc As Boolean
Static oRE As Object


' variables for the email here
Dim strForm As String
Dim objFolder As Folder
Dim objItem As Object 'mailitem
Dim objPage As Page


' domain variables for extracting Names from email addresses
Dim j As Long, AtSign As Long
Dim Locale As String, DomainPart As String
Dim Domain As String
Dim s As String ' Seller Name
Dim pi As String '  Proposal Id
Dim es  As String '  email status
Dim ef As String ' email feedback
Dim cn As String ' Company Name
Dim ea As String ' Email Address

'On Error GoTo ErrorHandler:



'lets get the id in case we need it
Dim racfid As Variant

rid = VBA.Environ("username")


' for 2015 manyy job
sQuery = "EXECUTE [SS_PROGRAM_WORKFLOW].dbo.[email_sent];"

dbconnstring = "provider=sqloledb;Server=xxxxx.xxxger.com;Database=ssxxx;Uid=xxxxxer;Pwd=xxxxxxprd;"
   db.CursorLocation = adUseClient
   db.Open dbconnstring
db.CommandTimeout = 4000
                With rs
                 .ActiveConnection = db
                 .Open sQuery
                
                End With

If tp = 0 Then
  '   On Error Resume Next
    rs.MoveLast
       rs.MoveFirst
       tp = rs.RecordCount
End If

If tp <> 0 Then
' looking to see if we find any records within 30 days
rs.MoveLast
   rs.MoveFirst
      tp = rs.RecordCount

Else '  it is a zero count get out of routine
rs.Close
     db.Close
       Set rs = Nothing
         Set db = Nothing
           Exit Sub
End If


' only one email to send
If tp >= 1 Then   ' must change back here. for testing only
  
  ' reset the recordset
       rs.MoveLast
            rs.MoveFirst
       ' put the items into an array for emailing each row.
       dArrp = rs.GetRows
        ' just in case i need it
      '  dArrpOrig = dArrp


  
' dig through the records and email as needed
For i = LBound(dArrp) To UBound(dArrp)

Select allOpen in new window

Global dArrp As Variant
Global dArrpOrig As Variant

sorry forgot this :

fordraiders

thanks, I am looking !

tp is dimensioned here but I do not see where it is being assigned before it is tested

the array thing just baffles me. I use it all the time in excel.

went with boring..
Do While Not rs.EOF

rs.movenext
loop

did that work for you?

the do loop....yes

I didn't see anything wrong with the array loop, well not the first line anyway since you didn't post the rest of the code ;)  ~

For i = LBound(dArrp) To UBound(dArrp)

tp = 1
Do While Not rs.EOF
' lets load the form values for emailing


strForm = "IPM.Note.proposalm"
    Set objFolder = _
        Application.ActiveExplorer.CurrentFolder
           Set objItem = _
                        objFolder.Items.Add(strForm)
                           objItem.To = "xxx.xxxxxx@xxxxxx.com"
                               objItem.Subject = "Proposal Survey. Please Respond."
' ok now we have to extract the name from the email address

ea = rs.Fields(5) ' who is the actual  email going to - address

'If rs.Fields(5) = "" Then
's = "david.powell@grainger.com"
'End If
s = rs.Fields(5)
s = Left(s, InStr(s, "@") - 1)
' now take out the .
s = Replace(s, ".", " ")
s = s

' set a on object on the form
' for reference
'txtSname = ProposalName = s seller name
'txtProposalId = ProposalId  pi
'txtEstatus = EmailStatus  es
'txtFeedback = wlFeedback  ef
'Label9 = Company Name
'Column Names
 '0 proposal_id
 '1 specialist_responsible
 '2 customer_name
 '3 date_proposal_completed
 '4 seller_racf_id
 '5 seller_email
 '6 wl_emailed_on
 '7 wl_survey_completed_on
 '8 wl_status
 '9 wl_Q1_answer
 '10 wl_Q2_answer
 '11 wl_Q3_answer
 '12 wl_Feedback
 '13 wl_ReasonLoss

pi = rs.Fields(0)


'On Error Resume Next
'es = "emailed30" 'rs.Fields(8) '  Status
   
' a one time shot of emailed30
If es = "" Then
es = "emailed30"
End If

cn = rs.Fields(2) 'Company Name

  ' hard code the email name for testing
 '  objItem.To = ea
    '   objItem.To = "xxx.xxx@xxxx.com"  ' for reference same as above
           objItem.GetInspector.ModifiedFormPages("Survey").Controls("txtSname").Value = s
               objItem.GetInspector.ModifiedFormPages("Survey").Controls("txtProposalId").Text = pi
                   objItem.GetInspector.ModifiedFormPages("Survey").Controls("txtEstatus").Value = es
                     objItem.GetInspector.ModifiedFormPages("Survey").Controls("txtCompany").Value = cn
            objItem.Save
    objItem.Send

next i

Thats the rest...i do not  see how it affects recordset and array...But curious !!

actually, I was wrong -- there is something wrong

For i = LBound(dArrp, #) To UBound(dArrp,#)

with getrows, you also need a second argument for the column since it is a two-dimensional array. The first subscript identifies the field and the second identifies the record number

well, ok i tried that in the getrows itself...but never have tried in the array call itself.

what is the pound sign ?

But I use getrows in excel all the time and never declared those particulars for getrows or the array ?
curious..

ok...

lngCount = UBound(dArrp, 2) + 1
       
' dig through the records and email as needed
For i = LBound(dArrp, 2) To UBound(dArrp, 2)

worked...Thanks !!!!

great! glad you got it, brett

Thanks for the help !!!

you're welcome, brett ~ happy to help