Okay, let's explicity save anything that's doing on on the main form, and
check that it is not at a new record (which would generate orphans in the
subform).
Then after assigning the RecordSource to the subform, get Access to tell us
the results:
Private Sub NotesTab_Change()
Dim TabValue As Long, SQLs As String
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Not changed"
Else
TabValue = Me.NotesTab.Value
SQLs = "SELECT [Call Log].* FROM [Call Log] WHERE (([Call
Log].[Staff ID]=" & TabValue & ") And ([Call Log].Hide = False)) ORDER BY
[Call Log].[Date Time] DESC;"
With Me.Call_Log_Subform_in_Venue_Form
.Form.RecordSource = SQLs
Debug.Print "For StaffID " & TabValue & " at " & Now()
Debug.Print "RecordSource: " & .Form.RecordSource
Debug.Print "LinkChildFields: " & .LinkChildFields
Debug.Print "LinkMasterFields: " & .LinkMasterFields
With .Form.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
End If
Debug.Print .RecordCount & " record(s)"
End With
Debug.Print
End With
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Rod said:
Private Sub NotesTab_Change()
Dim TabValue As Long, SQLs As String
TabValue = Me.NotesTab.Value 'not used but will replace the "1" in SQL
below
SQLs = "SELECT [Call Log].* FROM [Call Log] WHERE ((([Call Log].[Staff
ID])=" & 1 & ") And (([Call Log].Hide)=False)) ORDER BY [Call Log].[Date
Time] DESC;"
Me.Call_Log_Subform_in_Venue_Form.Form.RecordSource = SQLs
Me.Call_Log_Subform_in_Venue_Form.LinkChildFields = "Venue ID"
Me.Call_Log_Subform_in_Venue_Form.LinkMasterFields = "Venue ID"
Me.Call_Log_Subform_in_Venue_Form.Requery
End Sub
if I break on the end sub and
debug.? Me......RecordSource
I get the correct SQLs
If I cut and paste that sql into a query it gives the records I expect.
It is as if the requery does not work.
Allen Browne said:
Rod, it might help if you post the complete event procedure for the
Change
event of the tab control.
We can then suggest some Debug.Print statements, or breakpoints so you
can
verify what is executing and what is failing.
When you say, "it don't work", can you be more specific? Does it fail
with
an error? Return no records? Return wrong records?
The subform has a Select without Where clause for the record source.
I am trying to set it to a Select with a Where clause. This is not
happening, the original Select is being used.
Presumably this is executed from the main form's module. What event?
Click
of a button?
The sub form is on a tab control. The event is changing tabs. I know
the
event is fired.
Is there any chance the subform could be dirty at the time when this
executes? (It would not be from user entry, but could be programmatically
dirty.)
I'm not smart enough to answer this question.
You "took it from the subform property", so the assignment does
work.
Did
you also read the LinkMasterFields and LinkChildFields properties of the
subform control? When you assign a RecordSource, Access assigns whatever
it
feels like to these properties.
OK, I have now added
Me.MySubform.LinkChildFields = "Venue ID"
Me.MySubform.LinkMasterFields = "Venue ID"
with no change. The right linking is taking place.
From the code behind a form I want to be able to change the record
source
for a subform
something like this.
SQLstr="Select ......"
Me.MySubform.Form.RecordSource=SQLstr
Me.MySubform.Form.RecordSource.Requery
but it don't work.
I am very sure the SQL is fine because I took it from the subform
property.
