Solved

DBGrid

Posted on 2001-08-14
8
604 Views
Last Modified: 2011-10-03
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
Comment
Question by:s_ling
[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
8 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 80 total points
ID: 6383346
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
 

Author Comment

by:s_ling
ID: 6386849
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
 

Author Comment

by:s_ling
ID: 6386851
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:s_ling
ID: 6386854
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 6396131
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
 

Author Comment

by:s_ling
ID: 6438987
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7199938
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
 
LVL 5

Expert Comment

by:Netminder
ID: 7213362
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

688 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