Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Question Unanswered: ListBox as Record Navigation

    I guess I am missing a posting or something because I know this can be done I just can’t find any examples on how to do this.

    I have a table that stores inventory items and the details about it. I want to create a ListBox that lists all item names and part numbers in the inventory table and bind it to a SubForm to edit the item details (item number, name, price, reorder quantity, manufacturer, disposable, ect.) I have seen it done before but I don't remember where I saw it.

    Is this first of all possible and if so how on earth do you do this?

    -Tony

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could do is have a form with all your part data on
    add a combo box in the form header, or footer with the inforamtion you require
    when a user selects an item in th ecombo box, either apply a filter to the form data, or a filter to the sub form, or changes the record source

    eg "select my,column,list from mytable where mypartno=" & listbox.value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    The short answer is yes. The long answer will have to wait until I get home from work.
    (Of course, there's every chance that someone else will leap in with the correct answer before I get there...)

  4. #4
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Quote Originally Posted by healdem
    what you could do is have a form with all your part data on
    add a combo box in the form header, or footer with the inforamtion you require
    when a user selects an item in th ecombo box, either apply a filter to the form data, or a filter to the sub form, or changes the record source

    eg "select my,column,list from mytable where mypartno=" & listbox.value
    I am actually wanting to list them to the left of the subform in a ListBox but I guess I could do a filter like that at the top. Just not really what I wanted to do.

    I guess if I can't figure out how to use a ListBox a filter like that would work also.
    Last edited by gorf8083; 12-09-08 at 13:39.

  5. #5
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Talking

    Quote Originally Posted by weejas
    The short answer is yes. The long answer will have to wait until I get home from work.
    (Of course, there's every chance that someone else will leap in with the correct answer before I get there...)
    Laugh... now you are starting to sound like me laugh.

    If you could help me out with this it would be MUCH apreciated.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2

    Smile

    You're in luck! I was playing with an idea earlier this year, and I did the exact thing that you're trying to do.

    The form consists of an unbound listbox, and as many controls of the appropriate types as required for the field in your inventory table. The control source for the listbox is a query that returns the ID code and description of all inventory items, and the multi property needs to be disabled. You then put code on the "Click" event of the listbox, which looks up the selected value in the inventory table and populates the controls accordingly. If you have other controls like command buttons, you can also enable or disable them according to certain criteria (for example, only enable cmdDelete if there is no stock of the item selected).

    Hope this helps!

  7. #7
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Question

    Quote Originally Posted by weejas
    You're in luck! I was playing with an idea earlier this year, and I did the exact thing that you're trying to do.

    The form consists of an unbound listbox, and as many controls of the appropriate types as required for the field in your inventory table. The control source for the listbox is a query that returns the ID code and description of all inventory items, and the multi property needs to be disabled.
    I figured you needed to use am unbound ListBox for the control and that with that used a query for the Item Names.

    My thoughts were, and let me know if I am wrong, create a query that lists the Item Table Primary Key (auto-numbering record source just as a point of reference) and the Item Names. Then use a subform that is bound from the Primary Key Value of the query we just created to the Primary Key Value in the actual Item Table. My problem is actually getting the two to link up. I will play with it again and see if I can get it to work.

    Quote Originally Posted by weejas
    You then put code on the "Click" event of the listbox, which looks up the selected value in the inventory table and populates the controls accordingly. If you have other controls like command buttons, you can also enable or disable them according to certain criteria (for example, only enable cmdDelete if there is no stock of the item selected).

    Hope this helps!
    If you are doing a lookup using the "On Click" Event and populating unbound fields, how do you get the information back from the unbound fields to the original table? I could do a SQL query for a button labeled "Save Changes" which might not be a bad Idea anyway because if you use a subform, once you change the item information its changed. At least with a SQL query back to the original table they have a little bit of time to think about what they have changed before the changes are committed.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Ah, now you're asking a slightly different question!

    My form has a hidden label on it. The caption is set when the form displays a new set of data to "Clean". Every editable control has on its OnChange event a line of code that sets the label's caption to "Dirty".

    The Save button performs a series of checks - one for each control. In each case, it compares the value in the control with the value in the base table. If they match, it stops. If not, it creates a new record in the audit file and then updates the table. Then it resets the form.

    As time goes by, you'll start using bound controls less and less!

  9. #9
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Talking

    Quote Originally Posted by weejas
    Ah, now you're asking a slightly different question!
    No not really, still the same problem just thinking of different ways to come to the same conclusion. I think I am going to look more into the unbound fields and after I get it done I will go ahead and upload an example oh how I did it, so you can grade my work.

    Quote Originally Posted by weejas
    My form has a hidden label on it. The caption is set when the form displays a new set of data to "Clean". Every editable control has on its OnChange event a line of code that sets the label's caption to "Dirty".

    The Save button performs a series of checks - one for each control. In each case, it compares the value in the control with the value in the base table. If they match, it stops. If not, it creates a new record in the audit file and then updates the table. Then it resets the form.

    As time goes by, you'll start using bound controls less and less!
    I have already noticed that I have been useing less and less bound fields more and more. I also have Found that I am useing multi piece databases (front end and back end) to store data. and I am getting to be a master of the DLookUp Command. Laugh... its funny how we evolve from one thing to another. I am glad our Finance Comptroller Switched the whole project on me because it forced me to rethink the whole project to something more simple yet a lot more powerfull.

    They really dont pay me enough to do this... my title it technically Computer Tech Level I.

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    *Rereads original question*

    Oops. I missed the bit about editing the records once you'd found them!

    I'd be happy to look at what you've done. Can't promise to offer much in the way of grading, though.

Posting Permissions

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