to sync a list sfrm & detail sfrm

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


0
Utf
4/7/2007 1:46:04 AM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
639 Views

Similar Articles

[PageSpeed] 46

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
   *



DymondJack wrote:
> 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
> 
> 
0
strive4peace
4/7/2007 2:45:38 AM
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

"strive4peace" wrote:

> 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
>    *
> 
> 
> 
> DymondJack wrote:
> > 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
> > 
> > 
> 
0
Utf
4/7/2007 12:32:01 PM
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
   *



Dymondjack wrote:
> 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
> 
> "strive4peace" wrote:
> 
>> 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
>>    *
>>
>>
>>
>> DymondJack wrote:
>>> 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
>>>
>>>
0
strive4peace
4/7/2007 2:08:58 PM
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
   *



strive4peace wrote:
> 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
>   *
> 
> 
> 
> Dymondjack wrote:
>> 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
>>
>> "strive4peace" wrote:
>>
>>> 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
>>>    *
>>>
>>>
>>>
>>> DymondJack wrote:
>>>> 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
>>>>
>>>>
0
strive4peace
4/7/2007 2:30:32 PM
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!

"strive4peace" wrote:

> 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
>    *
> 
> 
> 
> strive4peace wrote:
> > 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
> >   *
> > 
> > 
> > 
> > Dymondjack wrote:
> >> 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
> >>
> >> "strive4peace" wrote:
> >>
> >>> 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
> >>>    *
> >>>
> >>>
> >>>
> >>> DymondJack wrote:
> >>>> 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
> >>>>
> >>>>
> 
0
Utf
4/9/2007 10:06:00 AM
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
   *



Dymondjack wrote:
> 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!
> 
> "strive4peace" wrote:
> 
>> 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
>>    *
>>
>>
>>
>> strive4peace wrote:
>>> 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
>>>   *
>>>
>>>
>>>
>>> Dymondjack wrote:
>>>> 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
>>>>
>>>> "strive4peace" wrote:
>>>>
>>>>> 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
>>>>>    *
>>>>>
>>>>>
>>>>>
>>>>> DymondJack wrote:
>>>>>> 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
>>>>>>
>>>>>>
0
strive4peace
4/9/2007 2:27:49 PM
Reply:

Similar Artilces:

Filtered List
I have a filtered list I wish to copy to another column. For example (much simplified): A1 1 a2 2 a3 1 a4 2 a5 1 I want to filter out the 2 values leaving the 1 values then copy the resulting filtered list into cells c1, c3 and c5. I have tried normal copy and paste - this copies the results into the hidden cells. I have selected visible cells using Edit Goto and tried pasting this and tried pasting values ...to no avail. Is this possible without vba? My current solution is to loop through the A column values and copy them to C one at at time - but it is not seem very efficient....

How do I use a Word merge list in Publisher
I have compiled a large address list for use with Word mail merges. Publisher does not recognize the .doc format in a merge address list - even though both are Microsoft products that do roughly the same thing. Can I use my Word list with Publisher? Hi microeng (microeng@discussions.microsoft.com), in the newsgroups you posted: || I have compiled a large address list for use with Word mail merges. || Publisher does not recognize the .doc format in a merge address list || - even though both are Microsoft products that do roughly the same || thing. Can I use my Word list with Publisher? Sa...

Drop down list font
When I create a drop down list in a cell using data validation, the fon in the list is very small. Once I make a choice from the list, the fon is formatted correctly in the selected cell but the list itself i barely readable. Is there any way to increase the font size in th list? Thank -- Sibermut ----------------------------------------------------------------------- Sibermute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1609 View this thread: http://www.excelforum.com/showthread.php?threadid=27543 The font size cannot be changed. See Debra Dalgleish...

Outlook Creating Sync Problem Folders.....HELP
When Outlook 2003 perform the send/receive action. My mail comes in/out, however a box pops up showing there was an error. This error then goes into a sync folder as an email. I DID NOT create this folder. If I delete the sync folder, it re-creates itself. Any ideas on how to stop this madness? The sync folder is a system folder that Outlook needs and uses. Would probably be better to address the error, rather than merely trying to hide the reporting mechanism, no? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforl...

Payee List Deletions
Am running Money 2006 standard on a WinXP Home Edition series. Is there a procedure for deleting a group of payee from the payee list without having to confirm each individual delete? Nope. (Beyond File|New that is.) "Hans36" <RWH36@comcast.net> wrote in message news:%23Sg6irpLHHA.4460@TK2MSFTNGP03.phx.gbl... > Am running Money 2006 standard on a WinXP Home Edition series. Is there a > procedure for deleting a group of payee from the payee list without having > to confirm each individual delete? ...

Query-based Distribution Lists not showing up in GAL
I've created several new query-based distribution lists (replacing standard DL's), and none of them are showing up in the GAL. Any thoughts on this? TIA. Anyone? I've got the same problem. Did you ever find a solution? Yes. Open Exchange System Manager, locate to Recipients\All Global Address Lists, right click Default Global Address List and click Properties. On General tab, click Preview… Is this query-based distribution group shown in the preview window? "ren14" wrote: > I've got the same problem. Did you ever find a solution? "ren14&q...

POS Sync After Every Z Report
Hi All~ I'm wondering if it's the expected behavior to have a POS on another station sync every time a Z report is ran? Should the database sync every time? I thought only when the POS was in offline status. I don't believe the POS is offline everyday, but everyday when we take a Z report, the database syncs with the POS with the database. Is this expected? Hi Jerry, It syncs every z because there may be changes to the items, taxes and other databases. -- * "Jerry Penna" <jerrypenna@msn.com> wrote in message news:3ac97809.0411161303.32f35868@posting.googl...

blank Distribution List in Global Address List
Good Morning, Exchange 5.5 SP 4 Outlook Client 2003 SP2 Upto last week, our distribution lists in global address list shown the list owners and their members. This week, I have add a member to the permission tab. Upon refresh the Exchange, now distribution lists all shown blank list owners and members. I went back to remove that member from the permission tab. But the problem still exist. And I'm getting report that some people able to see these distribution list owners and members while other still can't. Any suggestions are appreciated. Thank. ...

Drop Down List, AutoPostBack
I swear if I have to 'relearn' how AutoPostBack, EnableViewState and Me.IsPostBack works, or should work, I'm going to literally tear my hair out. I've got a pretty simple form, couple of text boxes, a check box, a drop down list and a button. The drop down list is populated via code, and that works fine. The TextValue is set to a one field returned by a stored procedure, as is the ValueField set to an associated numeric value. When I hit the command button to submit and run my code, the value selected in the drop down list changes. The values listed in the drop ...

Distribution List Question #6
I have set up a distribution list in Exchange 2003. The list contain user1, user2, and user3. When user1 responds to the group, I would like it to go to user2 and user3, but user1 should not receive a copy of the message. Is this possible with Exchange? If not, is it possible any other way without buying some more expensive software? That is the way groups work. Why is this such an issue out of interest? Oliver <Matthew.K.Swanson@gmail.com> wrote in message news:1168895050.790390.30570@v45g2000cwv.googlegroups.com... >I have set up a distribution list in Exchange 2003. The l...

Expand Distribution Lists
Is there a way to automatically expand distribution lists once they are in the To: box of an email in Outlook 2003, Exchange 2003. That way they names of the people are showing when a user selects the distribution list from the Global Address book. thank you -- Stacy Justice Aculab USA Inc. Click the + sign next to the DL in the To box. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Stacy Justice" <StacyJustice@discussions.microsoft.c...

Large List Sorting
Hello, All! Have a problem. I'm developing an application which reading a large database file (DBF) and displaying it in the list control. Because of the size of the file (>15 MB, later it will be increased) I'm using a virtual list (owner data). But now I need to sort data in the list. AFAIK to perfirm this I should read the whole file. In this case my program starts several minutes!!! It doesn't have to be like this [(C) Pink Floyd]... Is there any methods to load a large amount of data as quickly as possible? Or I just should humble, 'cause sorting of large amount of d...

Remote sync
Is there a software out there that I can use to either remote sync my Outlook contact and calendar from my office to my home pc, or remotely update the calendar to my home pc without connecting to an exchange server. I have a standalone desktop running xp and connecting to a cable modem at home and I want to update my outlook calendar and contact from the office(T1 line). I will buy any device that will do the job. Ipaq, Palm or AT&T blackberry 7210 or anything works. If there is no software to do this, is it possible to do it with any other solution. Any suggestion will be very greatly a...

OneNote sync no linger syncs
I have oneNote 2007 installed on Laptop with Windows Vista. It has been syncing with my Pocket PC but it no longer does. Updates on either device are not carried to the other. I have uninstalled OneNote on the Pocket PC with no effect. What should I do? Thanks ....and I assume you REinstalled OneNote on the Pocket PC? What OS is the Pocket PC running? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.htm Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazo...

outline numbered list not auto-numbering
I have 2003 version of Word at work. I am trying to create an outline numbered list with 3 levels. When I hit the enter button after typing the text for an item, word does not seem to recognize that it should continue the list. The next line just has normal formatting, and I'm forced to use the format painter brush to copy a previous item from the list, and then Word seems to recognize it as part of the list. I have verified the "Apply Automatic Numbered Lists" is checked in the Tools/Autocorrect Options/Autoformat as you type menu. An observation about Word ...

Outlook not syncing correctly
Hi Friday I had a nasty disk failure of a server which services a small network, it has only about 5 users. It's the only server in the AD, running as DC ofcourse, W2003 Sp1, Exchange 2003 Sp2. The disk was dead as dead can be. So the disaster recovery plan kicks in... yeah right. Last good tape turned out to be from august 15th 2005. Don't get me started on why. I put in a new disk, installed W2003 SP1 eval edition (only cd arround), restored from tape the c-drive, system state and exchange. After a few reboots solving minor issues about WMI etc, I started checking my mailboxes. Wit...

Smart List should contain same filter options as Excel
Many of us are familiar with the very flexible data filtering options that are contained in Microsoft Excel. Smartlist only contains a subset of those options that are somewhat limiting. For instance, if you are search in Item Descriptions, you cannot choose to return results that "does not contain" text. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the sugge...

List Validation #2
Hello Everybody, I have applied list validations to cell, say C4. The list contains two values : CY & CFS But when I copy & paste special values other than the list (sa WAREHOUSE from cell B4), excel accepts the same. Can anyone help me to restrict the cell updation only as per list. Thnks & Rgds, Rajendra -- Message posted from http://www.ExcelForum.com Hi with Data - Validation you can't prevent copy/paste operations. This would require VBA >-----Original Message----- >Hello Everybody, > >I have applied list validations to cell, say C4. > >The lis...

sync
I have the hebrew calendar enabled as the alternate calendar in my outlook2003. I have the latest version of Active Sync. I am able to sync the normal english calendar but i cannot sync the hebrew calendar. Can some body guide me? Thanks AN AN wrote: > I have the hebrew calendar enabled as the alternate calendar in my > outlook2003. I have the latest version of Active Sync. I am able to > sync the normal english calendar but i cannot sync the hebrew > calendar. Can some body guide me? > > Thanks > > AN Hi - might want to post in a PocketPC / Activesync group f...

Custom List View Filter on Dates
I appolgize if this is a second post of this question, but I do not think that my first one made it to this group. I have created a custom list in MOSS2007. The list has several dates, each indicating a stage in the process. I wish to make views that allow me to see those items that have Date 1 filled in but do not have Date 2 filled in. I have tried to use [Empty], [Blank], 0, and less than 01/01/2009. None of these work. Is there a special word for filtering dates? Thanks! GregDC ...

how to sort list, bolded items from no bolded?
I'm wondering how to sort a list that has some items bolded and some not. I need to know how to sort bolded items from non-bolded for a chart or table comparison. Customers that inquired about services are all listed. Then if the customers funds/buys a service, we bold their name, etc. I want to know how to sort for comparison sake. Thank you for any information. Formatting (bold, color, etc.) should not be used to store information. It should be used for presentation only. Excel isn't set up to operate on it. It can be done with macros, if you want to do it that way. Read ...

Bills Summary out of Sync with MSN Bill Pay
I am currently using Money 2006 Deluxe. I am currently using MSN Bill Pay as my online bill pay service. I have noticed over the last few months of using Money 2006 (and this happened with 2005, though not as frequently) that the Bills Summary gets out of sync with my MSN Bill Pay. Basically, it will show bills due in February of 2006 when they are due in October 2005. It throws off the cash flow since the cash flow uses the data from the Bills Summary. I can check the settings in MSN Bill Pay and the payment dates are correct. The only way to "fix" this problem is to re-...

Problem Extracting Data From A Generated List
Having a problem trying to use MATCH, INDEX and SUMPRODUCT to produce a list from some data. In row 1, I have Business Process Numbers (BP's) in this format: 1.01 44.01 46.02 46.03 46.06 which start at 1.01 and at the moment go up to 60.27. There are currently 146 BP's. The column under each BP has a list of Attribute numbers from the range of 1 to 1300 and this may very well expand in the near future. Due to the amount of Attributes, these obviously have to be placed Vertically. The attributes are arranged in numerical order in each column but there may be duplicates ...

distribution lists and email
Can anyone tell me how I send an email to a distribution list in outlook 2002 - the help instructions seem to be inaccurate. they say to chose item from the insert menu - however that is not a choice that appears on the insert menu?? Thanks Rory Create the distribution list. When you address the new message, click on the To: field, browse to the DL name, and you're good to go. Note - everyone will be able to see all the other DL members, so a better option is to put the DL in the BCC field (blind carbon copy) and put your own address in the TO field. Most people will appreciate i...

Automate sync deactivating
We've got about 40,000 contacts in our CRM 3.0 implementation with about 30 owners. We would like to disable the syncing of contacts and the removal of the CRM-sourced contacts in Outlook in an automated fashion. Is there a way to do this? We could go around to all of the workstations, but would prefer a script or something to handle it. I tried playing around with the SyncContact registry setting in the HKCU\Software\Microsoft\MSCRMClient key, but it didn't seem to have an effect. Also, to prevent these from happening in the future, can the installs be set to default with ...