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

vba 2010 outlook

When i get records form a record set "tp" what is the correct record count.
I'm using "getrows" and placing the rows into an array

example: tp = 24

but the UBound(dArrp)  comes out to 14 in debug mode ?




' reset the recordset
        rs.MoveFirst
       ' put the items into an array for emailing each row.
       dArrp = rs.GetRows(tp)
  
' dig through the records and email as needed
For i = LBound(dArrp) To UBound(dArrp) 

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
do:
rs.MoveLast
rs.MoveFirst

Open in new window


so it populates everything first
0
FordraidersAuthor Commented:
crystal, I have that in a upper portion of the code.
but tried it again..still comes out to 13 ?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
would you post the whole function? thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FordraidersAuthor Commented:
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)

Open in new window

0
FordraidersAuthor Commented:
Global dArrp As Variant
Global dArrpOrig As Variant

sorry forgot this :

fordraiders
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks, I am looking !
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
tp is dimensioned here but I do not see where it is being assigned before it is tested
0
FordraidersAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
did that work for you?
0
FordraidersAuthor Commented:
the do loop....yes
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ;)  ~
0
FordraidersAuthor Commented:
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
0
FordraidersAuthor Commented:
Thats the rest...i do not  see how it affects recordset and array...But curious !!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
FordraidersAuthor Commented:
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..
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
# is 1 or 2  ... or is that 0 or 1 ...

I'll have to look up some code where I used it
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
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 !!!!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
great! glad you got it, brett
0
FordraidersAuthor Commented:
Thanks for the help !!!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, brett ~ happy to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.