We help IT Professionals succeed at work.

best way to handle a loop

Pioneermfg
Pioneermfg asked
on
230 Views
Last Modified: 2010-04-07
I am writing an application that extracts invoice data from a table.  My problem is that an invoice can contain more than one line of data.  I am counting (x) but don't know how to display in the textboxes the first result set.  I believe an array is the solution, but have never ever understood arrays.  I have 26 elements (text boxes) does this mean an arrary(26) or array(1, 26).  then how do I loop and populate the text boxes with the first result set?


Dim ssql As String
Dim esql As String
Dim strvar As String
Dim datevar As String
Dim rs As ADODB.Recordset
Dim rse As ADODB.Recordset
Dim msgr As VbMsgBoxResult
Dim x As Integer

strvar = txtInv.Text
x = 0
    ssql = "SELECT * "
    ssql = ssql & "FROM CADORDERS "
    ssql = ssql & "where SOPNUMBE = '" & strvar & "'"
       
    Set rs = New ADODB.Recordset
        With rs
            .Open ssql, CN, adOpenDynamic
       
        Do
            If rs.BOF And rs.EOF Then
                msgr = MsgBox("Invoice not Found, Try again?", vbYesNo)
                   If msgr = vbYes Then
                        MsgBox "Please check Invoice Number and try again"
                        txtInv.Text = ""
                        txtInv.SetFocus
                        Exit Function
                    Else
                        msgr = MsgBox("Not in Database, would you like to add invoice?", vbYesNo)
                            If msgr = vbYes Then
                                cmdArec.Enabled = True
                                cmdArec.SetFocus
                                Exit Function
                            Else
                                msgr = MsgBox("would you like to exit program?", vbYesNo)
                                    If msgr = vbYes Then
                                        CN.Close
                                        Unload Me
                                        End
                                    Else
                                        txtInv.Text = ""
                                        txtInv.SetFocus
                                        Exit Function
                                    End If
                            End If
                    End If
            Else
                   
                    txtPPCad.Text = rs(3)
                    txtShipD.Text = rs(0)
                    txtFrtCad.Text = rs(4)
                    txtOrd.Text = rs(1)
                    txtTaxCad.Text = rs(5)
                    txtTOthCad.Text = rs(6)
                    txtDAmtCad.Text = rs(9)
                    txtIFCad.Text = rs(10)
                    txtTInvCad.Text = rs(8)
                    txtBgstCad.Text = rs(11)
                    txtCgstCad.Text = rs(12)
                    txtOCCad.Text = rs(13)
                    txtTCostCad.Text = rs(14)
                    txtPSPCad.Text = rs(3)
                    txtPSCCad.Text = rs(15)
                    txtGPCad.Text = rs(16)
                    txtScommCad.Text = rs(17)
                    txtGSTColCad.Text = rs(5)
                    txtGSTPDCad.Text = rs(18)
                    txtGSTDiffCad.Text = rs(19)
                    txtQty.Text = rs(37)
                    txtPCM.Text = rs(38)
                    txtTCProd.Text = rs(39)
                    txtBillCost.Text = rs(40)
            End If
            rs.MoveNext
            x = x + 1
          Loop Until rs.EOF = True
        End With
End Function
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi Pioneermfg,

Sorry, but it's unclear to me what you're trying to do.  Could you try another pass at explanation?   :)

Regards,

Patrick

Author

Commented:
When the query executes, the loop checks for the invoice number to make sure it is in the table.  If not, options are given. If the invoice number does exist, then each text box is given the result from the record set. This works fine if only one line item is on an invoice.  Lets say invoice AAA has 2 line items.  During the first execution of the loop, the text boxes are given values from the recordset for the first line item on invoice AAA.  Since EOF is not true, the loop executes again.  The data from the first loop is replaced with the  data from the second loop.  I need to do computations of the each line item from invoice AAA and can not do that at this time.  I believe that an array is the answer, but I have no idea how to make that happen.
CERTIFIED EXPERT
Top Expert 2010

Commented:
> I need to do computations of the each line item from invoice AAA and can not do that at this time.

What sort of computations?

Author

Commented:
The invoice is expressed in Canadian dollars.  I need to convert those numbers into US Dollars.
CERTIFIED EXPERT
Top Expert 2010

Commented:
Why do you need an array?  Instead of:

                   txtPPCad.Text = rs(3)

etc., why not:

                   txtPPCad.Text = Format(rs(3) * CAD_to_US_Rate, "$#,##0.00;($#,##0.00)")

etc.

Patrick
CERTIFIED EXPERT

Commented:
It looks like you need an array like this  rs(x, 40)  where x = total number of records you are expecting.

'---- start of loop

x = x + 1
rs(x,1) = value
rs(x,2) = value


'--- end of loop

At the conclusion x holds the 'number of records' value.  To loop through the array you can do something like this....

For J1 = 1 to x
      For J2 = 1 to 40
              debug.print rs(J1, J2)
      Next J2
Next J1

Or in your case

J1 = J1 + 1
me.textbox1 = rs(J1,1)
me.textbox2 = rs(J1,2)


Scott C.














Author

Commented:
Scott;
  forgive me, but I don't understand arrays.  in your statement  "rs(x,1) = value"  what is "value"?  Is it the the total of "X"? so as to read "rs(x,1)=x?

Author

Commented:
when I step through the code, I retrieve each line item for an invoice (lets assume that invoice 123 has two line items).  What displays in the textboxes in the last line item for invoice 123.  If i put in rs.movefirst, and mouse over the values of the textboxes in code, I see the first line item data. The form however still displays the last line item data retrieved. I guess I need to know how to display the first line item when an invoice has more than one line item of data.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can fill the array with one line of code using GetRows():

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthgetrows.asp

/gustav
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Scott!
CERTIFIED EXPERT

Commented:
You're welcome!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.