Misbehaving Subform

[Andrew Thackray]

Hi

Can anyone help. I have an obscure problem with an unbound sub form on my main form. Both forms are unbound as my data structure is too complex for a sinngle binding.

In the main form I have been setting the sub forms recordsource property and refreshiug it each time I want to change the records displayed. The data in the subform has an association via a secondary key to a primary table

This worked fine until I converted the data to Access 2003 format from access 2000 format Now suddenly the subform will only return records whern the SQL query asks for the set of records for the first key in the primary table. If I change the query so that is requests any onther record set I get no records returned. Yet if, in the debu window I cut and paste the subform recordsource query into an access query the query returns the right records.

The subform controls are data bound to the fields returned in the recordsource query.

The subroutine for resetting the subform is as follows. It is designed only to change the subform if records will be returned by the SQL passed to the routine

Sub ResetAllocations(ByVal SQL As String)

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

If SQL <> "" Then
Set conn = CurrentProject.Connection
With rs
.Open SQL, conn, adOpenDynamic
If Not .EOF And Not .BOF Then
Me.Activity_Resource_Allocations.Form.RecordSource = SQL
Me.Activity_Resource_Allocations.Form.Requery
Me.Activity_Resource_Allocations.Form.Refresh
End If
.Close
conn.Close
End With
End If

[Andrew Thackray]

Solved IT !!

I had master & child keys linked in the form even though it was not bound. Removing the links fixed the problem