Results 1 to 4 of 4
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    List Box DoubleClick

    I have designed a search Form with the following codes:

    Option Compare Database
    Option Explicit


    Dim strRowsource As String

    Private Sub Form_Open(Cancel As Integer)
    Me.Caption = "SEARCH FORM"

    strRowsource = "SELECT AddressQry.ID, AddressQry.NAME, AddressQry.CITY, AddressQry.STATE, AddressQry.TYPE " & _
    "FROM AddressQry " & _
    "ORDER BY AddressQry.Name"

    List1.RowSource = strRowsource
    End Sub

    Private Sub List1_DblClick(Cancel As Integer)
    If Me.List1.Column(4) = "[Seed Party]" Then
    DoCmd.OpenForm "frmSdPartyCorrection", , , "[seedPartyID]=" & List1.Column(0)
    ElseIf Me.List1.Column(4) = "[Factory]" Then
    DoCmd.OpenForm "frmFactoryCorrection", , , "[factoryID]=" & List1.Column(0)
    End If
    End Sub

    Private Sub txtSearch_Change()
    If Frame1 = 1 Then
    strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
    "FROM AddressQry " & _
    "WHERE[Name] Like'*" & Me.txtSearch.Text & "*'" & _
    "ORDER BY AddressQry.Name"
    ElseIf Frame1 = 2 Then
    strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
    "FROM AddressQry " & _
    "WHERE[City] Like'" & Me.txtSearch.Text & "*'" & _
    "ORDER BY AddressQry.Name"
    ElseIf Frame1 = 3 Then
    strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
    "FROM AddressQry " & _
    "WHERE[State] Like'" & Me.txtSearch.Text & "*'" & _
    "ORDER BY AddressQry.Name"
    ElseIf Frame1 = 4 Then
    strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
    "FROM AddressQry " & _
    "WHERE[Type] Like'" & Me.txtSearch.Text & "*'" & _
    "ORDER BY AddressQry.Name"
    End If

    List1.RowSource = strRowsource
    End Sub

    The List Box loads correctly when the Form opens. The txtSearch_Change Function also works properly. However, on double clicking a particular item in the List, the relevant Form should open based on the 5th item in the column ie, Column(4). I will be either "Seed Party" or "Factory". The Double click event is not generating the forms.The SQL is from the Union Qry based on two different tables.
    Please help.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    strRowsource = "SELECT AddressQry.ID, AddressQry.NAME, AddressQry.CITY, AddressQry.STATE, AddressQry.TYPE " & _
    Be aware: "Name", "State" and "Type" are reserved words and shouldn't be use as object names.

    List of reserved words: http://allenbrowne.com/AppIssueBadWord.html


    Are the values in the field "Type" really "[Seed Party]" and "[Factory]"?? (with the brackets??)
    I would think the values would be "Seed Party" and "Factory". (no brackets??)

    try
    Code:
    Private Sub List1_DblClick(Cancel As Integer)
      '    Msgbox "The Type is " & Me.List1.Column(4)
       If Me.List1.Column(4) = "Seed Party" Then
          DoCmd.OpenForm "frmSdPartyCorrection", , , "[seedPartyID]=" & List1.Column(0)
       ElseIf Me.List1.Column(4) = "Factory" Then
          DoCmd.OpenForm "frmFactoryCorrection", , , "[factoryID]=" & List1.Column(0)
       End If
    End Sub

  3. #3
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thank you ssanfu, I removed the square brackets and it worked. Actually they are data entered and not field names.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Excellent!

    Good luck with your project....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-22-2015, 12:30 PM
  2. Replies: 1
    Last Post: 04-21-2014, 08:00 AM
  3. Replies: 15
    Last Post: 11-20-2013, 04:30 PM
  4. Replies: 2
    Last Post: 04-05-2013, 03:42 PM
  5. Replies: 5
    Last Post: 03-03-2010, 12:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums