ADODB relationship table not working.

[starclassic]

Hi,
Im still fairly new with ADO, but I have a back end database (tesing.mdb) with table tblCollectionsData and tblAdjustments which both have a relationship base on policynumber. Front end have mainfrm(tblCollections) and subform (tbladjustment).

I Have these code to connect to database and bind to the form (frmCollectionsData)and same thing for the subfrom (frmAdjustments).
But when I scroll thru the record on main form the subform will not display the data related to the main form.
The relationship have been established on the back end database. Any Idea how to go about this.
Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = _
"W:\RODP\DB Projects\Collections Database\Testing.mdb"

'.ConnectionString = ConnString
.Open
End With

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM tblCollectionsData"
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.Open
End With
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub

 

[dhookom]

Why aren't you using bound forms?
Have you set the link master/child properties of the subform control?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BobRodes]

<Why aren't you using bound forms?

I can't speak to that, and if Duane is asking it's probably a good idea in Access. I'm more of a VB6 person. However, your connection string looks off. You seem to have two providers in it, for one. In VB, we would use the JET provider, thus:

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=W:\RODP\DB Projects\Collections Database\Testing.mdb"

assuming that there is no password protection on the file. For more information, check the popular

http://www.connectionstrings.com/.

HTH

Bob

 

[starclassic]

I could used a bound form but I just wanna give it a try first using the ADO if its the best route especially the data are access by quite a lot of people.

 

[dhookom]

You would need to update the subform's sql string with every record change in the main form. This only happens automatically when you have the subform bound and the Link Master/Child properties set.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BobRodes]

<You would need to update the subform's sql string with every record change

Yup, that's how I do it in VB. Actually, it isn't the resource hog that it might appear to be.

 

[BobRodes]

One other note: if you're trying to minimize round trips to the server, you can also pull the entire child recordset into the client context and change the filter property as you go. If you do this, you can have uneven performance on a large recordset, and you would want to consider applying a dynamic index. (I'm assuming that you're wanting to do "manual binding" with some sort of unbound form. Again, I don't have a deep understanding of Access forms.)