Full Version: Automatically select a record from listbox
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
robertdagg
Oct 10 2004, 10:00 PM
Hi all

I have a listbox on a form used for searching records. Several fields are avilable for searching, so the SELECT is a muli-part SELECT. The listbox is populated by the SELECT.

In some circumstances, the user will only have one record returned. I'd like in that situation to have the search form close and the form displaying the full record open.

I have tried to use:
CODE
  
            where = ("Select PKSupplier, SuppName from tblSuppliers " _
                & (" where " + (where)))
            Me.lstSuppliers.RowSource = where
            
            strLinkCriteria = "[PKSupplier]=" & Me.lstSuppliers

But the strLinkCriteria always ends up with "[PKSupplier]=" and I have no criteria to pass to the next form.

I'm guessing that as the listbox does not receive focus and as such the record is not selected, then that is why I'm getting nothing passed to my next form.

Is there a better way to make this happen, or is there a way to pick up the record from the listbox?
If I can pick up the first record automatically from the listbox, then I think the code will work.

Appreciate your time.
Robert.
HelloAgain
Oct 10 2004, 10:50 PM
I think the best way to go about this is to run a Dlookup statement first, and if the result is 1 (i.e., only 1 record matches), add code to open whatever form displays that record and bypass the listbox completely. If the Dlookup statement returns a value of greater than one, display the list box as normal, and then your problem should be solved.
HelloAgain
Oct 10 2004, 10:57 PM
I'm sorry, I meant DCOUNT, not DLOOKUP. It's getting late...
R. Hicks
Oct 10 2004, 11:07 PM
No need for the DLookup() or DCount() function ...
You can get the number of items in a listbox by using:

ListboxName.ListCount

RDH
robertdagg
Oct 11 2004, 08:39 AM
Hi Matt, Hi Ricky

Ahh .... seems I didn't quite explain my problem properly. Sorry 'bout that.

I've gotten past the problem of knowing how many records I have returned, now I'm stuck trying to get the right record to display on the form. (There are two forms - the first is the search form, the second is a form that displays all the data for the record selected from the search form).

Here's the code I'm trying to work with:
CODE
  
Private Sub cmdSearch_Click()
' Search for a Supplier with the supplied criteria

    On Error GoTo Err_cmdSearch_Click

    Dim where As Variant
    Dim intNum As Integer

    where = Null
    where = "[SuppName] like " + "'*" + Me![txtSupplierName] + "*'"
    
    ' Check how many suppliers are being returned.
    ' If only one - go back to Supplier form and display.
    ' If more than one, let user pick from this screen.
    ' If zero - display error message.
    Select Case DCount("[SuppName]", "tblSuppliers", where)
        Case 0
            ' No records matched the selection criteria
            MsgBox "There are no Suppliers matching your request" & Chr$(13) _
                & "               Please try again.", vbCritical, "No matches"
            txtSupplierName.SetFocus
                
        Case Is < 2
            ' Only one record matched
            Me.lblListBoxSupplier.Visible = True
            Me.lblListBoxHeader.Visible = True
            Me.lstSuppliers.Visible = True
            
            where = ("Select PKSupplier, SuppName from tblSuppliers " _
                & (" where " + (where)))
            Me.lstSuppliers.RowSource = where
            
            strLinkCriteria = "[PKSupplier]=" & Me.lstSuppliers
            
' Here is where I am not getting the right code .....                
            MsgBox strLinkCriteria
            DoCmd.Close
            DoCmd.OpenForm "frmSuppliers", , , strLinkCriteria

        Case Else
            ' More than one result, so display the results in the listbox
            Me.lblListBoxSupplier.Visible = True
            Me.lblListBoxHeader.Visible = True
            Me.lstSuppliers.Visible = True
            where = ("Select PKSupplier, SuppName  from tblSuppliers " _
                & (" where " + (where)) & "ORDER BY [tblSuppliers].[SuppName];")
            Me.lstSuppliers.RowSource = where

    End Select

Exit_cmdSearch_Click:
    Exit Sub

Err_cmdSearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearch_Click
    
End Sub     ' cmdSearch_Click


I can't seem to get the case of just one record to work. I seem to be missing the knowledge on how to get the key (PKSupplier) from the search and pass it back to the second form to display the full record.

Thanks in advance for your assiatance.
Robert.
robertdagg
Oct 11 2004, 09:32 AM
Basically what I am trying to find out how to do, is how do I get the cursor to select the first item in a listbox automatically. IE: is there a method for listboxes for positioning the cursor?


Thanks
Robert.
robertdagg
Oct 11 2004, 10:16 AM
Ahhhhh ... have found the answer......

I unearthed the facts on listboxes, and found there is a property - listindex - and now I am using that property to set the value I want from the listbox and all is working great.

Thanks
Robert.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.