Listbox dblclick issue

[boostbyslinky]

Morning..

I have two listboxes, both doing similar things (displaying commitments against properties/suppliers)..
The first listbox is a list driven by a query and can be double clicked to launch a display/edit form.. This works without issue.
The 2nd listbox within a form which is called from a "search" form. The submit button on the search form amends the query used to populate the listbox.

These parts all work perfectly. However, attempting to doubleclick on a record in the 2nd listbox seems to fail to pass a value to Me!List1 (yes, it's called list1, but is the 2nd listbox (just had a thought, they're both called List1, but are on different forms, is there an issue here (other than logical or sense) )..

So, when attempting to execute this VB

Private Sub List1_DblClick(Cancel As Integer)
'Open Commitment in Commitment Display Form (Read Only)
Dim stLinkCriteria As String
Dim ALimit, TCA As Double
stLinkCriteria = "ID=" & Me!List1
ALimit = DLookup("Authlimit", "tblstaff", "Userid='" & Environ("username") & "'")
TCA = DLookup("TotalContractAmount", "tblCommitments", " ID=" & Me!List1 & "")
If ALimit > TCA Then
    If DLookup("Authorised", "tblCommitments", " ID=" & Me!List1 & "") = -1 Then
        DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
    Else
        DoCmd.OpenForm "frmCommitmentApprove", , , stLinkCriteria, acFormReadOnly
    End If
Else
DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
End If
End Sub

It fails as Me!List1 is Null, leading to stLinkCriteria being Null and the TCA Dlookup failing as well..

The two processes for each of the listboxes are EXACTLY the same, the only difference between the two is a change in rowsource for the 2nd listbox..

Help!?

 

[pr2-eugin]

Step through the Code.. Or do some debugging.. Just wondering, you seem to open the Form frmCommitmentDisplay inside the If and outside.. If the List1 value is Null the one outside the If will still try to open?

 

[boostbyslinky]

Hello again Paul...

It fails @

TCA = DLookup("TotalContractAmount", "tblCommitments", " ID=" & Me!List1 & "")

Using the immediates window, ?me!List1 returns Null, whereas if I stick a break point into the "other" listbox and run the same query in immediates, I get the ID of the record I doubleclicked on..

 

[pr2-eugin]

So okay let me get this right.. Where is this Event called? I am quiet

Could you explain with a logical sequence..

FormA -> ListBox doubleClick -> Requery FormB ListBox..

 

[boostbyslinky]

FormA Listbox -> DoubleClick -> Commitment Display/Edit Form (works)

FormA "search" button -> FormB -> Select Property or Supplier from Combox box -> Search button -> FormC Listbox Rowsource amended and Form Launched.
(this part works!)

Private Sub Command5_Click()
Dim SQLStr As String
SQLStr = Me.SuppSelect.Value
SQLStr = "SELECT qryCommitmentSummary.ID, qryCommitmentSummary.Type, qryCommitmentSummary.ShortDescription, qryCommitmentSummary.Property, qryCommitmentSummary.TotalContractAmount, qryCommitmentSummary.EndDate, qryCommitmentSummary.Owner, qryCommitmentSummary.Status, qryCommitmentSummary.Approved FROM qryCommitmentSummary WHERE (((qryCommitmentSummary.Supplier)=""" & SQLStr & """)) ORDER BY qryCommitmentSummary.[ID];"
DoCmd.OpenForm "frmcommitmentlist", acNormal, , , acFormReadOnly, acHidden
Forms![frmcommitmentlist]![List1].RowSource = SQLStr
Forms![frmcommitmentlist].Visible = True
DoCmd.Close acForm, "frmCommitSearch", acSaveNo

Doubleclick on selected row in formC!listbox to call the code in my first post. 'tis this part which fails..

 

[pr2-eugin]

Okay instead of the Bang (!) operator try the Dot (.).

Private Sub List1_DblClick(Cancel As Integer)
   [COLOR=Green] 'Open Commitment in Commitment Display Form (Read Only)[/COLOR]
    
    Dim stLinkCriteria As String
    Dim ALimit[COLOR=Blue] As Double[/COLOR], TCA As Double
    stLinkCriteria = "ID = " & Me[COLOR=Red][B].[/B][/COLOR]List1
    
    ALimit = [COLOR=Blue]Nz([/COLOR]DLookup("Authlimit", "tblstaff", "Userid = '" & Environ("username") & "'")[COLOR=Blue], 0)[/COLOR]
    TCA = [COLOR=Blue]Nz([/COLOR]DLookup("TotalContractAmount", "tblCommitments", "ID = " & Me[COLOR=Red][B].[/B][/COLOR]List1)[COLOR=Blue], 0)[/COLOR]
    
    If ALimit > TCA Then
        If [COLOR=Blue]Nz([/COLOR]DLookup("Authorised", "tblCommitments", " ID = " & Me[B][COLOR=Red].[/COLOR][/B]List1)[COLOR=Blue], 0)[/COLOR] = -1 Then
            DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
        Else
            DoCmd.OpenForm "frmCommitmentApprove", , , stLinkCriteria, acFormReadOnly
        End If
    Else
        DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
    End If
End Sub

 

[boostbyslinky]

?me.List1
Null

 

[pr2-eugin]

Lets debug !

Private Sub List1_DblClick(Cancel As Integer)
    Dim lCtr As Long
    Debug.Print "Row Type : " & Me.List1.RowSourceType & vbCrLf
    Debug.Print "Row Source : " & Me.List1.RowSource & vbCrLf
    For lCtr = 0 To Me.List1.ListCount - 1
        Debug.Print "Row - " & lCtr + 1 & " : " & Me.List1.Column(0, lCtr) & "/" & Me.List1.Column(1, lCtr)
    Next
[COLOR=Green]'    Open Commitment in Commitment Display Form (Read Only)
    
'    Dim stLinkCriteria As String
'    Dim ALimit As Double, TCA As Double
'    stLinkCriteria = "ID = " & Me.List1
'
'    ALimit = Nz(DLookup("Authlimit", "tblstaff", "Userid = '" & Environ("username") & "'"), 0)
'    TCA = Nz(DLookup("TotalContractAmount", "tblCommitments", "ID = " & Me.List1), 0)
'
'    If ALimit > TCA Then
'        If Nz(DLookup("Authorised", "tblCommitments", " ID = " & Me.List1), 0) = -1 Then
'            DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
'        Else
'            DoCmd.OpenForm "frmCommitmentApprove", , , stLinkCriteria, acFormReadOnly
'        End If
'    Else
'        DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
'    End If[/COLOR]
End Sub

Paste the result from the immediate window to here..

 

[boostbyslinky]

Here we go.. (supplier name changed to ZZZZZ for conf. reasons)


Row Type : Table/Query
Row Source : SELECT qryCommitmentSummary.ID, qryCommitmentSummary.Type, qryCommitmentSummary.ShortDescription, qryCommitmentSummary.Property, qryCommitmentSummary.TotalContractAmount, qryCommitmentSummary.EndDate, qryCommitmentSummary.Owner, qryCommitmentSummary.Status, qryCommitmentSummary.Approved FROM qryCommitmentSummary WHERE (((qryCommitmentSummary.Supplier)="ZZZZZ")) ORDER BY qryCommitmentSummary.[ID];
Row - 1 : ID/Type
Row - 2 : 5/Test
Row - 3 : 6/Test
Row - 4 : 7/Test
Row - 5 : 8/Test
Row - 6 : 9/Test
Row - 7 : 10/Test
Row - 8 : 11/Test
Row - 9 : 12/Test
Row - 10 : 13/Test
Row - 11 : 41/Test
Row - 12 : 43/Test
Row - 13 : 44/Test
Row - 14 : 45/Test

 

[pr2-eugin]

Looks like it is giving the right ID.. Probably it is not the ListBox that is causing the trouble.. atleast from the data.. For just to be sure.. Just do something like..

Private Sub List1_DblClick(Cancel As Integer)
   [COLOR=Green] 'Open Commitment in Commitment Display Form (Read Only)[/COLOR]
    
    Dim stLinkCriteria As String, listID As Long
    Dim ALimit[COLOR=Blue] As Double[/COLOR], TCA As Double
    listID = Me[COLOR=Red][B].[/B][/COLOR]List1.Column(0, List1.ItemsSelected)
    stLinkCriteria = "ID = " & listID
    
    ALimit = [COLOR=Blue]Nz([/COLOR]DLookup("Authlimit", "tblstaff", "Userid = '" & Environ("username") & "'")[COLOR=Blue], 0)[/COLOR]
    TCA = [COLOR=Blue]Nz([/COLOR]DLookup("TotalContractAmount", "tblCommitments", "ID = " & listID)[COLOR=Blue], 0)[/COLOR]
    
    If ALimit > TCA Then
        If [COLOR=Blue]Nz([/COLOR]DLookup("Authorised", "tblCommitments", " ID = " & listID)[COLOR=Blue], 0)[/COLOR] = -1 Then
            DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
        Else
            DoCmd.OpenForm "frmCommitmentApprove", , , stLinkCriteria, acFormReadOnly
        End If
    Else
        DoCmd.OpenForm "frmCommitmentDisplay", , , stLinkCriteria, acFormReadOnly
    End If
End Sub

 

[boostbyslinky]

Cheers Paul..
Just tested it and it still seems to be returning a Null value..
It's failing at
listID = Me.List1.Column(0, List1.ItemsSelected)
now..

One point to note (whether this is valid, or means anything, I don't know) .. When clicking on a row in the "other" listbox, the row is highlighted, inferring a selection.
When clicking on a row in the problematic listbox, there is no obvious selection..

I've compared the properties of the two listboxes and can see no apparent difference..

 

[pr2-eugin]

Could you possibly Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[boostbyslinky]

Morning Paul..
Apologies for not coming back to you last week.

With a fresh head I've looked at the issue this morning, created a new form, listbox, query and started re-creating the scenario.
I discovered that if I launched the form from the navigation pane the process was fine, however, if I launched it from a button click on the "search" form it was failing..
So I started to look deeper at how I was launching the form.. and then it hit me..

DoCmd.OpenForm "frmcommitmentlist", acNormal, , , acFormReadOnly, acHidden

The readonly section stops you from "selecting" any of the records in the listbox, therefore when double clicking on the listbox, it does exactly what it's supposed to do, but with no record selected, there's no data to pass to the next routine.

Having switched it to acFormEdit it now works perfectly!

 

[pr2-eugin]

Ah, so you have solved your own riddle.. Good for you..