setting subform recordsource fails when parent is on 'NewRecord'

  • Thread starter Thread starter astro
  • Start date Start date
A

astro

The following sets the subform recordset to nothing and works when the
parent is on an existing record but fails when the parent is on a new
record - which happens after the last record is deleted:

Me.cfProgram.Form.RecordSource = ""

Any ideas why this fails?
 
What are you setting the recordsource to when the parent is NOT on
newrecord? Also, how are the forms linked, if at all, i.e., how is the
Master/Child link property set?
 
....appreciate your efforts Paul ))

recordset is set to a SQL statement...

If Not IsNull(Me!locationID) Then
mvlocationID = removeGuid(StringFromGUID(Me!txtLocationID))
Me.cfProgram.Enabled = True
Me.cfProgram.Form.RecordSource = "SELECT [LocationProgram].[locationID],
[LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID = " & "'" &
removeGuid(StringFromGUID(Me!txtLocationID)) & "';"

Call synchWithCRM
Else
Me.cfProgram.Form.RecordSource = ""
Me.cfProgram.Enabled = False
End If
 
How is cfProgram linked to the parent, if at all? Is locationID your
primary key for the parent? Is it a required field in the record?

In any case, you probably need to test for newrecord, i.e.,

If Me.NewRecord =False Then
If Not IsNull(Me!locationID) Then
'Enable subform and set recordsource
Else
'Disable subform...and set recordsource to return no records
End if
Else
'Disable subform...and set recordsource to return no records
End if


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
...appreciate your efforts Paul ))

recordset is set to a SQL statement...

If Not IsNull(Me!locationID) Then
mvlocationID = removeGuid(StringFromGUID(Me!txtLocationID))
Me.cfProgram.Enabled = True
Me.cfProgram.Form.RecordSource = "SELECT
[LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID = " & "'" &
removeGuid(StringFromGUID(Me!txtLocationID)) & "';"

Call synchWithCRM
Else
Me.cfProgram.Form.RecordSource = ""
Me.cfProgram.Enabled = False
End If


Paul Overway said:
What are you setting the recordsource to when the parent is NOT on
newrecord? Also, how are the forms linked, if at all, i.e., how is the
Master/Child link property set?
Click to expand...
Click to expand...
 
there is no linkage on the form...the data in the subform is dynamically
populated in the on_current event.....

When the cursor falls into the 'new record' territory due to a delete on the
last record in the cursor set I need to
set the subform recordset to "" so it does not show the child-data from the
deleted record...

I can't do that because of the error Access is giving me...((

Paul Overway said:
How is cfProgram linked to the parent, if at all? Is locationID your
primary key for the parent? Is it a required field in the record?

In any case, you probably need to test for newrecord, i.e.,

If Me.NewRecord =False Then
If Not IsNull(Me!locationID) Then
'Enable subform and set recordsource
Else
'Disable subform...and set recordsource to return no records
End if
Else
'Disable subform...and set recordsource to return no records
End if


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
...appreciate your efforts Paul ))

recordset is set to a SQL statement...

If Not IsNull(Me!locationID) Then
mvlocationID = removeGuid(StringFromGUID(Me!txtLocationID))
Me.cfProgram.Enabled = True
Me.cfProgram.Form.RecordSource = "SELECT
[LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID = " & "'" &
removeGuid(StringFromGUID(Me!txtLocationID)) & "';"

Call synchWithCRM
Else
Me.cfProgram.Form.RecordSource = ""
Me.cfProgram.Enabled = False
End If


Paul Overway said:
What are you setting the recordsource to when the parent is NOT on
newrecord? Also, how are the forms linked, if at all, i.e., how is the
Master/Child link property set?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com



The following sets the subform recordset to nothing and works when the
parent is on an existing record but fails when the parent is on a new
record - which happens after the last record is deleted:

Me.cfProgram.Form.RecordSource = ""

Any ideas why this fails?
Click to expand...
Click to expand...
Click to expand...
 
Rather than setting it to "", just set to something that is not going to
return any records. I assume this would work if LocationID is required in
the child record...

"SELECT [LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID Is Null"


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
there is no linkage on the form...the data in the subform is dynamically
populated in the on_current event.....

When the cursor falls into the 'new record' territory due to a delete on
the last record in the cursor set I need to
set the subform recordset to "" so it does not show the child-data from
the deleted record...

I can't do that because of the error Access is giving me...((

Paul Overway said:
How is cfProgram linked to the parent, if at all? Is locationID your
primary key for the parent? Is it a required field in the record?

In any case, you probably need to test for newrecord, i.e.,

If Me.NewRecord =False Then
If Not IsNull(Me!locationID) Then
'Enable subform and set recordsource
Else
'Disable subform...and set recordsource to return no records
End if
Else
'Disable subform...and set recordsource to return no records
End if


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
...appreciate your efforts Paul ))

recordset is set to a SQL statement...

If Not IsNull(Me!locationID) Then
mvlocationID = removeGuid(StringFromGUID(Me!txtLocationID))
Me.cfProgram.Enabled = True
Me.cfProgram.Form.RecordSource = "SELECT
[LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID = " & "'" &
removeGuid(StringFromGUID(Me!txtLocationID)) & "';"

Call synchWithCRM
Else
Me.cfProgram.Form.RecordSource = ""
Me.cfProgram.Enabled = False
End If


What are you setting the recordsource to when the parent is NOT on
newrecord? Also, how are the forms linked, if at all, i.e., how is the
Master/Child link property set?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com



The following sets the subform recordset to nothing and works when the
parent is on an existing record but fails when the parent is on a new
record - which happens after the last record is deleted:

Me.cfProgram.Form.RecordSource = ""

Any ideas why this fails?
Click to expand...
Click to expand...
Click to expand...
 
I can't set it to anything - the object reference is invalid.......but only
when the cursor is on a 'new record'.....

I am going to do another post regarding Access with a SQL-Server backend
using Guids in a replication environment ...there seems to be some other
weirdness going on with Guid's in general and MSAccess....

Thanks


Paul Overway said:
Rather than setting it to "", just set to something that is not going to
return any records. I assume this would work if LocationID is required in
the child record...

"SELECT [LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID Is Null"


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
there is no linkage on the form...the data in the subform is dynamically
populated in the on_current event.....

When the cursor falls into the 'new record' territory due to a delete on
the last record in the cursor set I need to
set the subform recordset to "" so it does not show the child-data from
the deleted record...

I can't do that because of the error Access is giving me...((

Paul Overway said:
How is cfProgram linked to the parent, if at all? Is locationID your
primary key for the parent? Is it a required field in the record?

In any case, you probably need to test for newrecord, i.e.,

If Me.NewRecord =False Then
If Not IsNull(Me!locationID) Then
'Enable subform and set recordsource
Else
'Disable subform...and set recordsource to return no records
End if
Else
'Disable subform...and set recordsource to return no records
End if


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


...appreciate your efforts Paul ))

recordset is set to a SQL statement...

If Not IsNull(Me!locationID) Then
mvlocationID = removeGuid(StringFromGUID(Me!txtLocationID))
Me.cfProgram.Enabled = True
Me.cfProgram.Form.RecordSource = "SELECT
[LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID = " & "'" &
removeGuid(StringFromGUID(Me!txtLocationID)) & "';"

Call synchWithCRM
Else
Me.cfProgram.Form.RecordSource = ""
Me.cfProgram.Enabled = False
End If


What are you setting the recordsource to when the parent is NOT on
newrecord? Also, how are the forms linked, if at all, i.e., how is
the Master/Child link property set?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com



The following sets the subform recordset to nothing and works when
the parent is on an existing record but fails when the parent is on a
new record - which happens after the last record is deleted:

Me.cfProgram.Form.RecordSource = ""

Any ideas why this fails?
Click to expand...
Click to expand...
Click to expand...
 
Frankly, I can't see why you're not using Link Master/Child Fields property.
Just set it to LocationID.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
I can't set it to anything - the object reference is invalid.......but only
when the cursor is on a 'new record'.....

I am going to do another post regarding Access with a SQL-Server backend
using Guids in a replication environment ...there seems to be some other
weirdness going on with Guid's in general and MSAccess....

Thanks


Paul Overway said:
Rather than setting it to "", just set to something that is not going to
return any records. I assume this would work if LocationID is required
in the child record...

"SELECT [LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID Is Null"


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
there is no linkage on the form...the data in the subform is dynamically
populated in the on_current event.....

When the cursor falls into the 'new record' territory due to a delete on
the last record in the cursor set I need to
set the subform recordset to "" so it does not show the child-data from
the deleted record...

I can't do that because of the error Access is giving me...((

How is cfProgram linked to the parent, if at all? Is locationID your
primary key for the parent? Is it a required field in the record?

In any case, you probably need to test for newrecord, i.e.,

If Me.NewRecord =False Then
If Not IsNull(Me!locationID) Then
'Enable subform and set recordsource
Else
'Disable subform...and set recordsource to return no records
End if
Else
'Disable subform...and set recordsource to return no records
End if


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


...appreciate your efforts Paul ))

recordset is set to a SQL statement...

If Not IsNull(Me!locationID) Then
mvlocationID = removeGuid(StringFromGUID(Me!txtLocationID))
Me.cfProgram.Enabled = True
Me.cfProgram.Form.RecordSource = "SELECT
[LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID = " & "'" &
removeGuid(StringFromGUID(Me!txtLocationID)) & "';"

Call synchWithCRM
Else
Me.cfProgram.Form.RecordSource = ""
Me.cfProgram.Enabled = False
End If


message What are you setting the recordsource to when the parent is NOT on
newrecord? Also, how are the forms linked, if at all, i.e., how is
the Master/Child link property set?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com



The following sets the subform recordset to nothing and works when
the parent is on an existing record but fails when the parent is on
a new record - which happens after the last record is deleted:

Me.cfProgram.Form.RecordSource = ""

Any ideas why this fails?
Click to expand...
Click to expand...
Click to expand...
 
see my post " MSAccess subforms and Guids - linkage not being updated when
on 'new record'



Paul Overway said:
Frankly, I can't see why you're not using Link Master/Child Fields
property. Just set it to LocationID.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


astro said:
I can't set it to anything - the object reference is invalid.......but
only when the cursor is on a 'new record'.....

I am going to do another post regarding Access with a SQL-Server backend
using Guids in a replication environment ...there seems to be some other
weirdness going on with Guid's in general and MSAccess....

Thanks


Paul Overway said:
Rather than setting it to "", just set to something that is not going to
return any records. I assume this would work if LocationID is required
in the child record...

"SELECT [LocationProgram].[locationID], [LocationProgram].[programID] "
_
& " FROM LocationProgram WHERE locationID Is Null"


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


there is no linkage on the form...the data in the subform is
dynamically populated in the on_current event.....

When the cursor falls into the 'new record' territory due to a delete
on the last record in the cursor set I need to
set the subform recordset to "" so it does not show the child-data from
the deleted record...

I can't do that because of the error Access is giving me...((

How is cfProgram linked to the parent, if at all? Is locationID your
primary key for the parent? Is it a required field in the record?

In any case, you probably need to test for newrecord, i.e.,

If Me.NewRecord =False Then
If Not IsNull(Me!locationID) Then
'Enable subform and set recordsource
Else
'Disable subform...and set recordsource to return no records
End if
Else
'Disable subform...and set recordsource to return no records
End if


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


...appreciate your efforts Paul ))

recordset is set to a SQL statement...

If Not IsNull(Me!locationID) Then
mvlocationID = removeGuid(StringFromGUID(Me!txtLocationID))
Me.cfProgram.Enabled = True
Me.cfProgram.Form.RecordSource = "SELECT
[LocationProgram].[locationID], [LocationProgram].[programID] " _
& " FROM LocationProgram WHERE locationID = " & "'" &
removeGuid(StringFromGUID(Me!txtLocationID)) & "';"

Call synchWithCRM
Else
Me.cfProgram.Form.RecordSource = ""
Me.cfProgram.Enabled = False
End If


message What are you setting the recordsource to when the parent is NOT on
newrecord? Also, how are the forms linked, if at all, i.e., how is
the Master/Child link property set?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com



The following sets the subform recordset to nothing and works when
the parent is on an existing record but fails when the parent is on
a new record - which happens after the last record is deleted:

Me.cfProgram.Form.RecordSource = ""

Any ideas why this fails?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top