Solved

DBGrid

Posted on 2001-08-14
8
583 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now