Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Is this the correct code to display selected records from a listbox?

    Hello, what is the correct code if I was to select a row in a listbox on a form and have the corresponding records display in textboxes on the form? The listbox and textboxes/form are the same record source.

    I found this code which Access automatically generated when I added the listbox to the form and chose the option "Find A Record on my form based on a value in my listbox". The code works, but could someone explain it to me as Im not to hot with vba code as to what is actually happening -
    Code:
      Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[itemID] = " & Str(Nz(Me![List59], 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    it isn't that complicated. if the textboxes are unbound then simply put this into their control source:
    =me.ListBoxName.Column(2)
    (you have to put in the correct column number of course)

    if the textboxes are bound to the underlying record source of the form then one uses the 'AfterUpdate' or 'OnExit' event of the listbox to push the value to them:
    me.TextBoxName=me.ListboxName.Column(2)

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by NTC View Post
    it isn't that complicated. if the textboxes are unbound then simply put this into their control source:
    =me.ListBoxName.Column(2)
    (you have to put in the correct column number of course)

    if the textboxes are bound to the underlying record source of the form then one uses the 'AfterUpdate' or 'OnExit' event of the listbox to push the value to them:
    me.TextBoxName=me.ListboxName.Column(2)
    Thank you, your second example works great, I just put the code into the primary key textbox and now when I click a row in the listbox the selected record displays inside the all texboxes on my form.

    Super!

    So why can't Access's own code be that simple?

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    "Access's own code" ???....I don't know what is meant by that.....code is code.... the code for a list box to find a record (which is what you displayed in your original post) is a completely different task.....if your question had been on how to write code that makes a selection in a list box find the actual record...that same code would have been my answer....

    when you add a list box - Microsoft anticipates its use and puts that code in there upfront. it isn't a matter of it being more or less complicated. it all depends on the task that needs to be done. Finding a record is more complicated.

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by NTC View Post
    "Access's own code" ???....I don't know what is meant by that.....code is code.... the code for a list box to find a record (which is what you displayed in your original post) is a completely different task.....if your question had been on how to write code that makes a selection in a list box find the actual record...that same code would have been my answer....

    when you add a list box - Microsoft anticipates its use and puts that code in there upfront. it isn't a matter of it being more or less complicated. it all depends on the task that needs to be done. Finding a record is more complicated.
    Well you should know what is meant by it! Im talking when you put a list box on a form and Access gives you the choice of "find a record on my form based on the value I select in my listbox"...and when you click NEXT it generates its own vba code in the on-click-event of that listbox - the code I orgininally supplied in this thread - I'd call that "ACCESS'S own code"....seeing that Access generated it by iteself...but you see that differently then...

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    To answer your original question: When you clicked on (Selected) a value from the list box, that does not in and of itself reset the current record to match the list box value. The list box only controls the single field (in this case ItemID). If in the wizard you choose "Find a record ...", you are telling Access that when you select a value, you want Access to programatically reset the current record to the first record where the ItemID agrees with the value in the list box.

    Therefore, beware. If you have multiple records with the selected ItemID, you need as many list boxes as necessary to make the selection truly unique.

    As an aside, I'm curious why you're using a list box instead of a combo box. They do the same thing. The only time - almost - that I use a list box is when I want to select multiple values, which doesn't apply in your application.

    SL

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sam Landy View Post
    To answer your original question: When you clicked on (Selected) a value from the list box, that does not in and of itself reset the current record to match the list box value. The list box only controls the single field (in this case ItemID). If in the wizard you choose "Find a record ...", you are telling Access that when you select a value, you want Access to programatically reset the current record to the first record where the ItemID agrees with the value in the list box.

    Therefore, beware. If you have multiple records with the selected ItemID, you need as many list boxes as necessary to make the selection truly unique.

    As an aside, I'm curious why you're using a list box instead of a combo box. They do the same thing. The only time - almost - that I use a list box is when I want to select multiple values, which doesn't apply in your application.

    SL
    Hello, yes you are totally correct with saying that I want access to programatically resest the current record where the ItemID agrees with the value in the listbox.

    I came across the issue of the current record not matching the listbox value, so I have returned to using the code which was generated by Access for the listbox as it does reset the current record. Thankyou for pointing it out also thought

    Im using a listbox as I also have a seperate textbox to allow me to search for a record in a field, which will then be displayed in the listbox, which then can be displayed on the form when the record is selected from the listbox.

    A combo box would probably work, but if I had 1000 records, it would be a very long combo box!

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Im using a listbox as I also have a seperate textbox to allow me to search for a record in a field, which will then be displayed in the listbox, which then can be displayed on the form when the record is selected from the listbox.
    Oh, so what you're doing is putting data manually into the text box, then having Access put it from the text box into the list box (using VBA, I assume), and you selecting manually the value in the list box, which is coded to reset the current record. Do I read you right?

    If that's so, you're taking 4 steps to do what can be done in 2 steps. Besides, if you make a mistake entering the ItemID, you will get nothing in return for your double efforts.

    That's why the list box is there, to ensure you select the right ItemID the first time. So what if there are 10 million records? As you enter characters into the list box (or, as I prefer, the combo box), the list automatically completes your entry, so if the right ItemID comes up and you still have 6 characters to enter, all you have to do is hit <Return>.

    In other words, I would get rid of the text box, and just use the list.

    SL

  9. #9
    Join Date
    Aug 2004
    Posts
    364
    The text box filters the list box thats all using a query and a line in the criteria section (Like "*" & [Forms]![frmItem]![text55] & "*"). Then I click a button which re-queries the listbox, its really simple! Thats it.

    All the records are taken from the table record source, access isn't putting them from the text box into the list box, your getting muddled.

    I see no reason to have a combo box when the list box is doing me fine

Posting Permissions

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