Subform sourceobject problem

[neil.mcdonald]

Hi,

I have an unbound main form with a single subform. The sourceobject
for the subform is changed to various forms using command buttons on
the main form.

One of the subforms is a search, and another is a detailed view of the
record.

What I would like to do is double-click the row on the search form, and
display the detailed form in it's place, but with the relevant record.

The code I have so far is:

Dim JobNo As Integer
JobNo = Me.JobID.Value
DoCmd.OpenForm "JobForm", , , "JobId = " & JobNo, , acHidden
Forms!Main.SubForm.SourceObject = "JobForm"

The DoCmd statement seems to work if the form is opened normally (i.e.
not hidden), but the code above opens the subform at the first record.

Can anybody tell me what I'm missing?

Thanks,

Neil

 

[Allen Browne]

Okay, there's a bunch of stuff happening here.

Firstly, you cannot use OpenForm to load a subform control. You need to set
its SourceObject property. In doing so, you cannot pass a WhereCondition, so
you will need to find the record, or appy a filter instead.

Another interesting aspect is that you are unloading the subform while its
module is still running, and replacing it with something else. Access will
probably let you get away with that, but it makes me a little squeamish. I
would prefer to pass control to a generic function in a standard module, so
there is no issue with fully unloading the subform's module.

This is just an aircode example of the kind of thing. I've assumed your
subform control is named Sub1. (Subform is a keyword.) Access can assign the
wrong stuff to LinkMasterFields/LinkChildFields, so we make sure those are
clear.

Private Sub JobID_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.JobID) Then
strWhere = "JobID = " & Me.JobID
Call LoadOtherSubform(Me.Parent!Sub1, "JobForm", strWhere)
End If
End Sub

Public Function LoadOtherSubform(sfm As Subform, _
strFormName as String, Optional strWhere As String)
Dim rs As DAO.Recordset
If sfm.Form.Dirty Then 'Save first.
sfm.Form.Dirty = False
End If
sfm.SourceObject = strFormName
sfm.LinkMasterFields = vbNullString
sfm.LinkChildFields = vbNullString
If strWhere <> vbNullString Then
Set rs = sfm.FormRecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Oops: the record disappered."
Else
sfm.Form.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Function

 

[neil.mcdonald]

Hi Allen,

Thanks for taking the time to reply. I have to admit that the code
you've posted is way beyond my current capabilities in access (but I'm
keen to learn!).

I'm having trouble adapting the code - can you explain what "sfm"
refers to? I'm thinking it's the subform I'm trying to open, so for
example I've changed the code from

sfm.LinkMasterFields = vbNullString

to Me.Parent!SubForm.LinkMasterFields = vbNullString

This throws an error to tell me that the object doesn't exist, so I'm
either misunderstanding (highly likely) or my syntax is wrong (equally
likely).

If it helps, my main form is "Main", my search form "JobSearch" and my
details form "JobForm".

I appreciate any help you can offer.

Thanks again,

Neil

 

[Allen Browne]

Don't change anything in the LoadOtherSubform() function.

Do change Sub1 to the name of your subform on this line:
Call LoadOtherSubform(Me.Parent!Sub1, "JobForm", strWhere)

Subform is a VBA keyword, so if your subform control really is called
"Subform", it might be a good idea to put square brackets around the name:
Call LoadOtherSubform(Me.Parent![SubForm], "JobForm", strWhere)

Did you understand that the whole LoadOtherSubform() function was meant to
go into a standard module (i.e. clicking New on the Modules tab of the
database window, and pasting it in there)?

sfm is just the name of the argument that LoadOtherSubform() will use to
refer to this object. Its actual name is whatever was passed in. That's why
you don't change the sfm in the function. As long as the subform gets passed
correctly to the function, it can refer to it by using this variable.

 

[neil.mcdonald]

Hi Allen,

Cancel that last post! I have managed to get it to work by changing
the reference from Me.Parent! to Forms!Main!

One thing I would like to ask you is that my main form uses command
buttons to set the sourceobject for the subform. An example of the
code I'm using is:

DoCmd.Close acForm, "JobForm"
DoCmd.OpenForm "JobSearch", , , , , acHidden
Me.SubForm.SourceObject = "JobSearch"

Is this okay, or is there a better way to do it?

Thanks for your help,

Neil

 

[Allen Browne]

Hmm. I'm not sure I understand why you need the DoCmd.Close and
DoCmd.OpenForm hidden. Just setting the SourceObject should work.

You could also do it with:
Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")
if you wanted that function to take care of saving any edits in progress and
ensuring that Access does not mess up the LinkMasterFields and
LinkChildFields.

 

[neil.mcdonald]

Hi Allen,

I've changed the code back to the code you originally suggested, and
created a new module - it's working perfectly - Thanks!

I think I need to go and read up on modules, recordsets, and whole load
of other stuff!

Don't change anything in the LoadOtherSubform() function.

Do change Sub1 to the name of your subform on this line:
Call LoadOtherSubform(Me.Parent!Sub1, "JobForm", strWhere)

Subform is a VBA keyword, so if your subform control really is called
"Subform", it might be a good idea to put square brackets around the name:
Call LoadOtherSubform(Me.Parent![SubForm], "JobForm", strWhere)

Did you understand that the whole LoadOtherSubform() function was meant to
go into a standard module (i.e. clicking New on the Modules tab of the
database window, and pasting it in there)?

sfm is just the name of the argument that LoadOtherSubform() will use to
refer to this object. Its actual name is whatever was passed in. That's why
you don't change the sfm in the function. As long as the subform gets passed
correctly to the function, it can refer to it by using this variable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for taking the time to reply. I have to admit that the code
you've posted is way beyond my current capabilities in access (but I'm
keen to learn!).

I'm having trouble adapting the code - can you explain what "sfm"
refers to? I'm thinking it's the subform I'm trying to open, so for
example I've changed the code from

sfm.LinkMasterFields = vbNullString

to Me.Parent!SubForm.LinkMasterFields = vbNullString

This throws an error to tell me that the object doesn't exist, so I'm
either misunderstanding (highly likely) or my syntax is wrong (equally
likely).

If it helps, my main form is "Main", my search form "JobSearch" and my
details form "JobForm".

I appreciate any help you can offer.

Thanks again,

Neil

 

[neil.mcdonald]

Hi Allen,

I appreciate your help so far - hopefully I won't need to trouble you
again after this...

I tried using the

Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")

behind the command buttons, and it works great - with one exception.
One of the other subforms I have has three tabs. If I try to navigate
from that subform to another, I get an error message "Method 'Form' of
object '_SubForm' failed.


The debugger goes to the following line of code:

If sfm.Form.Dirty Then 'Save first.

I guess I need to modify the code so that it can deal with tabbed forms
as well as the others, but (surprise!) I'm not sure how...

If you can give me some pointers, I'll gladly try to write the code
myself.

Thanks,

Neil

Hmm. I'm not sure I understand why you need the DoCmd.Close and
DoCmd.OpenForm hidden. Just setting the SourceObject should work.

You could also do it with:
Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")
if you wanted that function to take care of saving any edits in progress and
ensuring that Access does not mess up the LinkMasterFields and
LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Cancel that last post! I have managed to get it to work by changing
the reference from Me.Parent! to Forms!Main!

One thing I would like to ask you is that my main form uses command
buttons to set the sourceobject for the subform. An example of the
code I'm using is:

DoCmd.Close acForm, "JobForm"
DoCmd.OpenForm "JobSearch", , , , , acHidden
Me.SubForm.SourceObject = "JobSearch"

Is this okay, or is there a better way to do it?

Thanks for your help,

Neil

 

[Allen Browne]

The tabs should not make any difference.

If the other form is unbound, then it will not have any Dirty property, so
the reference would generate an error. Perhaps use error handling for this
case. Or just remove that If block if no editing is allowed in any subforms.
Or test for the property, e.g.:
If HasProperty(sfm.Form, "Dirty")
after adding this function to the standard module as well:

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I appreciate your help so far - hopefully I won't need to trouble you
again after this...

I tried using the

Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")

behind the command buttons, and it works great - with one exception.
One of the other subforms I have has three tabs. If I try to navigate
from that subform to another, I get an error message "Method 'Form' of
object '_SubForm' failed.


The debugger goes to the following line of code:

If sfm.Form.Dirty Then 'Save first.

I guess I need to modify the code so that it can deal with tabbed forms
as well as the others, but (surprise!) I'm not sure how...

If you can give me some pointers, I'll gladly try to write the code
myself.

Thanks,

Neil

Hmm. I'm not sure I understand why you need the DoCmd.Close and
DoCmd.OpenForm hidden. Just setting the SourceObject should work.

You could also do it with:
Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")
if you wanted that function to take care of saving any edits in progress
and
ensuring that Access does not mess up the LinkMasterFields and
LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Cancel that last post! I have managed to get it to work by changing
the reference from Me.Parent! to Forms!Main!

One thing I would like to ask you is that my main form uses command
buttons to set the sourceobject for the subform. An example of the
code I'm using is:

DoCmd.Close acForm, "JobForm"
DoCmd.OpenForm "JobSearch", , , , , acHidden
Me.SubForm.SourceObject = "JobSearch"

Is this okay, or is there a better way to do it?

Thanks for your help,

Neil


(e-mail address removed) wrote:
Hi Allen,

Thanks for taking the time to reply. I have to admit that the code
you've posted is way beyond my current capabilities in access (but I'm
keen to learn!).

I'm having trouble adapting the code - can you explain what "sfm"
refers to? I'm thinking it's the subform I'm trying to open, so for
example I've changed the code from

sfm.LinkMasterFields = vbNullString

to Me.Parent!SubForm.LinkMasterFields = vbNullString

This throws an error to tell me that the object doesn't exist, so I'm
either misunderstanding (highly likely) or my syntax is wrong (equally
likely).

If it helps, my main form is "Main", my search form "JobSearch" and my
details form "JobForm".

I appreciate any help you can offer.

Thanks again,

Neil





Allen Browne wrote:
Okay, there's a bunch of stuff happening here.

Firstly, you cannot use OpenForm to load a subform control. You need
to
set
its SourceObject property. In doing so, you cannot pass a
WhereCondition, so
you will need to find the record, or appy a filter instead.

Another interesting aspect is that you are unloading the subform
while
its
module is still running, and replacing it with something else.
Access
will
probably let you get away with that, but it makes me a little
squeamish. I
would prefer to pass control to a generic function in a standard
module, so
there is no issue with fully unloading the subform's module.

This is just an aircode example of the kind of thing. I've assumed
your
subform control is named Sub1. (Subform is a keyword.) Access can
assign the
wrong stuff to LinkMasterFields/LinkChildFields, so we make sure
those
are
clear.

Private Sub JobID_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.JobID) Then
strWhere = "JobID = " & Me.JobID
Call LoadOtherSubform(Me.Parent!Sub1, "JobForm", strWhere)
End If
End Sub

Public Function LoadOtherSubform(sfm As Subform, _
strFormName as String, Optional strWhere As String)
Dim rs As DAO.Recordset
If sfm.Form.Dirty Then 'Save first.
sfm.Form.Dirty = False
End If
sfm.SourceObject = strFormName
sfm.LinkMasterFields = vbNullString
sfm.LinkChildFields = vbNullString
If strWhere <> vbNullString Then
Set rs = sfm.FormRecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Oops: the record disappered."
Else
sfm.Form.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Function


I have an unbound main form with a single subform. The
sourceobject
for the subform is changed to various forms using command buttons
on
the main form.

One of the subforms is a search, and another is a detailed view of
the
record.

What I would like to do is double-click the row on the search
form,
and
display the detailed form in it's place, but with the relevant
record.

The code I have so far is:

Dim JobNo As Integer
JobNo = Me.JobID.Value
DoCmd.OpenForm "JobForm", , , "JobId = " & JobNo, , acHidden
Forms!Main.SubForm.SourceObject = "JobForm"

The DoCmd statement seems to work if the form is opened normally
(i.e.
not hidden), but the code above opens the subform at the first
record.

 

[neil.mcdonald]

Hi Allen,

I'm obviously way out of my depth on this. At which point should I be
testing for the property? I've tried as an If statement before 'If
sfm.Form.Dirty Then' but I get "Method 'Form' of object '_SubForm'
failed"

I like the idea of using the standard module to make sure any edits are
saved, and it also makes the code so much cleaner. The only trouble is
I don't know how to make it work!

As you said, the tabbed subform is unbound. Does this mean that there
is no Form property to find? Do I need to test for the Dirty property
within the tab control?

Thanks,

Neil

The tabs should not make any difference.

If the other form is unbound, then it will not have any Dirty property, so
the reference would generate an error. Perhaps use error handling for this
case. Or just remove that If block if no editing is allowed in any subforms.
Or test for the property, e.g.:
If HasProperty(sfm.Form, "Dirty")
after adding this function to the standard module as well:

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I appreciate your help so far - hopefully I won't need to trouble you
again after this...

I tried using the

Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")

behind the command buttons, and it works great - with one exception.
One of the other subforms I have has three tabs. If I try to navigate
from that subform to another, I get an error message "Method 'Form' of
object '_SubForm' failed.


The debugger goes to the following line of code:

If sfm.Form.Dirty Then 'Save first.

I guess I need to modify the code so that it can deal with tabbed forms
as well as the others, but (surprise!) I'm not sure how...

If you can give me some pointers, I'll gladly try to write the code
myself.

Thanks,

Neil

Hmm. I'm not sure I understand why you need the DoCmd.Close and
DoCmd.OpenForm hidden. Just setting the SourceObject should work.

You could also do it with:
Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")
if you wanted that function to take care of saving any edits in progress
and
ensuring that Access does not mess up the LinkMasterFields and
LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


Cancel that last post! I have managed to get it to work by changing
the reference from Me.Parent! to Forms!Main!

One thing I would like to ask you is that my main form uses command
buttons to set the sourceobject for the subform. An example of the
code I'm using is:

DoCmd.Close acForm, "JobForm"
DoCmd.OpenForm "JobSearch", , , , , acHidden
Me.SubForm.SourceObject = "JobSearch"

Is this okay, or is there a better way to do it?

Thanks for your help,

Neil


(e-mail address removed) wrote:
Hi Allen,

Thanks for taking the time to reply. I have to admit that the code
you've posted is way beyond my current capabilities in access (but I'm
keen to learn!).

I'm having trouble adapting the code - can you explain what "sfm"
refers to? I'm thinking it's the subform I'm trying to open, so for
example I've changed the code from

sfm.LinkMasterFields = vbNullString

to Me.Parent!SubForm.LinkMasterFields = vbNullString

This throws an error to tell me that the object doesn't exist, so I'm
either misunderstanding (highly likely) or my syntax is wrong (equally
likely).

If it helps, my main form is "Main", my search form "JobSearch" and my
details form "JobForm".

I appreciate any help you can offer.

Thanks again,

Neil





Allen Browne wrote:
Okay, there's a bunch of stuff happening here.

Firstly, you cannot use OpenForm to load a subform control. You need
to
set
its SourceObject property. In doing so, you cannot pass a
WhereCondition, so
you will need to find the record, or appy a filter instead.

Another interesting aspect is that you are unloading the subform
while
its
module is still running, and replacing it with something else.
Access
will
probably let you get away with that, but it makes me a little
squeamish. I
would prefer to pass control to a generic function in a standard
module, so
there is no issue with fully unloading the subform's module.

This is just an aircode example of the kind of thing. I've assumed
your
subform control is named Sub1. (Subform is a keyword.) Access can
assign the
wrong stuff to LinkMasterFields/LinkChildFields, so we make sure
those
are
clear.

Private Sub JobID_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.JobID) Then
strWhere = "JobID = " & Me.JobID
Call LoadOtherSubform(Me.Parent!Sub1, "JobForm", strWhere)
End If
End Sub

Public Function LoadOtherSubform(sfm As Subform, _
strFormName as String, Optional strWhere As String)
Dim rs As DAO.Recordset
If sfm.Form.Dirty Then 'Save first.
sfm.Form.Dirty = False
End If
sfm.SourceObject = strFormName
sfm.LinkMasterFields = vbNullString
sfm.LinkChildFields = vbNullString
If strWhere <> vbNullString Then
Set rs = sfm.FormRecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Oops: the record disappered."
Else
sfm.Form.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Function


I have an unbound main form with a single subform. The
sourceobject
for the subform is changed to various forms using command buttons
on
the main form.

One of the subforms is a search, and another is a detailed view of
the
record.

What I would like to do is double-click the row on the search
form,
and
display the detailed form in it's place, but with the relevant
record.

The code I have so far is:

Dim JobNo As Integer
JobNo = Me.JobID.Value
DoCmd.OpenForm "JobForm", , , "JobId = " & JobNo, , acHidden
Forms!Main.SubForm.SourceObject = "JobForm"

The DoCmd statement seems to work if the form is opened normally
(i.e.
not hidden), but the code above opens the subform at the first
record.

 

[Allen Browne]

Ah, okay, it's not the Dirty property that it's complaining about, but the
Form property. In other words, it looks like this subform control does not
have any form loaded into it at present. It's as if you left the
SourceObject blank.

You could test whether the SourceObject is a zero-length string at the top
of the procedure with:
If sfm.SourceObject <> vbNullString Then
If sfm.Form.Dirty Then 'Save first.
sfm.Form.Dirty = False
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I'm obviously way out of my depth on this. At which point should I be
testing for the property? I've tried as an If statement before 'If
sfm.Form.Dirty Then' but I get "Method 'Form' of object '_SubForm'
failed"

I like the idea of using the standard module to make sure any edits are
saved, and it also makes the code so much cleaner. The only trouble is
I don't know how to make it work!

As you said, the tabbed subform is unbound. Does this mean that there
is no Form property to find? Do I need to test for the Dirty property
within the tab control?

The tabs should not make any difference.

If the other form is unbound, then it will not have any Dirty property,
so
the reference would generate an error. Perhaps use error handling for
this
case. Or just remove that If block if no editing is allowed in any
subforms.
Or test for the property, e.g.:
If HasProperty(sfm.Form, "Dirty")
after adding this function to the standard module as well:

Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I appreciate your help so far - hopefully I won't need to trouble you
again after this...

I tried using the

Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")

behind the command buttons, and it works great - with one exception.
One of the other subforms I have has three tabs. If I try to navigate
from that subform to another, I get an error message "Method 'Form' of
object '_SubForm' failed.


The debugger goes to the following line of code:

If sfm.Form.Dirty Then 'Save first.

I guess I need to modify the code so that it can deal with tabbed forms
as well as the others, but (surprise!) I'm not sure how...

If you can give me some pointers, I'll gladly try to write the code
myself.

Thanks,

Neil



Allen Browne wrote:
Hmm. I'm not sure I understand why you need the DoCmd.Close and
DoCmd.OpenForm hidden. Just setting the SourceObject should work.

You could also do it with:
Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")
if you wanted that function to take care of saving any edits in
progress
and
ensuring that Access does not mess up the LinkMasterFields and
LinkChildFields.


Cancel that last post! I have managed to get it to work by changing
the reference from Me.Parent! to Forms!Main!

One thing I would like to ask you is that my main form uses command
buttons to set the sourceobject for the subform. An example of the
code I'm using is:

DoCmd.Close acForm, "JobForm"
DoCmd.OpenForm "JobSearch", , , , , acHidden
Me.SubForm.SourceObject = "JobSearch"

(e-mail address removed) wrote:
Hi Allen,

Thanks for taking the time to reply. I have to admit that the code
you've posted is way beyond my current capabilities in access (but
I'm
keen to learn!).

I'm having trouble adapting the code - can you explain what "sfm"
refers to? I'm thinking it's the subform I'm trying to open, so
for
example I've changed the code from

sfm.LinkMasterFields = vbNullString

to Me.Parent!SubForm.LinkMasterFields = vbNullString

This throws an error to tell me that the object doesn't exist, so
I'm
either misunderstanding (highly likely) or my syntax is wrong
(equally
likely).

If it helps, my main form is "Main", my search form "JobSearch" and
my
details form "JobForm".


Allen Browne wrote:
Okay, there's a bunch of stuff happening here.

Firstly, you cannot use OpenForm to load a subform control. You
need
to
set
its SourceObject property. In doing so, you cannot pass a
WhereCondition, so
you will need to find the record, or appy a filter instead.

Another interesting aspect is that you are unloading the subform
while
its
module is still running, and replacing it with something else.
Access
will
probably let you get away with that, but it makes me a little
squeamish. I
would prefer to pass control to a generic function in a standard
module, so
there is no issue with fully unloading the subform's module.

This is just an aircode example of the kind of thing. I've
assumed
your
subform control is named Sub1. (Subform is a keyword.) Access can
assign the
wrong stuff to LinkMasterFields/LinkChildFields, so we make sure
those
are
clear.

Private Sub JobID_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.JobID) Then
strWhere = "JobID = " & Me.JobID
Call LoadOtherSubform(Me.Parent!Sub1, "JobForm",
strWhere)
End If
End Sub

Public Function LoadOtherSubform(sfm As Subform, _
strFormName as String, Optional strWhere As String)
Dim rs As DAO.Recordset
If sfm.Form.Dirty Then 'Save first.
sfm.Form.Dirty = False
End If
sfm.SourceObject = strFormName
sfm.LinkMasterFields = vbNullString
sfm.LinkChildFields = vbNullString
If strWhere <> vbNullString Then
Set rs = sfm.FormRecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Oops: the record disappered."
Else
sfm.Form.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Function


I have an unbound main form with a single subform. The
sourceobject
for the subform is changed to various forms using command
buttons
on
the main form.

One of the subforms is a search, and another is a detailed view
of
the
record.

What I would like to do is double-click the row on the search
form,
and
display the detailed form in it's place, but with the relevant
record.

The code I have so far is:

Dim JobNo As Integer
JobNo = Me.JobID.Value
DoCmd.OpenForm "JobForm", , , "JobId = " & JobNo, , acHidden
Forms!Main.SubForm.SourceObject = "JobForm"

The DoCmd statement seems to work if the form is opened
normally
(i.e.
not hidden), but the code above opens the subform at the first
record.

 

[neil.mcdonald]

I managed to get it working by using the following:


If HasProperty(sfm, "Form") = True Then
If sfm.Form.Dirty Then
sfm.Form.Dirty = False
End If
End If

Thanks again for all your help,

Neil

Ah, okay, it's not the Dirty property that it's complaining about, but the
Form property. In other words, it looks like this subform control does not
have any form loaded into it at present. It's as if you left the
SourceObject blank.

You could test whether the SourceObject is a zero-length string at the top
of the procedure with:
If sfm.SourceObject <> vbNullString Then
If sfm.Form.Dirty Then 'Save first.
sfm.Form.Dirty = False
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I'm obviously way out of my depth on this. At which point should I be
testing for the property? I've tried as an If statement before 'If
sfm.Form.Dirty Then' but I get "Method 'Form' of object '_SubForm'
failed"

I like the idea of using the standard module to make sure any edits are
saved, and it also makes the code so much cleaner. The only trouble is
I don't know how to make it work!

As you said, the tabbed subform is unbound. Does this mean that there
is no Form property to find? Do I need to test for the Dirty property
within the tab control?

The tabs should not make any difference.

If the other form is unbound, then it will not have any Dirty property,
so
the reference would generate an error. Perhaps use error handling for
this
case. Or just remove that If block if no editing is allowed in any
subforms.
Or test for the property, e.g.:
If HasProperty(sfm.Form, "Dirty")
after adding this function to the standard module as well:

Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I appreciate your help so far - hopefully I won't need to trouble you
again after this...

I tried using the

Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")

behind the command buttons, and it works great - with one exception.
One of the other subforms I have has three tabs. If I try to navigate
from that subform to another, I get an error message "Method 'Form' of
object '_SubForm' failed.


The debugger goes to the following line of code:

If sfm.Form.Dirty Then 'Save first.

I guess I need to modify the code so that it can deal with tabbed forms
as well as the others, but (surprise!) I'm not sure how...

If you can give me some pointers, I'll gladly try to write the code
myself.

Thanks,

Neil



Allen Browne wrote:
Hmm. I'm not sure I understand why you need the DoCmd.Close and
DoCmd.OpenForm hidden. Just setting the SourceObject should work.

You could also do it with:
Call LoadOtherSubform(Forms!Main![SubForm], "JobSearch")
if you wanted that function to take care of saving any edits in
progress
and
ensuring that Access does not mess up the LinkMasterFields and
LinkChildFields.


Cancel that last post! I have managed to get it to work by changing
the reference from Me.Parent! to Forms!Main!

One thing I would like to ask you is that my main form uses command
buttons to set the sourceobject for the subform. An example of the
code I'm using is:

DoCmd.Close acForm, "JobForm"
DoCmd.OpenForm "JobSearch", , , , , acHidden
Me.SubForm.SourceObject = "JobSearch"

(e-mail address removed) wrote:
Hi Allen,

Thanks for taking the time to reply. I have to admit that the code
you've posted is way beyond my current capabilities in access (but
I'm
keen to learn!).

I'm having trouble adapting the code - can you explain what "sfm"
refers to? I'm thinking it's the subform I'm trying to open, so
for
example I've changed the code from

sfm.LinkMasterFields = vbNullString

to Me.Parent!SubForm.LinkMasterFields = vbNullString

This throws an error to tell me that the object doesn't exist, so
I'm
either misunderstanding (highly likely) or my syntax is wrong
(equally
likely).

If it helps, my main form is "Main", my search form "JobSearch" and
my
details form "JobForm".


Allen Browne wrote:
Okay, there's a bunch of stuff happening here.

Firstly, you cannot use OpenForm to load a subform control. You
need
to
set
its SourceObject property. In doing so, you cannot pass a
WhereCondition, so
you will need to find the record, or appy a filter instead.

Another interesting aspect is that you are unloading the subform
while
its
module is still running, and replacing it with something else.
Access
will
probably let you get away with that, but it makes me a little
squeamish. I
would prefer to pass control to a generic function in a standard
module, so
there is no issue with fully unloading the subform's module.

This is just an aircode example of the kind of thing. I've
assumed
your
subform control is named Sub1. (Subform is a keyword.) Access can
assign the
wrong stuff to LinkMasterFields/LinkChildFields, so we make sure
those
are
clear.

Private Sub JobID_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.JobID) Then
strWhere = "JobID = " & Me.JobID
Call LoadOtherSubform(Me.Parent!Sub1, "JobForm",
strWhere)
End If
End Sub

Public Function LoadOtherSubform(sfm As Subform, _
strFormName as String, Optional strWhere As String)
Dim rs As DAO.Recordset
If sfm.Form.Dirty Then 'Save first.
sfm.Form.Dirty = False
End If
sfm.SourceObject = strFormName
sfm.LinkMasterFields = vbNullString
sfm.LinkChildFields = vbNullString
If strWhere <> vbNullString Then
Set rs = sfm.FormRecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Oops: the record disappered."
Else
sfm.Form.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Function


I have an unbound main form with a single subform. The
sourceobject
for the subform is changed to various forms using command
buttons
on
the main form.

One of the subforms is a search, and another is a detailed view
of
the
record.

What I would like to do is double-click the row on the search
form,
and
display the detailed form in it's place, but with the relevant
record.

The code I have so far is:

Dim JobNo As Integer
JobNo = Me.JobID.Value
DoCmd.OpenForm "JobForm", , , "JobId = " & JobNo, , acHidden
Forms!Main.SubForm.SourceObject = "JobForm"

The DoCmd statement seems to work if the form is opened
normally
(i.e.
not hidden), but the code above opens the subform at the first
record.