• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

DBGrid

Hi,

I would like to know how can I display, add, delete, edit  the records  in unbound DBGrid.

The fields for the DBGrid are Tree No, Species Code, Type, Diameter(cm), Height(m) and Volume(m3).

---------
tree no, species code, diameter, height and volume are in table called ASM while type is in table called SPECY in access.
--------

after the users get to this form, the system needs to display the records in DBGrid before allow the users to add, edit, delete.
to display the records, the system needs to find in ASM table for "PECREFNO", "BLOCK NO", "BLOCK AREA", "STRIP NO", "STRIP AREA".  

If under the  "PECREFNO", "BLOCK NO", "BLOCK AREA", "STRIP NO", "STRIP AREA", got records for Tree No, Species Code, Type, Diameter(cm), Height(m) and Volum(m3). Then, display it and allow the users to edit or delete.  if not, allow the users to add.

besides, is that possible for me to do renumbering (for tree no) and set the function like combo box (for species code) in DBGrid.  
0
s_ling
Asked:
s_ling
1 Solution
 
TimCotteeCommented:
s_ling, a lot of questions there, my suggestion would be the following: use a DataGrid rather than the DBGrid; this allows you to use a disconnected ADO recordset that you create in code as the bound source of the grid. This has the advantage that you can add/edit/delete records just as you would normally with a bound grid but that the records exist only in the memory of the machine. To simplify the storing of this data you can also take advantage of the .Save method of the ADO recordset which allows saving and loading of the recordset to/from an XML file. You can also add a combo box to an individual cell using the following example:

You need to set the button property of the appropriate column to true:

Datagrid1.Columns(1).Button = True

And then use the following to make the datacombo appear in the cell when the button is clicked.

Private Sub DataGrid1_ButtonClick(ByVal ColIndex As Integer)
' Assign the Column object to Co because it will be used
' more than once.
Dim Co As Column
 
Set Co = DataGrid1.Columns(ColIndex)
 
' Position and drop down list1 at the right edge of the
' current cell.
DataCombo1.Left = DataGrid1.Left + Co.Left + Co.Width
DataCombo1.Top = DataGrid1.Top + DataGrid1.RowTop(DataGrid1.Row)
DataCombo1.Visible = True
DataCombo1.ZOrder 0
DataCombo1.SetFocus
End Sub

Private Sub DataGrid1_Scroll(Cancel As Integer)
' Hide the list if we scroll.
DataCombo1.Visible = False
End Sub

Private Sub List1_Click()
'Update the appropriate field in the record.
DataCombo1.Visible = False
End Sub

When the button is clicked, the data combo is displayed next to the field. Choosing an item
from the list will then update the record (you need to put in the appropriate code for this) and hide the datacombo.

Here is how you would create a recordset, this is not the structure for you but you get the idea:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst.Fields
 .Append "Field1",adInteger
 .Append "Field2",adBStr,50
 .Append "Field3",adDBDate
End With
rst.Open
Set DataGrid1.DataSource = rst

You can then add/delete/update rows in the datagrid and the data is stored in the underlying recordset
which exists only in the memory of the machine on which the application is running. When you have finished
with the recordset just use

rst.Close
Set rst = Nothing

To free the resources used.
0
 
s_lingAuthor Commented:
Hi TimCottee,

There is a compile error "Invalid Outside Procedure" in line "Set rst = New ADODB.Recordset". What's wrong with that?

Besides, if I want to join a field from another table in the column(2) of the datagrid1, how can I write? is that possible for the column to show the records automatically after the users selected the "SPECIES CODE" from the column that has combo box function? for example, "MRPX" is one of the species code. After the users selected "MRPX", the next column will show the "TYPE" for "MRPX" automatically. For example, "01".  
0
 
s_lingAuthor Commented:
Hi TimCottee,

There is a compile error "Invalid Outside Procedure" in line "Set rst = New ADODB.Recordset". What's wrong with that?

Besides, if I want to join a field from another table in the column(2) of the datagrid1, how can I write? is that possible for the column to show the records automatically after the users selected the "SPECIES CODE" from the column that has combo box function? for example, "MRPX" is one of the species code. After the users selected "MRPX", the next column will show the "TYPE" for "MRPX" automatically. For example, "01".  

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
s_lingAuthor Commented:
Hi TimCottee,

There is a compile error "Invalid Outside Procedure" in line "Set rst = New ADODB.Recordset". What's wrong with that?

Besides, if I want to join a field from another table in the column(2) of the datagrid1, how can I write? is that possible for the column to show the records automatically after the users selected the "SPECIES CODE" from the column that has combo box function? for example, "MRPX" is one of the species code. After the users selected "MRPX", the next column will show the "TYPE" for "MRPX" automatically. For example, "01".  

0
 
TimCotteeCommented:
s_ling, apologies, I don't seem to have been getting any notifications on this one.

The section you have a problem with is not intended to be pasted directly into the code window. You need to include this probably in the Form_Load event code. Except for the Dim statement which should be in the declarations section of the form.

As for joining another table, I thought that initially your requirement was for the grid not to be bound to a database. If you want to have a join in the grid then you must create the select statement to join two tables and then display them. However perhaps that is not what you are really asking for. By all means you can use the Click event of the combo box to retrieve the matching detail from a database table when selected and populate a specific column in the grid. This though is not really a joining of tables, more of a lookup.
0
 
s_lingAuthor Commented:
TimCottee,

I didn't bound the data grid to a database.
Actually is that possible for me to display the records and at the same time allow add, save, modify and delete in data grid?
0
 
DanRollinsCommented:
Hi s_ling,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept TimCottee's comment(s) as an answer.

s_ling, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now