DblClick list box item to run query (1 Viewer)

Miff3436 · Nov 17, 2017

Hi all,

I have an unbound listbox named Listbox19 on a form, it has 5 columns in it of which one carries is a recordId. I also have a query named drilldown that also has a recordID field.

I was hoping to find a way that a user can double click on any item in the list box and it will run the query and return the matched recordId record in the query.

Thanks Miff3436

jdraw · Nov 17, 2017

Have you tried this? What result? If error, please tell us the number/description.

Miff3436 · Nov 17, 2017

Hi Jdraw,

I can double click a record from the listbox to run a query or open a form but not filtering the record to the one I have selected in the list box.

I'm not even at the stage of getting errors to be honest and was looking for some guidance.

Thanks Matt (Access 2010)

arnelgp · Nov 17, 2017

if in the macro you are running add ApplyFilter, the where cindition is your listbox

Lightwave · Nov 17, 2017

USING a VBA onclick event - Within the On_Click Event of the ListBox - my listbox is named List0

where ID filters the record you are wishing to show in the Form1 (change this to the name of your form)

I would normally close the form with the list box on it prior to going to the form but I've taken that out here so it should just popup with the other possibly in the background depending on modal setting.

In the listbox that I have ID happens to be the first column - can't remember that may be important as it obviously will only need one field to filter on.

Code:

Private Sub List0_Click()

    Dim stFormName As String
    Dim stLinkCriteria As String
    
    stFormName = "Form1"
    
    stLinkCriteria = "[ID]=" & Me.List0
    DoCmd.OpenForm stFormName, , , stLinkCriteria
    
End Sub

Miff3436 · Nov 17, 2017

Hi Lightwave,

I used your code and have just altered the form name and the listbox name to suit.

Code:

Private Sub List19_Click()
    Dim stFormName As String
    Dim stLinkCriteria As String
    
    stFormName = OpenNIDrillDown
    
    stLinkCriteria = "[RecordID]=" & Me.List19
    DoCmd.OpenForm stFormName, , , stLinkCriteria
End Sub

I am now getting a run time error 2494 - Action requires a form name argument.

I'm sure you can tell by my response that I am not the best with Access so you will have to stick with me.

Thanks for your response
Miff3436

arnelgp · Nov 17, 2017

enclised your formname inside quoutes "OpenNiDrillDown"

Miff3436 · Nov 17, 2017

Hi arnelgp,

I did that and now I am getting the following:

error 3464 Data type mismatch in criteria expression.

I have been through and had a look where there could be anything different and really cant identify the problem

Thanks Miff

arnelgp · Nov 17, 2017

if the list is text enclosed in diubke quote:

stLinkCriteria = "[RecordID]=" & """" & Me.List19 & """"

Miff3436 · Nov 17, 2017

Hi arnelgp,

Yes that has done the trick, perfect. Thank you all for your help with this issue it is now working exactly how I wanted / needed it to.

Have a good weekend all

Miff3436

DblClick list box item to run query (1 Viewer)

Miff3436

Registered User.

jdraw

Super Moderator

Miff3436

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Lightwave

Ad astra

Miff3436

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Miff3436

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Miff3436

Registered User.

Similar threads

Users who are viewing this thread