Using Class Module to fill unbound ADO Data Grid ??

[DougP]

I want to fill a data grid without using and ADO control so it is not bound and left open all the time.
I found this in help and thought I could modify it to read in Access data but can't get it to work.

Private WithEvents Conn2 As ADODB.Connection
Private WithEvents rsNames As ADODB.Recordset

Private Sub Class_GetDataMember(DataMember As String, Data As Object)
    Set Data = rsNames
End Sub

Private Sub Class_Initialize()
   ' Add the names of the new datamember to the DataMember collection
   ' This allows other objects to see the available DataMembers
   DataMembers.Add "BusinessOps"

    Dim SQLCode As String
    
    Set Conn2 = New ADODB.Connection
    Conn2.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\main.mdb"
    
   Set rsNames = New ADODB.Recordset ' Set the object variable.
    SQLCode = "SELECT * FROM [BusinessOps];"
    rsNames.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic

   ' Create a recordset with two fields and open the recordset. The
   ' first record has an integer data type and the second is a string,
   ' with a maximum of 256 characters. The CursorType is set to
   ' OpenStatic--an updatable snapshot of a set of records. The
   ' LockType is set to LockOptimistic to allow updates to the
   ' recordset
   GoTo Skip
   With rsNames
      .Fields.Append "ID", adInteger
      .Fields.Append "Name", adBSTR, 255
      .CursorType = adOpenStatic
      .LockType = adLockOptimistic
      .Open
   End With

   Dim i As Integer
   For i = 1 To 10 ' Add ten records.
      rsNames.AddNew
      rsNames!ID = i
      rsNames!Name = "Name " & i
      rsNames.Update
   Next i
Skip:
    rsNames.MoveLast
    rsNames.MoveFirst ' Move to the beginning of the recordset.
End Sub

If I 'REM' out the Goto Skip it fills the data grid with two fields, ID and name
other wise it’s all blank
It does read in my data ‘casue I can print it in the Debug window
Debug.pring rsNames!
Help
Boss wants me to use a List box. I want to prove a Data grid is superior.

DougP, MCP, A+

 

[Whatsit]

Try stepping thru your code with "F8", where does it go after "rsNames.Movefirst" ??????

 

[johnwm]

Are you sure that you've posted the right bit of code? This code appears to modify a database table structure and add some dummy records.

What sort of Datagrid are you using? The standard Datagrid does not support an unbound mode. You can use the MSFlexgrid (see faq222-3262 for details of use and editing)

To open a recordset and populate a control (this one uses a List, but similar will work for an unbound grid:

Private Sub Command1_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strCn As String
    Dim strSQL As String
    Dim strPathtoDB As String

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    strPathtoDB = App.Path & "\Db1.mdb"
    strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPathtoDB
    cn.Open strCn
rs.Open ("select clientname from tblclients"), cn
Do While Not rs.EOF
  List1.AddItem rs.Fields(0)
  rs.MoveNext
Loop
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[DougP]

yes I got this from help there is more

here is the part that binds it to the datagrid

Private Sub Form_Load()
   ' Create a new NamesData Object
   Set datNames = New NamesData
    
   ' Bind the DataGrid to the new DataSource datNames
    Set DataGrid1.DataSource = datNames

End Sub

the name of the Class module is "NamesData".

The original code creates a table, adds records to it, then those 10 records show in the datagrid
I just want to open my table and have those records show instead.

DougP, MCP, A+

 

[johnwm]

Your question says:

I want to fill a data grid without using and ADO control so it is not bound and left open all the time.

You now appear to to realise that you are using a standard Datagrid, which is a bound control - which do you actually want, bound or unbound? Generally you will get a lot more control of your data by using unbound controls and populating them yourself. One way of doing that is shown in my previous post above. Or have I misunderstood your problem?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[DougP]

What I want is to open a record set with code and populate the Datagrid with a SQL string without using and ADO control
The Boss thinks that by using the Normal ADO control and Datagrid that if the power goes off they are going to loose everything. or corrupt the underlying Access database.

I need to get this figured out. The Datagrid is a much better way to present data
He want’s to use a list box ‘cause it can be done as mentioned above. It does not leave the database open the whole time it is open.


DougP, MCP, A+

 

[johnwm]

You can have the best of both worlds by using a Flexgrid - see my (much) earlier post

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[DougP]

I've seen the Flex grid before but I can't find it now. What is the exact spelling of it?

There must be 1000 items in the component list of this computer.


DougP, MCP, A+

 

[johnwm]

Project|Components|Controls|Microsoft Hierarchical Flexgrid Control 6.0

It's part of MSHFLXGD.OCX

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts