Solved

DBGrid

Posted on 2001-08-14
8
575 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

706 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

23 Experts available now in Live!

Get 1:1 Help Now