?
Solved

gridupdate

Posted on 2003-02-22
10
Medium Priority
?
348 Views
Last Modified: 2012-05-04
Hello, I am new to the programming world, so I got some problems. I would like to find out how to enter data in a sales program using a grid which can be updated.
That is adding and deleting articles on the grid before doing the final calculation. I already have the information flowing on the grid but I cannot update.

0
Comment
Question by:maniacpfm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 2

Expert Comment

by:JoaTex
ID: 7999827
Hi

See this litle program

Create a DataBase named: Teste.mdb (put your data at c:\Teste.mdb)
3 Fields Indexed By Name
Name,Age,Country (All Text)
name the table as Table1.
enter some data on fields: Ex:
John,25,England
Charles,20,France
Rocky,27,USA

Open a Form1 in your project.
put 2 CommandButtons on it
command1 Named: New, Caption = UpDAte
command2 Named: Delete, Caption = Delete
1 data Control named: Data1
on properties set Databasename = c:\Teste.mdb
            Recordset = Table1
            Visible = False
1 grid control named: DataGrid1 (Microsoft Data Bound Grid Control 5.0)(SP3) In This Example
on project properties DBgrid1 set Data Mode = 0-Bound
                                  Data Source= DAta1
Right mouse click on dbgrid1 control on form1 and select Retrieve Fields.
Right mouse click on dbgrid1 control on form1 and select Properties.
 
Select General:
Caption: Teste - Table1
AllowAddNew,AllowDelete,AllowUpdate,ColumnHeaders,Enabled,MarqueeUnique - All Checkbox = on  
Accept Apply

Select: Columns
Column0(Name),Caption = Name,DataField = Name
Column1(Age),Caption = Name,DataField = Age
Column2(Country),Caption = Name,DataField = Country

Select Layout:
Column = Column0(NAme), Split = Split0, Alignement = 0-Left
Column = Column1(Age), Split = Split0, Alignement = 2 - Center, Apply
Column = Column2(Country), Split = Split0, Alignement = 0-Left

Select Splits:
Correct ScrollBars = 2 - Vertical, Apply
OK

Save your project and run the program. See Size grid. Exit Program. Adjust Grid.
Now, go to data1 Control Properties and at DatabaseName delete the directory.
Transfer the c:\Teste.mdb to your aplication directory.
In this way when you make the exe file or disrtribution of your program de database always shoul be at the same
directory of your exe file.

Put this code on Form1:

Code:

Option Explicit

Private Sub ComDel_Click()
   Data1.Recordset.Delete
   DBGrid1.Refresh
End Sub

Private Sub ComNew_Click()
   Data1.UpdateRecord
End Sub

Private Sub DBGrid1_AfterInsert()
   If DBGrid1.Row = 0 Then Data1.Recordset.AbsolutePosition = 0
End Sub

Private Sub Form_Load()
   Me.Top = (Screen.Height - Me.Height) / 2
   Me.Left = (Screen.Width - Me.Width) / 2
   Data1.DatabaseName = App.Path & "\Teste.mdb"
End Sub

Now you can see that updates and controls are direct from grid. you must try to make some improvements
such as some errors that are going to happen, as for instance duplicate files by same name index, make an error routine,
and try to make DELETE ALL or Search method.

Hope this suites you.
Sorry My English
Jo
0
 

Author Comment

by:maniacpfm
ID: 8015013
Hi Jo thanks for the reply, but i need to update only the grid because i want to used it for calculations before updating the database. I need to read and read to the grid only
0
 
LVL 2

Expert Comment

by:JoaTex
ID: 8015570
Hi
Well, I think that you do not have your grid linked to a database, and that is a very diferent Thing.There´s a lot of things that you can do in a grid. I Need more specifications of what you want to do, or send me the grid program to understand what you whant.I think that you want to make same calculation on your grid then make a Grid1.refresh and past to your dataBase, but fellow, you can make calculations between rows,coluns,totals,sub-totals and so on, what you mean by update the grid,is it to transfer the grid to your database?
Tell me about.
Jo
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:maniacpfm
ID: 8023777
Hi Jo, you are right on what i want to do:
This as the variables i am passing to my grid(Item, quantity, price, total1, total2,), but i want to be able to delete or modify a row (that is what i refer to as updating the grid), then sum the total1 + total2 columns and send to the database. It's a sales programme.
0
 

Author Comment

by:maniacpfm
ID: 8023866
I also want that when i change the quantity field, the total1 or total2 fields should be recalculated (just like in excel)
0
 
LVL 2

Expert Comment

by:JoaTex
ID: 8029853
Hi
Here it's a little example of what you want, There4s a few little bugs that you must work on if you want because there are not very important.

I use a database with 4 Fields:
1- Item
2- Quantity
3- price
4- Cost Price

The only field that can be Change it's the Quantity in this case. When Client Choose the quantity the price it4s automaticaly calculated by cost Price and totals are correct.
To Delete a Item just click on BackSpace.
I named my DataBase as Stock.mdb and should be put on same directory of your application.
The form of this project has:
1 Data Control
1 Command Button
1 FlexGrid
3 Labels
See Properties After Code.

Code:

Option Explicit
Dim N
Dim Total1
Dim Total2

Private Sub Command1_Click()
   For N = 1 To Data1.Recordset.RecordCount
      Data1.Recordset.AbsolutePosition = N - 1
      Data1.Recordset.Edit
      If Grid1.TextArray(N * 4 + 1) = "" Then Grid1.TextArray(N * 4 + 1) = 0
      If Grid1.TextArray(N * 4 + 2) = "" Then Grid1.TextArray(N * 4 + 2) = 0
      Data1.Recordset.Fields(1) = Grid1.TextArray(N * 4 + 1)
      Data1.Recordset.Fields(2) = Grid1.TextArray(N * 4 + 2)
      Data1.UpdateRecord
   Next N
   End
End Sub

Private Sub Form_Activate()
   Data1.Recordset.MoveLast
   Grid1.Rows = Data1.Recordset.RecordCount + 1
   For N = 1 To Data1.Recordset.RecordCount
      Data1.Recordset.AbsolutePosition = N - 1
      Grid1.Row = N
      Grid1.TextArray(N * 4) = Data1.Recordset.Fields(0)
      Grid1.TextArray(N * 4 + 1) = Data1.Recordset.Fields(1)
      If Grid1.TextArray(N * 4 + 1) = 0 Then Grid1.TextArray(N * 4 + 1) = ""
      Grid1.TextArray(N * 4 + 2) = Data1.Recordset.Fields(2)
      If Grid1.TextArray(N * 4 + 2) = 0 Then Grid1.TextArray(N * 4 + 2) = ""
      Grid1.TextArray(N * 4 + 3) = Data1.Recordset.Fields(3)
      Label2 = Val(Label2) + Val(Data1.Recordset.Fields(1))
      Label3 = Val(Label3) + Val(Data1.Recordset.Fields(2))
   Next N
End Sub

Private Sub Form_Load()
   Me.Top = (Screen.Height - Me.Height) / 2
   Me.Left = (Screen.Width - Me.Width) / 2
   Grid1.ColWidth(0) = 2000
   Grid1.ColWidth(1) = 1200
   Grid1.ColWidth(2) = 1200
   Grid1.Row = 0
   Grid1.TextArray(0) = "                Item"
   Grid1.TextArray(1) = "     Quantity"
   Grid1.TextArray(2) = "      Price"
   Grid1.TextArray(3) = "  Cost Price"
   Data1.DatabaseName = App.Path & "\stock.mdb"
   Data1.RecordSource = "Table1"
End Sub

Private Sub Form_Unload(Cancel As Integer)
   End
End Sub

Private Sub Grid1_KeyPress(KeyAscii As Integer)
   If Grid1.Col <> 1 Then Exit Sub
   If KeyAscii = 8 Then
      N = Grid1.Row
      Grid1.Text = ""
      Grid1.TextArray(N * 4 + 2) = ""
      Grid1_LeaveCell
      Exit Sub
   End If
   If KeyAscii < 48 Or KeyAscii > 57 Then Exit Sub
   Grid1.Text = Grid1.Text & Chr(KeyAscii)
End Sub

Private Sub Grid1_LeaveCell()
   If Grid1.Col <> 1 Then Exit Sub
   Total1 = 0
   Total2 = 0
   N = Grid1.Row
   Grid1.TextArray(N * 4 + 2) = Val(Grid1.Text) * Val(Grid1.TextArray(N * 4 + 3)) & " "
   If Val(Grid1.TextArray(N * 4 + 2)) = 0 Then Grid1.TextArray(N * 4 + 2) = ""
   For N = 1 To Grid1.Rows - 1
      Total1 = Total1 + Val(Grid1.TextArray(N * 4 + 1))
      Total2 = Total2 + Val(Grid1.TextArray(N * 4 + 2))
   Next N
   Label2 = Total1
   Label3 = Total2 & " "
End Sub

Properties:

VERSION 5.00 Object = "{5E9E78AO-531B-llCF-91F6-C2863C385E30}#1.0#0.'; "MSFLXGRD.OCX"
Begin VB.Form Form1
      Caption            =       "Stock Items"
      ClientHeight       =      2445
      ClientLeft       =      2835
      ClientTop       =      2790
      ClientWidth       =       6345
      LinkTopic       =      "Form1"
      ScaleHeight       =       2445
      ScaleWidth       =      6345

Beqin VB.CommandButton Command1
      Caption       =      "UpDate"
      Height             =      345
      Left             =      240
      Tablndex       =      4
      Top             =       1950
      Width             =       1005
End

Begin VB.Data Data1
      Caption            =      "Data1"
      Visible       =      0 'False
End

Begin MSFlexGridLib.MSFlexGrid Grid1
      Height             =       1605
      Left             =       330
      Tablndex       =      O
      Top             =       240
      Width             =       5715
      ExtentX       =      10081
      ExtentY       =       2831
      Version       =      393216
      Rows             =      6
      Cols             =      4
      FixedCols       =       0
      BackColorBkg       =       12632256
      AllowBigSelection  =       0 'False
      ScrollBars       =       2
      BorderStyle       =      0
      Appearance       =       O
      BeginProperty Font {OBE35203-8F91-11CE-9DE3-00AAO04BB851}
            Name       =      "Time5 New Roman"
            Size       =      9
            Charset =      0
            Weight       =       400
            Underline =      0 'False
            Italic       =      0 'False
            Strikethrough =      0 'False
            EndProperty
End

Begin VB.Label Label3
      Alignment       =       2 'Center
      Appearance       =      0 'Flat
      BackColor       =      &H80000005&
      BorderStyle      =      1 'Fixed Single
      ForeColor       =      &H80000008&
      Height             =      255
      Left             =       3570
      TabIndex       =      3
      Top             =      1980
      Width             =      1065
End

Begin VB.Label Label2
      Alignment       =      2 'Center
      Appearance       =       0 'Flat
      BackColor       =      &H80000005&
      BorderStyle       =      1 'Fixed Single
      ForeColor       =      &H80000008&
      Height             =      255
      Left             =       2250
      TabIndex       =      2
      Top             =      1980
      Width             =       1125
End

Begin VB.Label Label1
      Alignment       =       1 'Right Justify
      Caption       =      "Totals:"
      Height             =      225
      Left             =      1410
      Tablndex       =      1
      Top             =      2010
      Width             =      735
End

Hope it suites you.
0
 

Author Comment

by:maniacpfm
ID: 8040795
Hi Jo

You see, I select an item from a product table and load it on the grid. The grid is not connected to any table, nor does it update any table. My main problem is that since I add and delete on the grid, i should read only the grid. I want to delete an item if i select a wrong item. I want to also be able to change the quantity and price on the grid and the total column is recalculated for each item. I don't necessary update a table.
0
 
LVL 2

Accepted Solution

by:
JoaTex earned 200 total points
ID: 8073870
Hi There
Here it's your code to update your grid.
To insert a Row use insert key.
to delete a Row use Delete Key

Code:

Option Explicit
Dim N
Dim Total1
Dim Total2
Dim Msg
Private Sub AddRow()
    N = Grid1.Row
    If Grid1.Row = Grid1.Rows - 1 And Grid1.TextArray(N * 4 + 0) = "" Then
        Msg = MsgBox("No Item on Row!", 64, "Grid1")
        Exit Sub
    End If
    Grid1.Rows = Grid1.Rows + 1
End Sub
Private Sub DelRow()
    N = Grid1.Row
    If Grid1.Rows = 2 Then Exit Sub
    Msg = MsgBox("Do You Realy Want to Delete This Row?" & Chr(13) & "Row: " & Grid1.TextArray(N * 4 + 0), 260, "Grid1")
    If Msg = 7 Then Exit Sub  'Não
    Grid1.RemoveItem (N)
    Grid1_LeaveCell
End Sub
Private Sub Form_Activate()
   Grid1.Rows = 2
   Grid1.Row = 1
End Sub

Private Sub Form_Load()
   Me.Top = (Screen.Height - Me.Height) / 2
   Me.Left = (Screen.Width - Me.Width) / 2
   Grid1.ColWidth(0) = 2000
   Grid1.ColWidth(1) = 1200
   Grid1.ColWidth(2) = 1200
   Grid1.Row = 0
   Grid1.TextArray(0) = "                Item"
   Grid1.TextArray(1) = "     Quantity"
   Grid1.TextArray(2) = "      Price"
   Grid1.TextArray(3) = "  Cost Price"
End Sub
Private Sub Form_Unload(Cancel As Integer)
   End
End Sub
Private Sub Grid1_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 45 Then AddRow
    If KeyCode = 46 Then DelRow
End Sub

Private Sub Grid1_KeyPress(KeyAscii As Integer)
    If Grid1.Col = 2 Then Exit Sub
    If KeyAscii = 8 Then
        Grid1.Text = ""
        Exit Sub
    End If
    If Grid1.Col = 1 Or Grid1.Col = 3 Then
        If KeyAscii < 48 Or KeyAscii > 57 Then Exit Sub
    End If
    Grid1.Text = Grid1.Text & Chr(KeyAscii)
End Sub
Private Sub Grid1_LeaveCell()
   If Grid1.Col = 2 Then Exit Sub
   If Grid1.Row = 0 Then Exit Sub
    N = Grid1.Row
    If Grid1.Col = 3 And Right(Grid1.TextArray(N * 4 + 3), 1) <> "€" Then
        If Grid1.Text <> "" Then Grid1.Text = Grid1.Text & " €"
    End If
   Total1 = 0
   Total2 = 0
   Grid1.TextArray(N * 4 + 2) = Val(Grid1.TextArray(N * 4 + 1)) * Val(Grid1.TextArray(N * 4 + 3)) & " €"
   If Val(Grid1.TextArray(N * 4 + 2)) = 0 Then Grid1.TextArray(N * 4 + 2) = ""
   For N = 1 To Grid1.Rows - 1
      Total1 = Total1 + Val(Grid1.TextArray(N * 4 + 1))
      Total2 = Total2 + Val(Grid1.TextArray(N * 4 + 2))
   Next N
   Label2 = Total1
   Label3 = Total2 & " €"
End Sub
Jo
0
 

Expert Comment

by:CleanupPing
ID: 9447582
maniacpfm:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question