Use subform/query as listbox?

[christakis]

Hi,

I have a form with a few combo-boxes and a subform. The subform displays the results of a query based on the selection of the combo-boxes above.

Each row of the query esentially is a record. I want to select the whole row (like a listbox) and use it to run a vba code using info from a field of the row. Ideally the code would execute when i double click the row

Is there a way to do this? I cant use a listbox as access says I can only have 20 fields (I have around 35) at a time.

Best Regards,
Chris

 

[HiTechCoach]

You can use the double click event of the controls on the sub form to run your VBA code. I usually make a function behind the form/sub form that holds the VBA code. I set function to be run with the On Click event for every control on the form/sub form.

Example:

Public Function fDoSomethingWithCurrentRec()

On Error GoTo Err_DoSomethingWithCurrentRec_Click

    Dim stDocName As String


' Be sure any changes are saved
  If Me.Dirty Then Me.Dirty = False

' Open a report for the current record

    stDocName = "rptMyReportName"
    DoCmd.OpenReport stDocName, acPreview, , "RecID=" & Me.RecID

Exit_DoSomethingWithCurrentRec_Click:
    Exit Sub

Err_DoSomethingWithCurrentRec_Click:
    
    If Err.Number <> 2501 Then ' if report not canceled
        MsgBox Err.Description
    End If
    Resume Exit_DoSomethingWithCurrentRec_Click

End Function

To call use the Function for each control, set the Double Click Event to be:

=fDoSomethingWithCurrentRec()

Note: This is not using an [Event Procedure].

 

[christakis]

Thank you for your reply. Your code is golden. It works like a treat However I have a few more general questions regarding were I can put this. Part of my form structure is like this:

frmSearch
-Tab 1
--Subform searchType1
---Subform queryResultForType1 <-*
-Tab 2
--etc
---etc
...
down to
-Tab24

The code is currently in the subform marked with <-*. Do I need to put the code in all "queryResultForTypex" subforms or will it still work if I place it only once in "frmSearch"?

I am a bit confused on the scope functions in forms have. What scope does the Me.blahblah have? Now I think of it what exactly is Me. ?

Best Regards,
Christakis