Listview Click Event 3

[ZmrAbdulla]

Hi,
Can anyone help me to create a click event on ListView (not Listbox)
My requirement is to open a form containing more data of the record clicked in the listview
Using Access 2000
Thanks

Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com

 

[thendrickson]

I did not realize that you could use the ListView in Access.
A search of help in Access did not turn anything up.

Never the less, I assume you know what you are using so here is my best solutions

Assuming that you are actually using Access forms you can set an openargs propery containing the value of the selected
listview item. Use that value to create the SQL statement on the fly.

If, as I believe, you are using VB with an Access database, thisngs are only sllightly more complex, but much more flexible.

First create needed puplic properities for the form you are opening then use them to create the SQL statement dynamically.

This can be as simple as something like this:

FormDetails

Private mStrWhere as string

Public property Let propStrWhere(byval as string)
mStrWhere = propStrWhere
end property


I suggest creating the SQL statement in a seperate sub and calling it from either form load or form activate event.

That depends on what you are doing. Form Load is probably the most intuitive event

From the form containing the list view control

Use the DblClick event.
Feel free to experiment with the click event and you will see why I suggest this. The code is simple and the same either way.

the code should look something like this:

DetailsForm.propStrWere = listview.selecteditem

or like

DetailsForm.propStrwhere = "Where Name = " & listview.SelectedItem.

This is the simplistic view and is just the basics.
You can create quite complex queries this method and can be very flexible.

 

[jksmi]

Hi,

if your listview doesnt already contain the primary key for your record then the first thing to do is to add this, you can use the tag property, i.e.

Dim itmX as ListItem
Set itmX = listView.ListItems.add(, , [i]YOUR_RECORDSET!ITEM_NAME[/i])
with itmX
    .ListSubItems.add , , [i]YOUR_RECORDSET!ITEM_DESC[/i]
    [highlight].Tag = [i]YOUR_RECORDSET!ITEM_PRIMARY_KEY[/i][/highlight]
    .ToolTipText = "Location: " & mso.BasicProperties.Location
End With

Then on the click event something like...

Private Sub listView1_Click()
Dim strFilter As String
Dim frm As Form

Set frm = Forms.Item("[i]YOUR_OTHER_FORM_NAME[/i]")

If Isloaded(frm.Name) Then
    strFilter = "[ITEM_PRIMARY_KEY] = " & listView1.SelectedItem.Tag    [COLOR=green]' remember to surround with ' if string, i.e. not numeric[/color]
    strKey = msosFound.Item(strKey).BasicProperties.LocationID & "¬" & strKey
    frm.Filter = strFilter
    frm.FilterOn = True
    frm.SetFocus
End If
End Sub

Doesnt have to be a primary key of course, filter will apply to any field, its just the WHERE clause of a query without the WHERE.

The isloaded function is nicked from the Northwind database. It just checks that your other form is open, so you dont get errors, so you dont have to use it - if you do want to it goes like this...

Function Isloaded(ByVal strFormName As String) As Integer
    Const conObjStateClosed = 0
    Const conDesignView = 0

    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            Isloaded = True
        End If
    
    End If
    
End Function

HTH, Jamie
FAQ219-2884

 

[thendrickson]

Jamie,

Yes you can use the filter property.

Which is more than enough for a simple where clause. (same thing I know) and this might be all he needs

I perhaps over simplified my example a bit too much, but I was attempting to show the very basics of what can be a somewhat complex, but effective design.


I have worked with VBA enough to understand VBA and while I do not care for VBA, VBA can be almost as flexible as VB if the app is designed properly and written with flexibility in mind.

Using the Access open Args you can create a complex SQL Statement in the calling forms code then pass that SQL statement to the called form.

You may also add properties to the called form, set them from the calling form and then create the full SQL statement from the called forms code.

Where you set the tag property to the primary key (a very good point which I overlooked by the way), I would set a property on the called form instead.

I prefer to encapsulate as much as possible in a given incidence of the form class

There may be advantages to either creating the SQL statement using the calling form and passing the complete SQL statement to the called forms OpenArgs property or setting additional properties on the called form depending on your purpose, but I feel that setting the properties on the called form is the better way (other Opinions will be quite welcome)

I prefer to create SQL Statements dynamically (Based on some pretty tough scrapes I got myself into using bound controls)

It is my opinion, and I am certain that I will hear about this, is that assigning a record source at design time is a poor way to go. This should be done at run time.

Even if you wish to write your queries at design time, you could still use the OpenArgs property (or create your own) to determine which query to run.

then something like

if openargs = "Bill" then
me.recordsource = qBill
elseif me.openargs = et, etc

might be the way to go.

I feel the key to any development is flexibility and ease of maintenence. Try not to lock yourself into a corner.

 

[thendrickson]

Just reread my last post and I do not care for the way I came across I am afraid.

Hope I am not being offensive to any one

 

[ZmrAbdulla]

HI,
Thanks to both of you,
After posting this question, I have done a search in google group and found some useful posts.
Finally I made it to as simple as opening another form from a continues form. Changed Click to Double Click for better performance. I am using Access Forms. Also have a PrimaryKey.

Private Sub ListView1_DblClick()
On Error GoTo Err_ListView1_DblClick
    Dim lListItem As ListItem
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Ledger"
    
    stLinkCriteria = "[LedgerEntryID]=" & Me!ListView1.SelectedItem.ListSubItems(5).Text
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ListView1_DblClick:
    Exit Sub

Err_ListView1_DblClick:
    MsgBox Err.Description
    Resume Exit_ListView1_DblClick
            
  End Sub

This is similar to your suggestions.
Thanks again.

Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com

 

[thendrickson]

ZA

Actually, you are doing exactly what Jamie was suggesting since the final argument passed sets the filter property.

I am not saying that this is bad and it is surely simple which is an exactly what Access is all about.

But applications tend to grow and become more complex. A form you design today may be useful for a different app or maybe just open it from another form.

In the US there is an old saying about the "Kiss factor" which stands for "Keep it Simple".

You are following that principle for now, but future changes may become unnecessarily complex.

I have put myself into a box many times before learning to design ahead for those changes.

If you choose to go the route of using the filter property and setting the record source at design time then please use Jamies code. His way is more flexible for future growth.

IE: perhaps you might want to open the same form again in the future, but with the filter property turned off.

 

[jksmi]

Hi thendrickson,

No offence taken, I'm allways answering q's without reading them properly and then getting picked up on it later - I assumed the other form was already open (hence the call to isloaded). Maybe I should learn my lesson...

Good point about the open args - I've seen quite a few q's in the access forums asking how to open a form and pass information to it from another. Perhaps someone should write a fact - that and my favourite; raiseevent.

HTH, Jamie
FAQ219-2884

 

[ZmrAbdulla]

Thanks for that comments


Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com

 

[Betsvigi9]

Hello

I am trying to do the same thing as ZmrAbdulla i.e. open a form containing more data of the record clicked in the listview, but using Access 97. I am struggling with the stLinkCriteria and although the form opens, it doesn't open the correct record. The example below is using Access 2000, does anyone know what the comparable code would be for the .SelectedItem.ListSubItems(5).Text section would be for access 97.

Thanks

<<Private Sub ListView1_DblClick()
On Error GoTo Err_ListView1_DblClick
Dim lListItem As ListItem
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Ledger"

stLinkCriteria = "[LedgerEntryID]=" & Me!ListView1.SelectedItem.ListSubItems(5).Text
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ListView1_DblClick:
Exit Sub

Err_ListView1_DblClick:
MsgBox Err.Description
Resume Exit_ListView1_DblClick

End Sub>>

 

[ZmrAbdulla]

Try Changing

stLinkCriteria = "[LedgerEntryID]=" & Me!ListView1.SelectedItem.ListSubItems(5).Text

To

 stLinkCriteria = "[LedgerEntryID]=" & "'" & Me!ListView1.SelectedItem.ListSubItems(5).Text & "'"

Sometime the criteria is not recognized by Access if you don't put it in double quotes

Zameer Abdulla
Visit Me

 

[Betsvigi9]

Thanks Zameer

Actually that wasn't the problem, the problem was that I was stupidly using the wrong index. That you confirmed that the code should have worked, got me on the right track though.