New Record Subform RecordSource Error

[scott]

The below code is being used on the OnCurrent event of my main form. It
changes or syncs the nested subform's recordsource when the user advances to
another record. It works fine except when the current record is a new
record. The SQL fails because the
[Forms]![Switchboard]![subfrmWindow].[Form].[objID] id field on the main
form is null because it's a new record.

In my code below, I tried to detech if the record was a new record, but for
some reason, the first IF test below always fires, even on a new record.
Even if I do succed in deteching for a new record, I'm not sure what the
ELSE SQL statement should be for the subform.

Any ideas on dealing with the recordsource of a subform when going to a new
record? This is an ADP file, but I think regular mdb files would suffer from
the same poblem.


CODE:

If (Not Screen.ActiveForm.NewRecord) Then
sSQL = "SELECT idField1, Field2 " & _
"FROM myTable " & _
"WHERE idField1 = " &
[Forms]![Switchboard]![subfrmWindow].[Form].[objID] & " " & _
"ORDER BY Field2"

Else
sSQL = "SELECT idField1, Field2 " & _
"FROM myTable " & _
"WHERE idField1 = " &
[Forms]![Switchboard]![subfrmWindow].[Form].[objID] & " " & _
"ORDER BY Field2"
End If

Forms!Switchboard.Form!subfrmWindow.Form!f_objects_sub_Tab.Form.RecordSource
= sSQL

 

[Baz]

I am baffled as to why you are doing this at all. Why not just link the
subform to the main form using the "Link Child Fields" and "Link Master
Fields" properties of the subform control?

 

[scott]

Thanks for your interest, but I solved the problem. The reason I have to go
about it with no "child linking" is that it's an adp file. I solved it by
just creating a sproc as the form's record source and passing the params.
adp files don't behave as nicely and easily to the rules as an mdb file
does.

I am baffled as to why you are doing this at all. Why not just link the
subform to the main form using the "Link Child Fields" and "Link Master
Fields" properties of the subform control?

The below code is being used on the OnCurrent event of my main form. It
changes or syncs the nested subform's recordsource when the user advances to
another record. It works fine except when the current record is a new
record. The SQL fails because the
[Forms]![Switchboard]![subfrmWindow].[Form].[objID] id field on the main
form is null because it's a new record.

In my code below, I tried to detech if the record was a new record, but for
some reason, the first IF test below always fires, even on a new record.
Even if I do succed in deteching for a new record, I'm not sure what the
ELSE SQL statement should be for the subform.

Any ideas on dealing with the recordsource of a subform when going to a new
record? This is an ADP file, but I think regular mdb files would suffer from
the same poblem.


CODE:

If (Not Screen.ActiveForm.NewRecord) Then
sSQL = "SELECT idField1, Field2 " & _
"FROM myTable " & _
"WHERE idField1 = " &
[Forms]![Switchboard]![subfrmWindow].[Form].[objID] & " " & _
"ORDER BY Field2"

Else
sSQL = "SELECT idField1, Field2 " & _
"FROM myTable " & _
"WHERE idField1 = " &
[Forms]![Switchboard]![subfrmWindow].[Form].[objID] & " " & _
"ORDER BY Field2"
End If

Forms!Switchboard.Form!subfrmWindow.Form!f_objects_sub_Tab.Form.RecordSource
= sSQL