Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003

    Unanswered: how to reference subform's recordset field value

    How can I reference a value in a sub-form recordset, if that value is not displayed on the subform itself?

    My subform recordsource is
    SELECT contact_no, case_no, ssn, name, dob FROM contacts

    The parent and subform are linked through case_no

    On the subform (datasheet view), only ssn, name, and dob are shown.

    Essentially, I'm trying to allow the users to double-click on a line in the subform and have a separate normal-view form open showing that contact. For this I use contact_no, the field not shown on the subform but which exists in the recordset. It works the first time you double-click, and the window opens fine, but when you try it again a second time it doesn't work.

    Here's the line that doesn't work (it's in the subform code module):

    contact_id_long = me.recordsetclone.contact_id

    Basically, my contact_id is my handle, but I don't want to show it on the subform, because it's not meaningful to the users. I can't use SS# for my handle.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    Sydney Australia
    If this any help this is what I do with some of my subforms.

    I simply place an open form macro on one of the displayed fields on the subform and the form that opens displays all the data for that record.

    Say your new form is NewForm then Where is

    [IDNumber or whatever] =[Forms]![MainForm]![Subform]![IDNumber]


  3. #3
    Join Date
    Mar 2003
    Thanks, you're wonderful! One last thing--why does it work your way every time I go back into the subform from the main form, and my way work only the first time?

    from the subform code module:

    contact_id_long = Forms("MainForm").SubForm!contact_id

    MY WAY:
    contact_id_long = Me.recordsetclone.contact_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts