My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Automatically select a record from listbox
|
|
 Oct 10 2004, 10:00 PM
Post
#1
|
|
|
UtterAccess Veteran Posts: 474 From: Lakewood, Colorado, USA  |
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. |
 |
 
|
|
Oct 10 2004, 10:50 PM
Post
#2
|
|
|
UtterAccess Guru Posts: 703 |
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.
|
|
|
|
|
Oct 10 2004, 10:57 PM
Post
#3
|
|
|
UtterAccess Guru Posts: 703 |
I'm sorry, I meant DCOUNT, not DLOOKUP. It's getting late...
|
|
|
|
|
Oct 10 2004, 11:07 PM
Post
#4
|
|
|
UA Forum Administrator Posts: 38,073 From: Birmingham, Alabama USA |
No need for the DLookup() or DCount() function ...
You can get the number of items in a listbox by using: ListboxName.ListCount RDH |
|
|
|
|
Oct 11 2004, 08:39 AM
Post
#5
|
|
|
UtterAccess Veteran Posts: 474 From: Lakewood, Colorado, USA |
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. |
|
|
|
|
Oct 11 2004, 09:32 AM
Post
#6
|
|
|
UtterAccess Veteran Posts: 474 From: Lakewood, Colorado, USA |
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. |
|
|
|
|
Oct 11 2004, 10:16 AM
Post
#7
|
|
|
UtterAccess Veteran Posts: 474 From: Lakewood, Colorado, USA |
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. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 12:20 PM |