Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Houston, Texas

    Unanswered: List item selection to open another form....

    I have a table with 3 primary key fields. This table called tbl_groups {primary fields are ga_number(text),PYE(date),PlanNum(text)}.

    I have a query based form (called frm_search_adv) where the form lists all the records in a List Box called LstResult.

    I want to be able to highlight a record and pull that record in another form called frm_groups_adv but remember ga_number, pye and planNum has to match the record.

    I try to use itemsselected but don't know how to use this command or write the code. Any detail help will be very very appreciated. I have been trying to do this for last 3 weeks.


  2. #2
    Join Date
    Apr 2002
    Hi Dura,

    As a starter, suggest you have a look at the Access Help at these topics:
    ItemsSelected and ItemData
    They might give you the key to accessing items in a list box.

    I have used code like this to open a specific record in a next form, based on the selection in a list box:

    Dim frm As Form, ctl As Control
    Dim varElement As Variant, inti As Integer

    Dim stDocName As String
    Dim stLinkCriteria As String

    Dim SQLTekst As Variant

    SQLTekst = ""

    Set frm = Forms!MySelectionForm 'This is my form with the list box
    Set ctl = frm!LstKlant 'This is my listbox
    inti = 1
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "Make your choice!", vbCritical, "Warning!"
    Exit Sub
    End If

    If ctl.ItemsSelected.Count > 1 Then
    MsgBox "You've selected " & ctl.ItemsSelected.Count & " items. " , vbInformation
    End If

    'List box is set to multiple choice, so walk through all selected items:

    For Each varElement In ctl.ItemsSelected
    If inti > 1 Then
    SQLTekst = SQLTekst & " OR "
    End If
    SQLTekst = SQLTekst & "[MyKeyValueID]=" & "'" & ctl.ItemData(varElement) & "'"
    inti = inti + 1
    Next varElement

    Rem MsgBox SQLTekst

    stDocName = "Frm10_MyDetailForm"
    stLinkCriteria = SQLTekst
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    hth :-)

  3. #3
    Join Date
    Dec 2002
    Houston, Texas

    Getting error on "DoCmd.OpenForm stDocName, , , stLinkCriteria"

    I wonder why am I getting error on the last line. Which basically opens up the target form. Anyhelp will be appreciated.

    Your help has been appreciated very very much.

    Thanks. Dura

  4. #4
    Join Date
    Apr 2002

    Re: Getting error on "DoCmd.OpenForm stDocName, , , stLinkCriteria"


    Check the syntax of SQLTekst by removing the Rem.
    Does the message box show like decent SQL?

    Since you built SQLTekst with single and double quotes, things go easily wrong here. May also depend on the type of variables you pass in to the expression, whether they are text or numeric.
    Just playing around with it will help you.

    If you remove the , , , stLinkCriteria, your form opens normally I expect?
    If so, then you are sure it is something in stLinkCriteria, which is the same as SQLTekst.


  5. #5
    Join Date
    Dec 2002
    Houston, Texas

    Talking Solved....

    I think I found the problem. I was trying to pull a record by the autonumber field called Ga_Record_ID. And it was not working. So, I created another filed (text) called Ga_Record_ID_2 which basically copies ga_record_id field. And everything works fine.

    I want to especially thank to Marion who helped me resolve this issue. Thanks Marion. Really appreciated your generous help.

    Thanks Again

Posting Permissions

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