8 Replies - 5598 Views - Last Post: 31 March 2009 - 01:13 AM Rate Topic: -----

#1 stj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 01-March 09

Calculating a total a in a datagrid and pass final total to a text box

Post icon  Posted 01 March 2009 - 06:13 AM

Dim WithEvents adoPurchasesRS As Recordset
Dim mbChangedByCode As Boolean
Dim mvBookMark As Variant
Dim mbEditFlag As Boolean
Dim mbAddNewFlag As Boolean
Dim mbDataChanged As Boolean

Dim strSQL As String
Dim lngOrderID As Long
Dim intShipVia As Integer
Dim dblSubTotal As Double
Dim dblTotal As Double
Dim dblFreight As Double

Dim db_file As String 'Name of DataBase
Dim SQLstmt As String 'SQL Statement String(s)
Dim db As ADODB.Connection 'Connect to the Main ADO Data Type - Alternative
Dim cn As ADODB.Connection 'Connect to the Main ADO Data Type
Dim cn1 As ADODB.Connection 'Connect to secondary ADO Data Type
Dim rs1 As ADODB.Recordset 'Primary Record Source Name
Dim rs2 As ADODB.Recordset 'Aggregate Record Source Name
Dim rs3 As ADODB.Recordset 'Record Source to allow updates
Dim RcCount As Integer 'Record Counter
Dim retval As Integer 'Variable used for Messages Boxes
Dim strCnn As String 'String to connect cn1
Dim strItem As String 'String to represent Item Field/Record Addition
Dim strCost As String 'String to represent Cost Field/Record Addition
Dim strQuantity As String 'String to represent Quantity Field/Record Addition
Dim strJobNoID As String 'String to represent JobNoID Field/Record Addition
Dim strCustomerID As String 'String to represent CustomerID Field/Record Addition
Dim booRecordAdded As Boolean 'Record added test/check
Dim ij As Integer 'index for Text Box Array
' property variable for Company ID
Private mlngID As Long


'---------------------------------------------------------------------------
' Public Property ID
'
' Purpose: Unique ID of Company to show on the form (0 means show all)
'
' Assumptions: None
'
' Effects: None
'
' Inputs:
' ID: value to set ID property to
'
'---------------------------------------------------------------------------
Public Property Let ID(ByVal ID As Long)
mlngID = ID
End Property

Private Sub cmdSave_Click()

End Sub



Private Sub cmdFind_Click()
Dim BM As Variant
If txtFind.Text = "" Then Exit Sub
BM = adoPurchasesRS.Bookmark
adoPurchasesRS.MoveFirst
adoPurchasesRS.Find "JobNoID LIKE '" & txtFind.Text & "*'", 0, adSearchForward
If adoPurchasesRS.EOF Then
adoPurchasesRS.Bookmark = BM
End If
End Sub

Private Sub Command1_Click()
Dim retval
retval = Shell("C:\WINDOWS\system32\calc.exe", 1)
End Sub

Private Sub Form_Load()
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient

'Database Connection using DSN
db.Open "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;dsn=Columbia;uid=;pwd=;"
' db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Columbia Final Testing Phase 1\Columbia.mdb;"

Set adoPurchasesRS = New Recordset
adoPurchasesRS.Open "SHAPE {select [Customer ID],Customername,JobNoID,ChargedTotalCost,Tax,FinalTotal,TotalPurchased from Current_Requests WHERE [Customer ID] = " & mlngID & " Order By JobNoID} AS ParentCMD APPEND ({select JobNoID,Item,Quantity,Cost,Totals from [query1] } AS ChildCMD RELATE JobNoID TO JobNoID) AS ChildCMD", db, adOpenStatic, adLockOptimistic
'

'adoPurchasesRS.Open "SHAPE {select [Customer ID],Customername,JobNoID,ChargedTotalCost,Tax,FinalTotal,TotalPurchased from Current_Requests Order by JobNoID} AS ParentCMD APPEND ({select * from [query1] Order by JobNoID } AS ChildCMD RELATE JobNoID TO JobNoID) AS ChildCMD", db, adOpenStatic, adLockOptimistic


' adoPurchasesRS.Open "SHAPE select [Customer ID],Customername,JobNoID,ChargedTotalCost,Tax,FinalTotal,TotalPurchased from Current_Requests WHERE [JobNoID] = " & mlngID, AS ParentCMD APPEND ({select CustomerID,JobNoID,Item,Quantity,Cost,Totals from [query1] } AS ChildCMD RELATE JobNoID TO JobNoID) AS ChildCMD", db, adOpenStatic, adLockOptimistic



' adoPurchasesRS.Open "SHAPE {select [Customer ID],Customername,JobNoID from Current_Requests WHERE [Customer ID] = " & mlngID & " Order By JobNoID} AS ParentCMD APPEND ({SELECT CustomerID,JobNoID,Item,Cost,Quantity,[Cost]* [Quantity] AS Total FROM query1 "


Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtfields
Set oText.DataSource = adoPurchasesRS
Next

Set DataGrid1.DataSource = adoPurchasesRS("ChildCMD").UnderlyingValue


mbDataChanged = False


' '---------------------------------------------------------------------------------------
' ' Get an agregate recordset to calculate the totals
' SQLstmt = "SELECT sum(Quantity) as SumOfQuantities, sum(Totals) "
' SQLstmt = SQLstmt + " as SumOfTotal FROM query1 "
' Set rs2 = New ADODB.Recordset
' rs2.Open Query1, cn, adOpenStatic, adLockOptimistic, adCmdText
'---------------------------------------------------------------------------------------
End Sub

Private Sub Update_Controls()
'In order to update the Total Quantities and Total Sales
adoPurchasesRS.Requery
rs2.Requery

txtTotalQty.Text = rs2.Fields("sumOfQuantities")
txtTotalSales.Text = Format(rs2.Fields("SumOfTotal"), "#,##0.00")

End Sub
Private Sub setTotalBoxes()
'set the location for the Total Quantity and Total Sales TextBoxes
txtTotalQty.Top = DataGrid1.Top + DataGrid1.Height + 200
txtTotalQty.Left = lblTotalQty.Left + 1300
txtTotalQty.Width = DataGrid1.Columns(2).Width

lblTotalQty.Top = txtTotalQty.Top
lblTotalQty.Left = 150
lblTotalQty = "Total Quantity:"


txtTotalSales.Top = DataGrid1.Top + DataGrid1.Height + 200
txtTotalSales.Left = DataGrid1.Columns(3).Left
txtTotalSales.Width = DataGrid1.Columns(3).Width

lblTotalSales.Top = txtTotalSales.Top
lblTotalSales.Left = 3400
lblTotalSales = "Total Sales:"

End Sub
Private Sub formatDataGrid1()
With DataGrid1
.Columns(0).Width = 1100 'Set for maximum anticipated width
.Columns(1).Width = 1000 'Set for maximum anticipated width
.Columns(2).Width = 5000 'Set for maximum anticipated width
.Columns(3).NumberFormat = "Currency"
.Columns(5).NumberFormat = "Currency"
.HoldFields
End With
End Sub





'Private Sub Form_Resize()
' On Error Resume Next
' 'This will resize the grid when the form is resized
' grdDataGrid.Width = Me.ScaleWidth
' grdDataGrid.Height = Me.ScaleHeight - grdDataGrid.Top - 30 - picButtons.Height - picStatBox.Height
' lblStatus.Width = Me.Width - 1500
' cmdNext.Left = lblStatus.Width + 700
' cmdLast.Left = cmdNext.Left + 340
'End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If mbEditFlag Or mbAddNewFlag Then Exit Sub

Select Case KeyCode
Case vbKeyEscape
cmdClose_Click
Case vbKeyEnd
cmdLast_Click
Case vbKeyHome
cmdFirst_Click
Case vbKeyUp, vbKeyPageUp
If Shift = vbCtrlMask Then
cmdFirst_Click
Else
cmdPrevious_Click
End If
Case vbKeyDown, vbKeyPageDown
If Shift = vbCtrlMask Then
cmdLast_Click
Else
cmdNext_Click
End If
End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
Screen.MousePointer = vbDefault
Set frmProductsPurchased = Nothing
test24.Show
Unload Me
End Sub

Private Sub adoPurchasesRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record position for this recordset
' lblStatus.Caption = "Record: " & CStr(adoPurchasesRS.AbsolutePosition)
End Sub

Private Sub adoPurchasesRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This is where you put validation code
'This event gets called when the following actions occur
Dim bCancel As Boolean

Select Case adReason
Case adRsnAddNew
Case adRsnClose
Case adRsnDelete
Case adRsnFirstChange
Case adRsnMove
Case adRsnRequery
Case adRsnResynch
Case adRsnUndoAddNew
Case adRsnUndoDelete
Case adRsnUndoUpdate
Case adRsnUpdate
End Select

If bCancel Then adStatus = adStatusCancel
End Sub

'Private Sub cmdAdd_Click()
' On Error GoTo AddErr
' With adoPurchasesRS
' If Not (.BOF And .EOF) Then
' mvBookMark = .Bookmark
' End If
' .AddNew
'' lblStatus.Caption = "Add record"
' mbAddNewFlag = True
' SetButtons False
' End With
'
' Exit Sub
'AddErr:
' MsgBox Err.Description
'End Sub

'Private Sub cmdDelete_Click()
' On Error GoTo DeleteErr
' With adoPurchasesRS
' .Delete
' .MoveNext
' If .EOF Then .MoveLast
' End With
' Exit Sub
'DeleteErr:
' MsgBox Err.Description
'End Sub
'
'Private Sub cmdRefresh_Click()
' 'This is only needed for multi user apps
' On Error GoTo RefreshErr
' Set DataGrid1.DataSource = Nothing
' adoPurchasesRS.Requery
' Set DataGrid1.DataSource = adoPurchasesRS("ChildCMD").UnderlyingValue
' Exit Sub
'RefreshErr:
' MsgBox Err.Description
'End Sub

Private Sub cmdEdit_Click()
On Error GoTo EditErr

' lblStatus.Caption = "Edit record"
SetState "Edit"
mbEditFlag = True
SetButtons False
Exit Sub

EditErr:
MsgBox Err.Description
End Sub
Private Sub cmdCancel_Click()
On Error Resume Next

SetButtons True
mbEditFlag = False
mbAddNewFlag = False
adoPurchasesRS.CancelUpdate
If mvBookMark > 0 Then
adoPurchasesRS.Bookmark = mvBookMark
Else
adoPurchasesRS.MoveFirst
End If
mbDataChanged = False
SetState "View"

End Sub

Private Sub cmdUpdate_Click()
On Error GoTo UpdateErr

adoPurchasesRS.UpdateBatch adAffectAll

If mbAddNewFlag Then
adoPurchasesRS.MoveLast 'move to the new record
End If

mbEditFlag = False
mbAddNewFlag = False
SetButtons True
MsgBox "Purchase Record Saved", vbOKOnly + vbInformation, "Purchase Record Save "
mbDataChanged = False
SetState "View"
Exit Sub
UpdateErr:
MsgBox Err.Description
End Sub

Private Sub cmdClose_Click()
Unload Me
test24.Show

End Sub

Private Sub cmdFirst_Click()
On Error GoTo GoFirstError

adoPurchasesRS.MoveFirst
mbDataChanged = False

Exit Sub

GoFirstError:
MsgBox Err.Description
End Sub

Private Sub cmdLast_Click()
On Error GoTo GoLastError

adoPurchasesRS.MoveLast
mbDataChanged = False

Exit Sub

GoLastError:
MsgBox Err.Description
End Sub

Private Sub cmdNext_Click()
On Error GoTo GoNextError

If Not adoPurchasesRS.EOF Then adoPurchasesRS.MoveNext
If adoPurchasesRS.EOF And adoPurchasesRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPurchasesRS.MoveLast
End If
'show the current record
mbDataChanged = False

Exit Sub
GoNextError:
MsgBox Err.Description
End Sub

Private Sub cmdPrevious_Click()
On Error GoTo GoPrevError

If Not adoPurchasesRS.BOF Then adoPurchasesRS.MovePrevious
If adoPurchasesRS.BOF And adoPurchasesRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPurchasesRS.MoveFirst
End If
'show the current record
mbDataChanged = False

Exit Sub

GoPrevError:
MsgBox Err.Description
End Sub

Private Sub SetButtons(bVal As Boolean)
' cmdAdd.Visible = bVal
cmdEdit.Visible = bVal
cmdUpdate.Visible = Not bVal
cmdCancel.Visible = Not bVal
' cmdDelete.Visible = bVal
cmdClose.Visible = bVal
' cmdRefresh.Visible = bVal
cmdNext.Enabled = bVal
cmdFirst.Enabled = bVal
cmdLast.Enabled = bVal
cmdPrevious.Enabled = bVal
End Sub
Private Sub SetState(AppState As String)
Dim I As Integer
MyState = AppState
Select Case AppState

Case "View"





' Data fields = Locked

txtfields(0).Locked = True
txtfields(3).Locked = True
txtfields(2).Locked = True
txtfields(1).Locked = True
txtfields(4).Locked = True
txtfields(5).Locked = True
txtfields(6).Locked = True

DataGrid1.Enabled = False

' Data fields BackColor in View Mode

txtfields(0).BackColor = &HFF&
txtfields(3).BackColor = &HFF0000
txtfields(2).BackColor = &H0&
txtfields(1).BackColor = &H800000
txtfields(4).BackColor = &H0&
txtfields(5).BackColor = &H800000
txtfields(6).BackColor = &H800000

' Data fields ForeColor in View Mode

txtfields(0).ForeColor = &HFFFFFF
txtfields(3).ForeColor = &HC0FFFF
txtfields(2).ForeColor = &HFF00&
txtfields(1).ForeColor = &HC0FFFF
txtfields(4).ForeColor = &HFFFF00
txtfields(5).ForeColor = &HC0FFFF
txtfields(6).ForeColor = &HC0FFFF

DataGrid1.BackColor = &HC0FFFF
DataGrid1.ForeColor = &H800000


'
For I = 0 To 6



Next I

' cmdFirst.Enabled = True
' cmdPrevious.Enabled = True
' cmdNext.Enabled = True
' cmdLast.Enabled = True
'' cmdAddNew.Enabled = True
' cmdUpDate.Enabled = False
' cmdCancel.Enabled = False
' cmdEdit.Enabled = True
'' cmdDelete.Enabled = True
' cmdRefresh.Enabled = True
' cmdclose.Enabled = True
' cmdRefresh.SetFocus
' TxtAddress(9).SetFocus




Case "Edit"


'TxtInput Background Colour - txt box background colour changes to Red
'TxtInput ForeGround Colour - txt box ForeGround colour changes to White
'TxtInput ForeGround Colourand Baclground revert to Viewing when user clicks save.

For I = 1 To 6




' Data fields = UnLocked

txtfields(0).Locked = True
txtfields(3).Locked = True
txtfields(2).Locked = True
txtfields(1).Locked = False
txtfields(4).Locked = False
txtfields(5).Locked = False
txtfields(6).Locked = False



' Data fields BackColor in Edit Mode



txtfields(1).BackColor = &HFF&
txtfields(4).BackColor = &HFF&
txtfields(5).BackColor = &HFF&
txtfields(6).BackColor = &HFF&

' Data fields ForeColor in Edit Mode


txtfields(1).ForeColor = &H80FFFF
txtfields(4).ForeColor = &H80FFFF
txtfields(5).ForeColor = &H80FFFF
txtfields(6).ForeColor = &H80FFFF

Next I

' cmdFirst.Enabled = False
' cmdPrevious.Enabled = False
' cmdNext.Enabled = False
' cmdLast.Enabled = False
'' cmdAddNew.Enabled = False
' cmdUpDate.Enabled = True
' cmdCancel.Enabled = True
' cmdEdit.Enabled = False
'' cmdDelete.Enabled = False
' cmdRefresh.Enabled = False
' cmdclose.Enabled = False
'' txtInput(1).SetFocus

End Select
End Sub

Private Sub txtFields_Change(Index As Integer)
txtfields(4).Text = (Val(txtfields(5).Text) + Val(txtfields(6).Text)) + Val(txtfields(1).Text)
End Sub

Is This A Good Question/Topic? 0
  • +

Replies To: Calculating a total a in a datagrid and pass final total to a text box

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 02 March 2009 - 06:57 PM

actually wnat do you want to do with datagrid?

pls explain your problem briefly and explain?
where did you stuck give the details?

and one more thing,
try to format your code properly this will improve the readability of your code
Was This Post Helpful? 0
  • +
  • -

#3 stj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 01-March 09

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 03 March 2009 - 04:29 AM

View Postthava, on 2 Mar, 2009 - 05:57 PM, said:

actually wnat do you want to do with datagrid?

pls explain your problem briefly and explain?
where did you stuck give the details?

and one more thing,
try to format your code properly this will improve the readability of your code

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#4 stj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 01-March 09

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 03 March 2009 - 04:36 AM

First of all hank you so much foryuo reply most appeciated. I m relly a rel beginner, much as very new novice, so please be patient with me, and I really do not know what you mean when you say please formatyour code, as I got some of the code from another source named ado totals, butcant get it to work. All I want to do is calculate the totaol cost in the column totals and place the total ina text box, then calcuate the labor charge and the tax and then it automatically gives mea total cost,'
I would really appreciate yor assistance please sir,
regards
steve jones my e mail address is sjones8528@hotmail.com thanks again steve
Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 04 March 2009 - 07:13 PM

ok Here is a little guidence

use the datagrid's Keydown Event
check for the current col
you can retrive the current col position from the datagrid's col property

now
datagrid1.columns(0) will return the datagrid's first cell of the current row

using this thing you should calculate the total and update

like this
if datagrid1.col =2 or datagrid2.col =3 then
datagrid1.columns(4) =val(datagrid1.columns(2) )*val(datagrid1.columns(2) )
end if


you should update the Amount fild

now let us consider i connect the datagrid with my saltemp table
now i want to found the total amount of the sales

I should use the datagrid's Event Afterupdate

use a seperate recordset to calculate the total of the Amount field
use a select Query to get the sum of the recordset

AND SHOW THIS IN A SEPERATE TEXTBOX
Was This Post Helpful? 0
  • +
  • -

#6 stj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 01-March 09

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 05 March 2009 - 01:44 AM

Hi Thava, I have done what you have explained even though I have not understood properly, sorry, please advise me more as i m totally lost Please sir, I have enclosed the new code on a new generated form in VB I will attach a scrren shot also





[Dim WithEvents adoPrimaryRS As Recordset
Dim rs As New Connection
Dim rs1 As New Recordset

Dim mbChangedByCode As Boolean
Dim mvBookMark As Variant
Dim mbEditFlag As Boolean
Dim mbAddNewFlag As Boolean
Dim mbDataChanged As Boolean

Private Sub Form_Load()
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Columbia 14 Deployment\Columbia DataBase\Columbia.mdb;Jet OLEDB:Database Password=ALBATROSS;"

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "SHAPE {select * from Current_Requests Order by JobNoID} AS ParentCMD APPEND ({select * from Query1 Order by JobNoID } AS ChildCMD RELATE JobNoID TO JobNoID) AS ChildCMD", db, adOpenStatic, adLockOptimistic

Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtFields
Set oText.DataSource = adoPrimaryRS
Next

Set grdDataGrid.DataSource = adoPrimaryRS("ChildCMD").UnderlyingValue

mbDataChanged = False
End Sub

Private Sub Form_Resize()
On Error Resume Next
'This will resize the grid when the form is resized
grdDataGrid.width = Me.ScaleWidth
grdDataGrid.height = Me.ScaleHeight - grdDataGrid.Top - 30 - picButtons.height - picStatBox.height
lblStatus.width = Me.width - 1500
cmdNext.Left = lblStatus.width + 700
cmdLast.Left = cmdNext.Left + 340
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If mbEditFlag Or mbAddNewFlag Then Exit Sub

Select Case KeyCode
Case vbKeyEscape
cmdClose_Click
Case vbKeyEnd
cmdLast_Click
Case vbKeyHome
cmdFirst_Click
Case vbKeyUp, vbKeyPageUp
If Shift = vbCtrlMask Then
cmdFirst_Click
Else
cmdPrevious_Click
End If
Case vbKeyDown, vbKeyPageDown
If Shift = vbCtrlMask Then
cmdLast_Click
Else
cmdNext_Click
End If
End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
Screen.MousePointer = vbDefault
End Sub
Private Sub DataGrid1_AfterUpdate()
' Get an agregate recordset to calculate the totals
SQLstmt = "SELECT sum(Totals) as TotalPurchased FROM query1 "
Set rs1 = New ADODB.Recordset
rs1.Open SQLstmt, cn, adOpenStatic, adLockOptimistic, adCmdText
'----------------------------------------------------------------------

rs1.Requery



txtFields(5).Text = Format(rs1.Fields("TotalPurchased"), "#,##0.00")



End Sub
Private Sub adoPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record position for this recordset
lblStatus.Caption = "Record: " & CStr(adoPrimaryRS.AbsolutePosition)
End Sub

Private Sub adoPrimaryRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This is where you put validation code
'This event gets called when the following actions occur
Dim bCancel As Boolean

Select Case adReason
Case adRsnAddNew
Case adRsnClose
Case adRsnDelete
Case adRsnFirstChange
Case adRsnMove
Case adRsnRequery
Case adRsnResynch
Case adRsnUndoAddNew
Case adRsnUndoDelete
Case adRsnUndoUpdate
Case adRsnUpdate
End Select

If bCancel Then adStatus = adStatusCancel
End Sub

Private Sub cmdAdd_Click()
On Error GoTo AddErr
With adoPrimaryRS
If Not (.BOF And .EOF) Then
mvBookMark = .Bookmark
End If
.AddNew
lblStatus.Caption = "Add record"
mbAddNewFlag = True
SetButtons False
End With

Exit Sub
AddErr:
MsgBox Err.Description
End Sub

Private Sub cmdDelete_Click()
On Error GoTo DeleteErr
With adoPrimaryRS
.Delete
.MoveNext
If .EOF Then .MoveLast
End With
Exit Sub
DeleteErr:
MsgBox Err.Description
End Sub

Private Sub cmdRefresh_Click()
'This is only needed for multi user apps
On Error GoTo RefreshErr
Set grdDataGrid.DataSource = Nothing
adoPrimaryRS.Requery
Set grdDataGrid.DataSource = adoPrimaryRS("ChildCMD").UnderlyingValue
Exit Sub
RefreshErr:
MsgBox Err.Description
End Sub

Private Sub cmdEdit_Click()
On Error GoTo EditErr

lblStatus.Caption = "Edit record"
mbEditFlag = True
SetButtons False
Exit Sub

EditErr:
MsgBox Err.Description
End Sub
Private Sub cmdCancel_Click()
On Error Resume Next

SetButtons True
mbEditFlag = False
mbAddNewFlag = False
adoPrimaryRS.CancelUpdate
If mvBookMark > 0 Then
adoPrimaryRS.Bookmark = mvBookMark
Else
adoPrimaryRS.MoveFirst
End If
mbDataChanged = False

End Sub

Private Sub cmdUpdate_Click()
On Error GoTo UpdateErr

adoPrimaryRS.UpdateBatch adAffectAll

If mbAddNewFlag Then
adoPrimaryRS.MoveLast 'move to the new record
End If

mbEditFlag = False
mbAddNewFlag = False
SetButtons True
mbDataChanged = False

Exit Sub
UpdateErr:
MsgBox Err.Description
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdFirst_Click()
On Error GoTo GoFirstError

adoPrimaryRS.MoveFirst
mbDataChanged = False

Exit Sub

GoFirstError:
MsgBox Err.Description
End Sub

Private Sub cmdLast_Click()
On Error GoTo GoLastError

adoPrimaryRS.MoveLast
mbDataChanged = False

Exit Sub

GoLastError:
MsgBox Err.Description
End Sub

Private Sub cmdNext_Click()
On Error GoTo GoNextError

If Not adoPrimaryRS.EOF Then adoPrimaryRS.MoveNext
If adoPrimaryRS.EOF And adoPrimaryRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPrimaryRS.MoveLast
End If
'show the current record
mbDataChanged = False

Exit Sub
GoNextError:
MsgBox Err.Description
End Sub

Private Sub cmdPrevious_Click()
On Error GoTo GoPrevError

If Not adoPrimaryRS.BOF Then adoPrimaryRS.MovePrevious
If adoPrimaryRS.BOF And adoPrimaryRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPrimaryRS.MoveFirst
End If
'show the current record
mbDataChanged = False

Exit Sub

GoPrevError:
MsgBox Err.Description
End Sub

Private Sub SetButtons(bVal As Boolean)
cmdAdd.Visible = bVal
cmdEdit.Visible = bVal
cmdUpdate.Visible = Not bVal
cmdCancel.Visible = Not bVal
cmdDelete.Visible = bVal
cmdClose.Visible = bVal
cmdRefresh.Visible = bVal
cmdNext.Enabled = bVal
cmdFirst.Enabled = bVal
cmdLast.Enabled = bVal
cmdPrevious.Enabled = bVal
End Sub

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#7 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 05 March 2009 - 06:11 PM

did you get the total or not?
and also you still not calculate the Totals Field in keydown as i mentioned
for i=0 to datagrid1.columns.count-1
    debug.print datagrid1.columns(i)
Next



and one more thing
when you use a datagrid it always update the underlying table Directly
so you need to mentioned the Jobnoid Agregate Query
Was This Post Helpful? 0
  • +
  • -

#8 stj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 01-March 09

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 05 March 2009 - 07:13 PM

Attached ImageHi Sir, This is Steve. I am very sorry to tell you that I donot know what you are talking about really. I am a retired person trying to learn more about Vb, and I offered to help a friend, however, I am aged 55, and all that you are writingto me still does not make any sence to me i am sorry for being so dumb, perhaps you wouild be kind enough to really help me out so thatI may understnd what you are saying, I am not really sure were to start, and am totally confused now with what you have just sent, abiout adding this and writing that.

Could I perhaps beg of you to help me in this one instance and write some code for me to make it work. PLEASE

In the datebase their are current_requests for a job order process. Customer comes in hands his pc(example) to be fixed and her is given a job Order No, I need to relate the customer to the job otder number, and if product is purchasd for him example a hard drive he needs to pay for it with a labor cost, a tax of 12% and show total cost to customer.

I was trying to show all items for that job order then calculate the cost price and then the labor, and then tax and then final cost to customer.

I am at a total loss. I would be will to pay a little for you help in writing this or some sort of contribution for your help.

PLEASE HELP

yours kindly steve jones. stj

thank you
Was This Post Helpful? 0
  • +
  • -

#9 rkhunter  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 03-March 09

Re: Calculating a total a in a datagrid and pass final total to a text box

Posted 31 March 2009 - 01:13 AM

Hi Stj,

Did you manage to solve your problem, can share it with me? ;-) I having same problem.

Thanks

rgds.
rkhunter

View Poststj, on 5 Mar, 2009 - 06:13 PM, said:

Attachment attachmentHi Sir, This is Steve. I am very sorry to tell you that I donot know what you are talking about really. I am a retired person trying to learn more about Vb, and I offered to help a friend, however, I am aged 55, and all that you are writingto me still does not make any sence to me i am sorry for being so dumb, perhaps you wouild be kind enough to really help me out so thatI may understnd what you are saying, I am not really sure were to start, and am totally confused now with what you have just sent, abiout adding this and writing that.

Could I perhaps beg of you to help me in this one instance and write some code for me to make it work. PLEASE

In the datebase their are current_requests for a job order process. Customer comes in hands his pc(example) to be fixed and her is given a job Order No, I need to relate the customer to the job otder number, and if product is purchasd for him example a hard drive he needs to pay for it with a labor cost, a tax of 12% and show total cost to customer.

I was trying to show all items for that job order then calculate the cost price and then the labor, and then tax and then final cost to customer.

I am at a total loss. I would be will to pay a little for you help in writing this or some sort of contribution for your help.

PLEASE HELP

yours kindly steve jones. stj

thank you

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1