Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding a record from a filter (2000)

    Hello, I have a situation where a form opens to show a truncated list of filtered records. This filter is run from a macro on the load property. What I need to be able to do is by clicking on a given record in the list of filtered records is to bring the full record into another form that shows full details of that record. (Hope this makes sense).

    Kind Regards Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding a record from a filter (2000)

    If you can provide a unique identifier for the selected record, you can specify a Where-condition on opening the other form. The exact implementation depends on the situation; it's not clear to me whether the "truncated list" you mention means a list box or a continuous form, and I don't know of course if the unique identier would be numeric or text.

    To limit the other form to a specific record, open it as follows:

    Dim strWhereCondition As String
    strWhereCondition = ... ' depends on situation
    DoCmd.OpenForm "frmOtherForm", , , strWhereCondition

    where frmOtherForm is the name of the form that will display full details. Perhaps you can take it from here; if you want more help, please give some details (see my remarks above.)

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding a record from a filter (2000)

    Thanks Hans. The 'truncated list' is say a continous form with e.g. 'ID 1 Jones', what is needed is then to be able to click on 'id 1 Jones' and then full detials for Mrs Jones show in lets say in a customer form. Hope this clarifies?

    Thanks again Darren.

  4. #4
    Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding a record from a filter (2000)

    I did something like this before. To open the new form using the contents of a text box as the link criteria, add something like this to the text box

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding a record from a filter (2000)

    If you have a numeric ID field in the continuous form, you could use<pre>strWhereCondition ="[ID] = " & Me.[ID]</pre>

    If the ID field is text, tyou must surround its value in single or double quotes, as in Loren Ann's reply:<pre>strWhereCondition ="[ID] = " & "'" & Me.[ID] & "'"</pre>

    where ID is the name of the field that uniquely identifies the selected record in both cases.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding a record from a filter (2000)

    Hi again Hans/Loren Ann, thought I had it but not quite there. Do forgive, I am a novice amongst novices!

    The situation is; I have frmevents and frmdue. Events is the form with lots of detail per record and has a unique number ID, thus named ID. Due is a form with a list box triggered by the filter and just shows events due today and again the unqiue ID for the event. What I wish to do is click on an entry in the list box (IDlist) on frmdue and then the full event record appears in frmevents.

    This is my attempt which keep falling down;

    Private Sub IDLIst_Click()

    sDocName = "frmevents"
    sLinkCriteria = "[id]=" & "'" & frmdue![IDLIst] & "'"
    DoCmd.OpenForm sDocName, , , sLinkCriteria

    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding a record from a filter (2000)

    1. You write that ID is a number. You shouldn't surround the value in quotes, that is for text only.
    2. You must not use frmDue! - either use the full Forms!frmDue! or just Me! (or Me.)
    3. If the ID field is the first field in the row source of the list box (and if the Bound Column is 1), use this:
    sLinkCriteria = "[id]=" & Me![IDLIst]
    4. If the ID field is the second field in the row source of the list box, use this:
    sLinkCriteria = "[id]=" & Me![IDLIst].Column(1)
    (the column index is zero-based, so the first column has index 0, the second one has index 1, etc.)

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding a record from a filter (2000)

    Hans, that's on the money, thank you very much.

    Darren.

Posting Permissions

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