VB5: DAO-DbGrid Data Bindings Across Forms Possible? 1

[hafod]

Hi,
I hope I phrased this post correctly, possibly not!

Currently I have a dBGrid object which is data bound to a DAO recordset on the same form. The recordset datMembers' is linked to 'tblMembers' in the back end MSAccess98 database using a 'Dynaset' type recordset. All works fine!

However, I have tried unsuccessfully to make my solution more elegant placing a dBGrid on a second form specifically to view user details in a 'broadsheet' format thus de-cluttering the calling form. The DBGrid on the second form therefore needs to be 'data-bound' to the DAO on the calling form.

So I suppose I am looking for data-bindings across form objects. Can this be done?. I have tried unsuccessfully using several approaches - using the grids datasource property and attempting to link the DBGrid from the calling form. I cant find anything in my Wrox VB6 Programming book (My Bible!) or other published sources which help on this issue; they all focus on data linking DAO/ADO recordsets to display objects - on the same form!

This type of functionality is common with many applications when related sub-windows are brought up modally, but the technique eludes me at present.

Thanks in anticipation of advice

 

[SBerthold]

ADO would be easier work with something like this, because you do not need the DataControl.

My suggestion is to remove the DataControls, so all information in them is gone, then place new ones on the forms, with the same name, and DO NOT set any properties for them in the properties window!

Then, Do the following:

In Form1,

Private mRSData as DAO.Recordset
Private mDBMain as DAO.DataBase

Private Sub Form_Load()

    Set mDBMain = DAO.OpenDataBase("C:\SomeFolder\SomeDB.Mdb",False,False)

    Set mRSData = mDBMain.OpenRecordset("SELECT * FROM SomeTable",dbOpenDynaset)

    Set Data1.Recordset = mRSData 

End Sub

In Form2:

Private mRSData as DAO.Recordset

Public Sub OpenForm(rs AS DAO.Recordset)
    Set mRSData = rs

    Me.Show

End Sub

To open Form2, do this:

Dim myForm2 as Form
Set myForm2 = New Form2
Call myForm2.OpenForm(mRSData)

Basically, if the recordset in form2 is different, such as a data from a "sub" table, then just create also in it a module level database object, and pass the mDBMain Database object to the OpenForm proceedure, setting it to the Private Database variable, and also pass the PrimaryKey from the current record from the recordset in Form1, and open the new Recordset using the PrimaryKey for the criteria.

(This code was typed from memory and without testing: There may be some small errors)

 

[hafod]

Hi
Many thanks for your response SBerthold. (Im glad you correctly interpreted my post!) I have tried your DAO code. A DBGrid on the 'calling' form works well against a sample database in 'c\temp' sub dir. However there is an offending line of code - syntax probably on the destination, which causes an error at the line: 'Call myForm(2).OpenForm(mRSData)'. The call is on the click event of 'Command1' on the calling Form: Form2. The destination form is named 'Form3' for the purpose of this example.

Incidentally I usually use 'Unload.Me' and frmXXX.Show' to load/unload forms; your approach dosent require this approach - or have I overlooked something?

I have now tried permutations of the call method but to no avail using the VB Help Feature highlighting 'call'. I am having some difficulty now because I have limited experience of programmaticaly creating objects. Can you assist further here? I dont think I am 'that far away' from a solution now.

Many thanks
Mike

Code From the 'Calling' Form - Form2

Private mRSData As DAO.Recordset
Private mDBMain As DAO.Database

Private Sub Command1_Click()
Dim myForm(2) As Form
Set myForm(2) = New Form2
Call myForm(2).OpenForm(mRSData)
End Sub



Private Sub Form_Load()


    Set mDBMain = DAO.OpenDatabase("C:\Temp\DbTest.Mdb", False, False)

    Set mRSData = mDBMain.OpenRecordset("SELECT * FROM tblOrders", dbOpenDynaset)

    Set datOrders.Recordset = mRSData

 

[SBerthold]

I just tested my code and it works fine for me.
I only forgot to Set the DataControl in the Form2:

Public Sub OpenForm(rs As DAO.Recordset)
Set mRSData = rs

Set Data1.Recordset = mRSData
Me.Show

End Sub

And I tested the code for the Command_Click event which you have posted. Works also.

The problem is probably some other additional code which you have in the form you are calling, either in the OpenForm method, or the Form_Load or Form_Activate events.

Start a sample application with two forms and add just the code which I have posted (adding the missing SET statement).

I think though that your problem is because you have Set the Form object variable to the wrong form type:

The call is on the click event of 'Command1' on the calling Form: Form2. The destination form is named 'Form3'

Set myForm(2) = New Form2

In this example you would need to change this to Form3, if that is the child form:
Set myForm(2) = New Form3

>I usually use 'Unload.Me'

It depends on what you are trying to do.

In my example, Form1 is the Parent, and Form2 the Child. The Parent remains open when the Child is Opened.

 

[hafod]

Hi
Many thanks indeed for your last post - problem resolved ad your solution works fine. I added a command button to the destination form - Form3 to confirm I could do basic recordset programming tasks on the common data set - and yes, I can (simple code for this 'test' button on Form3 below:

Private Sub Command1_Click()
Dim intRecordCount As Integer
Dim strOrderID As String


mRSData.MoveFirst
mRSData.MoveLast
intRecordCount = mRSData.RecordCount

Do While Not mRSData.EOF
MsgBox intRecordCount
With mRSData
    strOrderID = !OrderID
    MsgBox strOrderID
    .MoveNext
End With

Loop

End Sub

You suggested that it would be simpler to achieve this functionality using ADO recordsets. Can you indicate how this would be done in this context. I have no experience of using ADO but looking at my 'ADO,ASP and XMP Complete book' it' s not for the faint hearted.
Many thanks again,
Mike

 

[SBerthold]

Great!


ADO: It's not difficult at all.

I said "simpler", becaause of working with the Data using the DataGrid (no DataControl needed) and because of not worrying about changes with Data in DataBound controls affecting the actual DB Table until you are actually ready to update (using client side cursors).

 

[hafod]

Many thanks again
Mike ;-}