Solved

Making data grid

Posted on 2004-08-09
21
513 Views
Last Modified: 2013-12-25
Hi there,
I would like to create a data grid displaying an address book. I use Access 2003. I can do that by using ADO data control and set everything so it displays all the records in my table (name, address, phone, etc). My question is can I display just the column name first?I want to display a certain record after the user search for it. Can I do that with ADO data Control? I think it is better if I use code rather than ADO Data control.

thanks
0
Comment
Question by:Cyber_Cus2
  • 11
  • 10
21 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 11755552
Yes, do not use the ADODC.  Use ADO recordsets and then use the Filter to dispaly a particular record.

Here is an ADO tutorial:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=12050&lngWId=1

This is how to use the Filter property:
http://www.activeserverpages.ru/ADO/daprop03_5.htm

Leon

0
 

Author Comment

by:Cyber_Cus2
ID: 11760726
Ok Now I understand about ADO recordset, but I still have same problem. I can't bind it with my datagrid. Do you have any idea?I just want to display the column name first. I will put the record after user search it.
thanks
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11762636
Once you get the recordset you can set it as a source for your grid:

Set dgdAddressBook.DataSource = recRecSet

Here is a sample how to work with it;

http://www.eggheadcafe.com/ng/microsoft.public.vb.controls/post252500.asp

Leon

0
 

Author Comment

by:Cyber_Cus2
ID: 11764230
dim myConn As ADODB.Connection
dim myRecSet As ADODB.Recordset    
Set myConn = New ADODB.Connection
Set myRecSet = New ADODB.Recordset
myConn.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=D:\Data\Immanuel\Program\Immanuel.mdb;"
myRecSet.Source = "SELECT * FROM [Address Book]"
Set dgdBook.DataSource = myRecSet

Is that correct?? I still can't see any column name in the datagrid
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11764337
Looks good.

Check the ColumnHeaders property of the grid.

Here is a refence page. (Just ignore the ADODC stuff);

http://www.raritanval.edu/departments/CIS/full-time/Schwarz/avb/Lesson6A.htm

Leon
0
 

Author Comment

by:Cyber_Cus2
ID: 11764546
I think that page is just showing the column header. In my data grid there is a column header but no name on it. I have 5 columns in my database, but the datagrid just have 2 columns. It is like my datagid is not connect with my database
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11764722
Are you saying that your grid is only displaying 2 out of 5 columns?
0
 

Author Comment

by:Cyber_Cus2
ID: 11764861
yes exactly!It looks like a dead datagrid no interaction with my database.Where should I put that code? On form load right?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11764903
If you are saying that your grid displays two column without you running the above code, then it means you did not remove all of the old setup you did.  The quickest wa to correct this would be to delete the grid and add a new one.  name it the same as teh old one.

Place the connection code in the Form_Load.  Let me know how that works.

Leon
0
 

Author Comment

by:Cyber_Cus2
ID: 11765150
negative Leon.  I've recreated the grid and put the code above in form load and the result is the same, the grid can show nothing. Is that something wrong with my code?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 29

Expert Comment

by:leonstryker
ID: 11765199
Lets see your Form_Load code.

Leon
0
 

Author Comment

by:Cyber_Cus2
ID: 11765240
Private Sub Form_Load()
    frmAddressBook.Top = (Screen.Height - frmAddressBook.Height) / 2
    frmAddressBook.Left = (Screen.Width - frmAddressBook.Width) / 2
   
    Dim myConn As ADODB.Connection
    Dim myRecSet As ADODB.Recordset
    Set myConn = New ADODB.Connection
    Set myRecSet = New ADODB.Recordset
    myConn.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=D:\Data\Immanuel\Program\Immanuel.mdb;"
    myRecSet.Source = "SELECT * FROM [Address Book]"
    Set dgdBook.DataSource = myRecSet
   
End Sub

same like above
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11765398
Sorry, did not look carefully at your code.  Try this:

Private Sub Form_Load()
Dim myConn As ADODB.Connection
Dim myRecSet As ADODB.Recordset
Dim strSQL As String
    With frmAddressBook
        .Top = (Screen.Height - .Height) / 2
        .Left = (Screen.Width - .Width) / 2
    End With
    Set myConn = New ADODB.Connection
    Set myRecSet = New ADODB.Recordset
    myConn.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=D:\Data\Immanuel\Program\Immanuel.mdb;"
   
    strSQL = "SELECT * FROM [Address Book]"
    myRecSet.Open strSQL, myConn, adOpenDynamic, adLockOptimistic

    Set dgdBook.DataSource = myRecSet
End Sub

Leon
0
 

Author Comment

by:Cyber_Cus2
ID: 11765515
Set dgdBook.DataSource = myRecSet  runtime error the rowset is not bookmarkable.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11765617
Private Sub Form_Load()
Dim myConn As ADODB.Connection
Dim myRecSet As ADODB.Recordset
Dim strSQL As String
    With frmAddressBook
        .Top = (Screen.Height - .Height) / 2
        .Left = (Screen.Width - .Width) / 2
    End With
    Set myConn = New ADODB.Connection
    Set myRecSet = New ADODB.Recordset
    myConn.CursorLocation = adUseClient      '<------------------   Add this
    myConn.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=D:\Data\Immanuel\Program\Immanuel.mdb;"
   
    strSQL = "SELECT * FROM [Address Book]"
    myRecSet.Open strSQL, myConn, adOpenDynamic, adLockOptimistic

    Set dgdBook.DataSource = myRecSet
End Sub

Leon
0
 

Author Comment

by:Cyber_Cus2
ID: 11765732
looks pretty now ;) but can i hide the records?I just want to dislpay the column name 1st
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 11765786
>>but can i hide the records?I just want to dislpay the column name 1st

I think you may be able to do that by setting the Filter property of the recordset to a record which does not exist.  Give it a try and let me know if it works.

Leon
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11765831
BTW, if you are going to play around with displaying records in your grid, you should declare your connection and recordset objects on a modular level. At the top of your Form code:

Private myConn As ADODB.Connection
Private myRecSet As ADODB.Recordset

If you have multiple forms and/modules which require database connection, you should declare your connection globally.  At the top of a Module, not behind a form:

Public myConn As ADODB.Connection

Leon
   
0
 

Author Comment

by:Cyber_Cus2
ID: 11766009
Ok, well done Leon. I just add this filter: myRecSet.Filter = "Name=" & Val(txtNameText) and everything is good now, thanks a lot for your help ;)
0
 

Author Comment

by:Cyber_Cus2
ID: 11766042
yes actually I declare the connection on module. I change it to make you clear about my code, thanks
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11766093
No problem and thanks for the grade,

Leon
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
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…
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…

757 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

20 Experts available now in Live!

Get 1:1 Help Now