Why will this statement not work??? 2

[rtshort]

Public Sub GetTotal()
Dim Labor As Integer
Dim Total As Currency
Dim LaborRate As Currency
LaborRate = 60#
Labor = LaborRate * Val(flexDA.TextMatrix(flexDA.Row, 2))
Total = Labor + Val(flexDA.TextMatrix(flexDA.Row, 3))
txtLaborTotal.Text = Total
End Sub
TextMatrix col2 is the Labor Hours.
TextMatrix col3 is the Part Price.
The txtLaborTotal.Text stays on 0 and never increases
I just want the value of the current row, not all rows. My intention is to put the LaborTotal and the PartPrice (added together) into the last column of the flexgrid,(Total) and keep a running SubTotal of the Total Column but I can't even get it into the temporary txtLaborTotal box. Every day above ground is a GOOD DAY!!!

 

[MichaelRed]

What are the values in [flexDA.Row]. How is the value of the row property being changed?

) Apr 26, 2001
Public Sub GetTotal()
Dim Labor As Integer
Dim Total As Currency
Dim LaborRate As Currency
LaborRate = 60#
Labor = LaborRate * Val(flexDA.TextMatrix(flexDA.Row, 2))
Total = Labor + Val(flexDA.TextMatrix(flexDA.Row, 3))
txtLaborTotal.Text = Total

'Try this to see what's What
Debug.Print flexDA.Row, flexDA.TextMatrix(flexDA.Row, 2)), flexDA.TextMAtrix(flexDA.Row, 3)), Labor, Total

Debug.Print 'Place a break point here, LOok at what ever values you want
End Sub



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rtshort]

(flexDA.Row, 2)is the "LaborHours" column
(flexDA.Row, 3)is the "PartPrice" column

I had them above but called it TextMatrix.column instead. Sorry. I'll try the Debug.Print and see what happens. flexDA.row is what I'm calling the current row since that will be the only row I want the total of. Then I'll add that total to another column(Total)in the FlexGrid. I have tried to total the PartPrice column and put it in a Text Box(just to try) and it works fine so I have the code for that once I get this to work correctly. Am I correct in calling the current row and column by using(flexDA.Row, 2), or do I need to (Dim i As Integer)to refer to the column and row as (i, 2). I didn't Dim i and use it for the row because I just want the total of the current row and don't need count capabilities.

Thanks for taking time to answer this post.
Every day above ground is a GOOD DAY!!!

 

[MichaelRed]

Referencing the current row should be O.K, I just do not see where the Row gets selected. to do what I think you are attempting, you need to increment through the rows.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rtshort]

The rows are added to the FlexGrid from clicking on a label on another form through a SQL Statement from a SQL 7.0 Database called Mercury.
'// this is on a form (frmMerc)
Private Sub lblPlug_DblClick()
sql = "Select * from Mercury where PartName = 'Spark Plug'"
Call AddNewItem
End Sub
'// PartName is not the primary key but nothing in the column is duplicated so the SQL Statement will work

'// this is also on the same form
Private Sub AddNewItem()
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.EOF = True And rs.BOF = True Then
rs.MoveFirst
End If
Item = rs.Fields("Part#&quot.Value & vbTab & rs.Fields("PartName&quot.Value & vbTab & rs.Fields("Labor&quot.Value & vbTab & rs.Fields("PartPrice&quot
frmDA.flexDA.AddItem Item
Call frmDA.GetTotal
rs.Close
End Sub'// this loads the MSHFlexGrid(frmDA)
Private Sub Form_Load()
With flexDA
.TextMatrix(0, 0) = "Part Number"
.TextMatrix(0, 1) = "Part Name"
.TextMatrix(0, 2) = "Hours"
.TextMatrix(0, 3) = "Part Price"
.TextMatrix(0, 4) = "Total"
.ColWidth(0) = 1200
.ColWidth(1) = 2500
.ColWidth(2) = 750
.ColWidth(3) = 1000
.ColWidth(4) = 1000
End With
End Sub

I don't want a running total of all of the entries, I just want the "Hours Total"(LaborRate*Labor)+ the "PartsPrice" to be put in the "TotalColumn" for each line. On the original post I'm calling Labor the "Hours" column. I appreciate you time answering this post. Every day above ground is a GOOD DAY!!!

 

[MichaelRed]

This:

If rs.EOF = True And rs.BOF = True Then
rs.MoveFirst
End If


doesn't accomplish anything. If BOF and EOF are true, the recordset is empty.

This:
If
Item = rs.Fields("Part#&quot.Value & vbTab & rs.Fields("PartName&quot.Value & vbTab & rs.Fields("Labor&quot.Value & vbTab & rs.Fields("PartPrice&quot
frmDA.flexDA.AddItem Item

actually loads/adds AN item (Singular) to the grid

This:

Call frmDA.GetTotal

Attempts to do your calc, but IT DOES NOT have the info on WHICH ROW to calculate. You need to Give this routine the 'current' row of the grid. I think that this can be frmDA.flexDA.Rows - 1 (Rows are numbered form Zero, but counted from one), so change the call to:


Call frmDA.GetTotal(frmDA.flexDA.Rows - 1)


Then Change the frmDA subroutine to:

Public Sub GetTotal(RowNum)
Dim Labor As Integer
Dim Total As Currency
Dim LaborRate As Currency
LaborRate = 60#
Labor = LaborRate * Val(flexDA.TextMatrix(RowNum, 2))
Total = Labor + Val(flexDA.TextMatrix(RowNum, 3))
txtLaborTotal.Text = Total

'Try this to see what's What
Debug.Print flexDA.Row, flexDA.TextMatrix(RowNum, 2)), flexDA.TextMAtrix(RowNum, 3)), Labor, Total

Debug.Print 'Place a break point here, LOok at what ever values you want
End Sub



This:

'// this loads the MSHFlexGrid(frmDA)
Private Sub Form_Load()
With flexDA
.TextMatrix(0, 0) = "Part Number"
.TextMatrix(0, 1) = "Part Name"
.TextMatrix(0, 2) = "Hours"
.TextMatrix(0, 3) = "Part Price"
.TextMatrix(0, 4) = "Total"
.ColWidth(0) = 1200
.ColWidth(1) = 2500
.ColWidth(2) = 750
.ColWidth(3) = 1000
.ColWidth(4) = 1000
End With
End Sub

only loads the header row, NOT the entire grid

If the grid has one fixed row, the header will be O.K., Otherwise, it will depend on some other details as to wheather the process will overwrite the first record.

Please review this and see if you understand the changes made. If this makes sense to you, try it out. ESPECIALLY the part about the debug.print. Let me know what results you get.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

This:

If rs.EOF = True And rs.BOF = True Then
rs.MoveFirst
End If


doesn't accomplish anything. If BOF and EOF are true, the recordset is empty.

This:
If
Item = rs.Fields("Part#&quot.Value & vbTab & rs.Fields("PartName&quot.Value & vbTab & rs.Fields("Labor&quot.Value & vbTab & rs.Fields("PartPrice&quot
frmDA.flexDA.AddItem Item

actually loads/adds AN item (Singular) to the grid

This:

Call frmDA.GetTotal

Attempts to do your calc, but IT DOES NOT have the info on WHICH ROW to calculate. You need to Give this routine the 'current' row of the grid. I think that this can be frmDA.flexDA.Rows - 1 (Rows are numbered form Zero, but counted from one), so change the call to:


Call frmDA.GetTotal(frmDA.flexDA.Rows - 1)


Then Change the frmDA subroutine to:

Public Sub GetTotal(RowNum)
Dim Labor As Integer
Dim Total As Currency
Dim LaborRate As Currency
LaborRate = 60#
Labor = LaborRate * Val(flexDA.TextMatrix(RowNum, 2))
Total = Labor + Val(flexDA.TextMatrix(RowNum, 3))
txtLaborTotal.Text = Total

'Try this to see what's What
Debug.Print flexDA.Row, flexDA.TextMatrix(RowNum, 2)), flexDA.TextMAtrix(RowNum, 3)), Labor, Total

Debug.Print 'Place a break point here, LOok at what ever values you want
End Sub



This:

'// this loads the MSHFlexGrid(frmDA)
Private Sub Form_Load()
With flexDA
.TextMatrix(0, 0) = "Part Number"
.TextMatrix(0, 1) = "Part Name"
.TextMatrix(0, 2) = "Hours"
.TextMatrix(0, 3) = "Part Price"
.TextMatrix(0, 4) = "Total"
.ColWidth(0) = 1200
.ColWidth(1) = 2500
.ColWidth(2) = 750
.ColWidth(3) = 1000
.ColWidth(4) = 1000
End With
End Sub

only loads the header row, NOT the entire grid

If the grid has one fixed row, the header will be O.K., Otherwise, it will depend on some other details as to wheather the process will overwrite the first record.

Please review this and see if you understand the changes made. If this makes sense to you, try it out. ESPECIALLY the part about the debug.print. Let me know what results you get.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[chiph]

RtShort -

To check for an empty recordset you might be tempted to look at the RecordCount property. Don't do it. What happens is in result sets with many rows, ADO caches them, going back to the database for more once you're getting close to reaching the end of what it's fetched. So the RecordCount shows 0 rows until ADO has truly reached the end of the results.

The correct way to do this (as Michael said) is to use the BOF and EOF properties. To do this, use code like:
[tt]
If Not (adoRS.BOF Or adoRS.EOF) Then
[tab]' I've got rows
End If
[/tt]

By applying DeMorgan's laws, you could also write it as:
[tt]
If (Not adoRS.BOF) And (Not adoRS.EOF) Then
[tab]' I've got rows
End If
[/tt]

The two are logically equivalent.

Hope this helps some.

Chip H.

 

[rtshort]

Took the weekend off for a change. I'll try it and post when I find out. Thanks guys. Every day above ground is a GOOD DAY!!!

 

[audioplayer65]

I would suggest using long or even varaint when evaluating a currency value adding it to something else, unless you really know the numeric range of the result.

Labor = LaborRate * Val(flexDA.TextMatrix(flexDA.Row, 2))
Labor should by Dim'ed as something that can handle the range of numbers that could result.

 

[robinmiller]

Why not create some properties to hold the values,
each time an item is added, intercept the values and increment the property values. The call a routine to carry out the calculations.

ie..

Option Explicit
' member variable for LaborRate property
Private m_LaborRate As Currency
' member variable for JobTotal property
Private m_JobTotal As Currency
' member variable for LaborHours property
Private m_LaborHours As Double
' member variable for PartPrice property
Private m_PartPrice As Currency

'---------------------------------------------
' Property LaborRate (Private)
'
' Created by robinm ()
' Date: 05-03-2001 Time: 13:16
'
'
'---------------------------------------------
Private Property Get LaborRate() As Currency
LaborRate = m_LaborRate
End Property

Private Property Let LaborRate(ByVal newValue As Currency)
m_LaborRate = newValue
End Property
'---------------------------------------------
' Property JobTotal (Private)
'
' Created by robinm ()
' Date: 05-03-2001 Time: 13:16
'
'
'---------------------------------------------
Private Property Get JobTotal() As Currency
JobTotal = m_JobTotal
End Property

Private Property Let JobTotal(ByVal newValue As Currency)
m_JobTotal = newValue
End Property
'---------------------------------------------
' Property LaborHours (Private)
'
' Created by robinm ()
' Date: 05-03-2001 Time: 13:17
'
'
'---------------------------------------------
Private Property Get LaborHours() As Double
LaborHours = m_LaborHours
End Property

Private Property Let LaborHours(ByVal newValue As Double)
m_LaborHours = newValue
End Property
'---------------------------------------------
' Property PartPrice (Private)
'
' Created by robinm ()
' Date: 05-03-2001 Time: 13:38
'
'
'---------------------------------------------
Private Property Get PartPrice() As Currency
PartPrice = m_PartPrice
End Property

Private Property Let PartPrice(ByVal newValue As Currency)
m_PartPrice = newValue
End Property

Private Sub cmdDoIt_Click()
Dim item As String
Dim Labor As Integer
Dim Price As Currency
Dim LaborPrice As Currency
'-------------------------------------------------
'this section purely creates random values that you would get from the database

Randomize

Labor = Int((6 * Rnd) + 1)
Price = CCur(Int((10 * Rnd) + 1))
LaborPrice = CCur(Int((8 * Rnd) + 1))
'-------------------------------------------------

'-------------------------------------------------
'Assign the values to the Properties defined above
PartPrice = Price
LaborHours = Labor
LaborRate = LaborPrice

'Create the Job/line total property
JobTotal = CCur(PartPrice + (LaborHours * LaborPrice))

'Create the item line including the generated total
item = "PART NO" & vbTab & "PARTNAME" & vbTab & LaborRate & vbTab & LaborHours & _
vbTab & Price & vbTab & JobTotal

'add it. Workd for me.
msgLabor.AddItem item

End Sub

 

[rtshort]

Audioplayer65, the labor hours will be something like (1.2 or 5.8). What would type variable would you suggest using. Variant, Single or what?? I'll try variant in place of currency but the numeric range of the result should be a dollar & cents amount. Like (1.2 * 40.00)Is this what you were asking?? Would you still use variant instead of currency.

Robinmiller, I'm kinda new at this. I don't really know what the Let method does. I've got 2 books I've been reading from the MS Press. They don't even mention the Let method. Would ya mind kinda explaining it.

Thanks to both of ya for taking time to answer. Every day above ground is a GOOD DAY!!!

 

[robinmiller]

Visual Basic provides three kinds of property procedures, as described below.

Property Get Returns the value of a property.
Property Let Sets the value of a property.
Property Set Sets the value of an object property (that is, a property that contains a reference to an object).
(IGNORE SET FOR NOW)

In this case, think of the properties as 'holders' of your information.

You dont have to do anything else with them except

Assign data to them by :

LaborRate = 15 (This calls the LET PROPERTY)
txtBox.Text = LaborRate (This calls the
GET PROPERTY)

The properties will hold the information until you reset them, or they go out of scope (You close the form/application or in the case of an object you set it to nothing)


If you go to a code window in VB , then on the toolbar
select TOOLS , ADD PROCEDURE.
You will get a VB window pop up with various options on it.
Type a Name in the text BOX, in this case type TEST.
Check the Property checkbox, also check the Private checkbox.
Then Click OK.

You should get the following now in the code window

Private Property Get test() As Variant

End Property

Private Property Let test(ByVal vNewValue As Variant)

End Property

You have created a Property called test.

You can then manipulate the values within the properties.

for example you could format the data.

Private Property Let test(ByVal vNewValue As Variant)
vNewValue = format(vNewValue,"DD-MMM-YYYY&quot
End Property

(obviously this would for an expected Date Value)


In your case, each time you are creating a line for the Bill
Before you insert a line to the grid, you should calculate the values.
So, if you dont want to use properties, you could use variables.
ie

Dim JobTotal as currency
Dim LineTotal as currency
Dim LaborRate as currency
Dim LaborHours as Double
Dim PartPrice as currency


Each time, after you have fired the SQL to retrieve the data from the database and return it via a recordset, caclulate the figures, BEFORE... you load the grid.

So in pseudo code (Plain English)

PartPrice = Recordset.PRICEFIELD
LaborRate = WHEREVER IT COMES FROM
LaborHours = WHEREVER IT COMES FROM

LineTotal = PartPrice + (LaborRate * LaborHours)
'Running total of all JobLines
JobTotal = JobTotal + LineTotal

Build the string to add to the flexigrid

item = "PART NO" & vbTab & "PARTNAME" & vbTab &
LaborRate & vbTab & LaborHours & _
vbTab & Price & vbTab & JobTotal

FLEXIGRID.ADDITEM STRING

Then simply set the variable to 0 after each Line add
except the JobTotal (Obviously as this holds the
running total)

partprice = 0 (etc.)


Any Questions feel free to mail me.

You should really start to look at Objects.

 

[rtshort]

Thanks Robin. That puts it more in perspective than all of the books I've been through from MS Press. Is there a good book that you would recommend for understanding Objects clearly.

I don't know your e-mail addy. Lucky you.
Every day above ground is a GOOD DAY!!!

 

[robinmiller]

No problems.

Robin.Miller@Telewest.co.uk

There a a number of books out there on objects. Some are good others are very hard to read clearly and give you a unclutered understanding of Objects

In my opinion , the best start is probably two books.

1st Beginning Visual Basic 6 Objects by Peter Wright
(WROX PRESS.

http://WWW.WROX.COM)

Very good book

2nd Dan Appleman's Developing ActiveX Components with Visual Basic 6 by Dan Appleman
(Abit more advanced but he explains things in a very easy to understand way.)

With the advent of VB7 (.net) the construct of the VB language will change. If you can program Objects, and think Objects as opposed to the usual VB modular programming then you will have a 'jump' start on the rest of the VB crowd.

Any questions feel free to mail me.