Duble click on list box to open form and subform

[mafhobb]

I have the following code attached to a form:

Private Sub Go_To_Project_Click()
On Error GoTo Err_Go_To_Project_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Project Main"
    stLinkCriteria = "[R&D ID#]=" & Me![lstsearch]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Go_To_Project_Click:
    Exit Sub
Err_Go_To_Project_Click:
   MsgBox Err.Description
    Resume Exit_Go_To_Project_Click
End Sub

Private Sub txtsearch_AfterUpdate()
Me.lstsearch.RowSource = "Select [R&D ID#], [SKU#], [Project Name], [Construction level], [Manufacturer], [Hobbico Status], [R&D Work By], [Product Manager], [Desktopper]" & _
                        "From [Project Main]" & _
                        "Where [Project Name] like '*" & Me.txtsearch & "*'" & _
                        "OR [SKU#] like '*" & Me.txtsearch & "*'" & _
                        "OR [R&D Work By] like '*" & Me.txtsearch & "*'" & _
                        "OR [Product Manager] like '*" & Me.txtsearch & "*'" & _
                        "OR [Desktopper] like '*" & Me.txtsearch & "*'" & _
                        "OR [R&D ID#] like '*" & Me.txtsearch & "*'"
Me.lstsearch.Requery
End Sub


Private Sub Exit_Search_Click()
On Error GoTo Err_Exit_Search_Click
    DoCmd.Close
Exit_Exit_Search_Click:
    Exit Sub
Err_Exit_Search_Click:
    MsgBox Err.Description
    Resume Exit_Exit_Search_Click
End Sub

Basically a text string is entered in the txtbox and a list of matching records shows up in lstsearch box. One selects the desired record in the lstsearch and clicks on the "Go to project" button and the appropriate form (aka "Project main") and subform (aka Project History) open up with the desired record and record history.

What would I need to add to lstsearch so the users can double-click on the record to open it instead of selecting it and clicking on the "Go to project" button?

Thanks

mafhobb

 

[ajetrumpet]

does this help...???

 

[boblarson]

What would I need to add to lstsearch so the users can double-click on the record to open it instead of selecting it and clicking on the "Go to project" button?

Not to sound too flippant - but wouldn't it be as easy as moving the code that is on the button that you are clicking to the double click event of the listbox?

 

[ajetrumpet]

Not to sound too flippant - but wouldn't it be as easy as moving the code that is on the button that you are clicking to the double click event of the listbox?

NO. the listbox does not have the same name as the button.

 

[Rabbie]

NO. the listbox does not have the same name as the button.

Adam, I think Bob probably meant that there should be code with the same functionality. A few names might need to be edited

 

[mafhobb]

AJETRUMPET,

This is what I need it to do, except that I am opening a form and a subform and for some reason I can't make that happen. What would be the code to open a form called "Project main" and a subform within it called "Project history" both showing the clicked record?

Thanks

mafhobb

 

[ajetrumpet]

It depends.

Are the two forms linked by way of parent-child relationship? You said the forms are "frmProjectMain" and "frmProjectHistory", correct?

If they are already involved in a relationship, all you need to code is the opening of the main form, and you do that like this:

on dblClick of listbox

  docmd.openform "Name", acnormal, , 
    "[project Name Field] = forms!CurrentForm!listboxname.column("ProjectNameColumn#")

In a linked rel, the sub's controls will automatically populate.

Is this your setup?

 

[boblarson]

A few names might need to be edited

The code used for the button WOULD be able to be used for the list box double-click EXCEPT for the error handler (which is why I don't usually use that back-assward Microsoft way of the error handler) I use a GENERIC error handler which can be used in ANY control because you DON'T need to name the error handler

MyButton_Click_Err

for heaven's sake just use e

err_handler:

exit err_handler

etc.

as there is NOTHING to be gained by naming the freakin error handler after the control. It doesn't report it to you in any way, so why do it? Because that is what comes out of the wizard, which is about as silly as the DoCmd.DoMenuItem code that comes out of the wizard.

I use MZ Tools to add my own error handler and the only thing that it does that ISN'T generic is in the message box tell you where the error occurred.

 

[mafhobb]

It works!!

Thank you Boblarson and everyone else who helped.

Mafhobb

 

[Zorkmid]

It depends.

Are the two forms linked by way of parent-child relationship? You said the forms are "frmProjectMain" and "frmProjectHistory", correct?

If they are already involved in a relationship, all you need to code is the opening of the main form, and you do that like this:

on dblClick of listbox
 
  docmd.openform "Name", acnormal, , 
    "[project Name Field] = forms!CurrentForm!listboxname.column("ProjectNameColumn#")

In a linked rel, the sub's controls will automatically populate.

Is this your setup?

I have a similar (almost identical) problem. I DO have a parent child relationship in my db. I'm not sure about the OP, but I find this thread VERY useful.. Im a bit confused by this though....

  " "[project Name Field] = forms!CurrentForm!listboxname.column (("ProjectNameColumn#")

To what does (ProjectNameColumn#") refer, and what about project name field?

The query that my listbox is sourced from gives all of the child records that have an EndDate field as null. So when I dblclick on the displayed record I want the form to open up for editing. I have played around with the code you generously provided, but I can't get this to apply to my listbox.

If you would look at my attachment, or point out where I might be going wrong in my dblclick code, it would be much appreciated.

-Z

 

[Zorkmid]

Similar question here:

http://www.access-programmers.co.uk/forums/showthread.php?p=890844#post890844

-Z