to sync a list sfrm & detail sfrm

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello and thanks in advance for any help.

frmCompanies
sfrCompContactDetail
sfrCompContactList

frm Companies contains both subforms, not nested

When the Last Name field of the List sub is double clicked, I would like the
record in the Details sub to change to that record. Here's my code:

***
Dim lngCurrent As Long
Dim strSQL As String

lngCurrent = Me.ContactID.Value

strSQL = "SELECT * FROM tblContacts "
strSQL = strSQL & "WHERE tblContacts!ContactID = " & lngCurrent

Dim objfrmList As AccessObject
Set objfrmList = CurrentProject.AllForms("sfrCompContactDetail")

objfrmList.Recordset = strSQL
***

When I run this, I get Error 438, Object doesn't support this method or event.

It debugs to the objfrmList.Recordset = strSQL line
I've also tried objfrmList.Recordset (strSQL) and
Forms.sfrCompContactDetail.Recordsource = strSQL
and a few others

In all cases that particular line throws the same error.

Being self taught, I'm assuming I either missed something very simple, or I
have the entire wrong concept on how to mainuplate the record.

Any advice greatly appreciated,
Jack
 
Hello Jack,

show all the records in the sfrCompContactList recordset and just move
to the first matching record using the Bookmark property

for the double-click event of the control in sfrCompContactList

'~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.newrecord then
msgbox "You are not on contact record",,"Cannot show Detail"
exit sub
end if

with me.parent.sfrCompContactDetail.form
.RecordsetClone.FindFirst "ContactID = " & Me.ContactID
'if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
end with
'~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Hi Crystal,

Thanks for your suggestion, though I could not get it to work. With the
code entered as you had written it, I get 'Error 2465, Application-defined or
object-defined error' on the with me.parent.sfrCompContactDetail.form line.

I tried with forms.sfrCompContactDetail.form also, but then I get the Error
438, object doesnt support this poroperty or method again. Do you know of
something else that I might be missing?

Thanks,
Jack
 
TestIfSubform
---


Hello Jack,

you will only be able to test this when it is used as a subform, not on
its own...

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number > 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and this at the top of the code:

'~~~~~~~~~~~~~~~~~~~~
If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
ps

also check the NAME property of each subform -- that is not the same as
the SourceObject...

for better understanding of Properties, download and read this:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Ok, I finally got it. The problem was that I had control name for the
subform different than the name of the form itself, and apparently it is the
control that needs to be addressed. Thanks for the help!
 
you're welcome, Jack ;) happy to help

yes, you are right -- you must reference the control that the subform is
in -- one reason that Names are so important is that is what the code
uses ;)

*** Subform Control vs Subform: The Difference between a Subform Control
and a Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

~~~~ turn on Properties window ~~~~

When you are in the design view, turn on/off the Properties window -->

1. from menu: View, Properties
OR
2. right-click and choose Properties from the shortcut menu

and then click on various objects. The properties window changes as you
change what is selected. If you have multiple objects selected, the
values for the properties they have in common will be displayed



Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top