Pioneermfg
asked on
best way to handle a loop
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
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
ASKER
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.
> 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?
What sort of computations?
ASKER
The invoice is expressed in Canadian dollars. I need to convert those numbers into US Dollars.
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
txtPPCad.Text = rs(3)
etc., why not:
txtPPCad.Text = Format(rs(3) * CAD_to_US_Rate, "$#,##0.00;($#,##0.00)")
etc.
Patrick
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.
'---- 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.
ASKER
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?
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?
ASKER
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.
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
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthgetrows.asp
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Scott!
You're welcome!
Sorry, but it's unclear to me what you're trying to do. Could you try another pass at explanation? :)
Regards,
Patrick