Results 1 to 6 of 6
  1. #1
    cgschlangen
    Guest

    Listbox Help (Access 2000)

    Hello loungers [img]/forums/images/smilies/smile.gif[/img]
    These listboxes are wonderful things, but I'll be darned if I can figure out how to work them - lol

    Actually I've got the listbox doing just about everything I want it to do - except this:
    I have Form: Sound Manager that pulls records out of Table: Sounds --- works beautifully
    I have Form: Initials that includes a text field and a search button - When a user clicks the Search button it takes the value of the Text Field and runs a query which then populates a listbox... What I want from there is a user to be able to select a returned record from the listbox which will load that record into Form: Sound Manager....
    Here's the code I'm using - but I'm not sure why it won't work

    Private Sub List7_Click()
    Dim rsColumn As Recordset

    rsColumn = Me.List7.Value
    DoCmd.GoToRecord acDataForm, SoundManager, acGoTo, rsColumn
    End Sub

    I'm fairly new to all this VBScripting - so chances are the format is wrong... Any help?
    Thanks
    Dan

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Help (Access 2000)

    First of all, VBScript and VBA are two different things. In Access, you're dealing with VBA, not VBscript. Second, a recordset is an object, not a value, and you can't set a recordset to a value in a listbox. Why don't you explain what is in the listbox so we have some idea of what to suggest.

    As far as the syntax of your DoCmd.GotoRecord line, it is wrong in several places. For one, the form argument is a string, which means it needs to be in quotes. Also, you can't pass a recordset or a record itself into the offset argument. The use of acGoTo means that the last argument has to evaulate to a number in the recordset, such as record number 10.
    Charlotte

  3. #3
    cgschlangen
    Guest

    Re: Listbox Help (Access 2000)

    Oh...
    More stuff I'd yet to learn - thanks for the reply... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    The query the listbox is populated by - Returns the following information -->

    | <font color=d2691e>UIN (Key)</font color=d2691e> | <font color=ff8c00>Initials</font color=ff8c00> | <font color=d2691e>Date</font color=d2691e> | <font color=ff8c00>Time</font color=ff8c00> | <font color=d2691e>Case Number </font color=d2691e> |

    I understand that the acGoTo needs a record number - and I guess what I'm getting at is how do I retrieve the record number?

  4. #4
    cgschlangen
    Guest

    Re: Listbox Help (Access 2000)

    Well, I'm picking up more and more why it won't work... Here's the code I"m using now:

    Private Sub List7_Click()
    Dim varColumn As Variant

    DoCmd.GoToRecord acDataForm, "SoundManager2", acGoTo, Me.List7.Value + 1
    End Sub

    which works just fine - what it returns though is - like you said - a record number, which won't discriminate when there is more than 1 set of initials in the database... (ex: record 30 in main table is record 1 in the query - but when you want to show the item in your form - you'll get record 1 from the main table) so to temp fix this I created another form which has the RecordSource of the Query I'm using for the list box.... so far it's worked like a champ... but I don't want to have my users click through 10 different forms to find information - so I guess what I need to find out is - if there is a way to change the recordsource on my original form to reflect the query that my listbox is based from? Or - - - is there a way to make the code above reflect the record number of the record on the table?
    Am I making any sense?
    I appreciate your help...
    Dan

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox Help (Access 2000)

    You state that the first column in the list box is UIN (Key). If the bound column property of the list box is 1, and if UIN is numeric, you can use this code:

    Private Sub List7_Click()
    Me.RecordsetClone.FindFirst "UIN=" & Me.List7
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub

    If UIN is a text field, use"UIN = " & Chr(34) & Me.List7 & Chr(34) instead. Chr(34) is the double quote character ".

  6. #6
    cgschlangen
    Guest

    Re: Listbox Help (Access 2000)

    Hans you are the best!!!!
    Works like a charm... thanks a million
    Dan

Posting Permissions

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