Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unanswered: get the ID number field from listbox

    I've been trying to get an answer to this problem for the last couple of days by searching the internet. I have a listbox on my main form interface that lists out all records from tblMSL using a query -> "SELECT [tblMSL].[Entry Number], [tblMSL].[Date], [tblMSL].[Time (Zulu)], [tblMSL].[Initials], [tblMSL].[Subject], [tblMSL].[Event] FROM tblMSL ORDER BY [tblMSL].[Entry Number]; "

    What I'm trying to do is allow the user to select a record from the listbox (MSLListBox) and click on an Edit button on the same form. It would then open up an Edit form that contains the record selected.

    It worked fine by doing [tblMSL].[Entry Number] = [Forms]![Interface]![MSLListBox].ListIndex when sorted by Entry Number. But when I started doing selective sorting, it wouldn't work because it was referencing the row number in the listbox, instead of the actually ID number in the table attached to the listbox. How would I go about extracting the ID number from the listbox record so I can tell the form which record to display for updating?

  2. #2
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey darhi,

    Try using your double click event, so when the user double-clicks the selected record the form will open. Like this:

    Dim ctl As Control
    Dim varItem As Variant
    Dim Filter As Variant
    If Me.msllistbox.ItemsSelected.Count = 0 Then
    MsgBox "No selection was made", vbInformation, "Message"
    ElseIf Me.msllistbox.ItemsSelected.Count > 1 Then
    MsgBox "You may only select one record to view.", vbInformation, "Message"
    Else
    Set ctl = Me.msllistbox
    For Each varItem In ctl.ItemsSelected
    Filter = ctl.ItemData(varItem)
    Filter = "[Entry Number]=" & Filter &
    Next varItem
    DoCmd.OpenForm "YourFormName", , , Filter
    End If

    This will open the form based on the fact that the first column on your listbox is the entry number and the form being opened has the entry number field.

    Let me know if you have any questions.
    Kal

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: get the ID number field from listbox

    Originally posted by darhl
    I've been trying to get an answer to this problem for the last couple of days by searching the internet. I have a listbox on my main form interface that lists out all records from tblMSL using a query -> "SELECT [tblMSL].[Entry Number], [tblMSL].[Date], [tblMSL].[Time (Zulu)], [tblMSL].[Initials], [tblMSL].[Subject], [tblMSL].[Event] FROM tblMSL ORDER BY [tblMSL].[Entry Number]; "

    What I'm trying to do is allow the user to select a record from the listbox (MSLListBox) and click on an Edit button on the same form. It would then open up an Edit form that contains the record selected.

    It worked fine by doing [tblMSL].[Entry Number] = [Forms]![Interface]![MSLListBox].ListIndex when sorted by Entry Number. But when I started doing selective sorting, it wouldn't work because it was referencing the row number in the listbox, instead of the actually ID number in the table attached to the listbox. How would I go about extracting the ID number from the listbox record so I can tell the form which record to display for updating?

    Did you try this :
    [tblMSL].[Entry Number] = [Forms]![Interface]![MSLListBox].Column(0)

  4. #4
    Join Date
    Apr 2003
    Posts
    1
    Leave off the listindex i.e.

    [tblMSL].[Entry Number] = [Forms]![Interface]![MSLListBox]

  5. #5
    Join Date
    Apr 2003
    Posts
    3

    Smile Re: get the ID number field from the listbox

    Thanks for all the help guys, that helped me out. I got it working now .
    I would also like to thank you guys for a quick response. Most forums I've been to, I wouldn't get a reply for days, even weeks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •