Results 1 to 12 of 12
  1. #1
    Solway is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6

    Help. need to setup a form to query database and produce a output list.

    Hi guys



    i've especially signed up to the forums in hope to get help.
    The last time i have touched microsoft access design was back in 1999 and was just a basic forms and querying the database.

    I'm completely out of my depth, and hope come kind soul could help please


    The story.
    I have a some very old custom software that uses a SQL database, however it is soooooooooo outdated, i cant get it to run on windows 10 (and i dont want to go into the trouble of VMs).
    I only use the software as a "lookup" system to find a item in a database, then i copy the text outputs into excel/word for quick templating of documents.
    Because i cant use this software on any modern OS, and that i dont use the software for what it is anyway. i have extracted the SQL database into a access file. I was thinking that i could just easily use access for the same result.

    The solution that i need

    I need to create some sort of form, that queries the database and when i select a item in a list, it adds it to another list, then updates the first list.

    The database is like 50,000 entries, and has ID fields and description fields etc.

    Basically, i need two lists. 1st list shows the first group of items, when a item is double clicked on the 1st list, it moves to the second list. and the 1st list updates with the relevant nextgroup item that corresponds to it.

    So i can try and explain what i mean. ive tried to simulate a example in this picture attached based on fruit!

    Click image for larger version. 

Name:	example.png 
Views:	35 
Size:	30.1 KB 
ID:	28215


    I hope that tries to explain what i mean. then i can try and expand on it.

    Any help much appreciated

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The database is like 50,000 entries, and has ID fields and description fields etc.
    How many tables
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What's the goal? You're asking for data to be listed, then after a move, entirely change the underlying data of a parent list, rinse & repeat.
    In the end, you have stitched together some list, which by itself is not real useful. Your data seems to be in columns and isn't normalized at all. Or is this one of those cases where the posted data and/or outcome doesn't come close to reflecting the real thing?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is typical of a cascading combo box setup. Or cascading list boxes, either one will do what you want.

    Simple Example

    Solway.zip

  5. #5
    Solway is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    thanks for the quick replies guys.

    the database is 1 table, and similar to the example i provided.

    I'll try a give you a demo of how the software does things.


    @rpeare thats a good example, however i need a combolist sort of thing that will also scroll.


    let me try and prepare a short video from the software, just need to get it to work on a vm.

  6. #6
    Solway is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    heres a video of the software
    https://www.dropbox.com/s/6y4tyi0keh...ample.avi?dl=0

    as you can see, when i double click the list on the left. it'll be put into the list on the right.
    and i can select and copy that list.

    then if i double click on the list on the right it'll refresh the left list, allowing me to change/undo etc.

    The database is pretty the same layout as per the fruits example lol

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There is essentially no difference between a combo box and a list box, you could convert the combo boxes to list boxes (right click change to list box) and it would work the same. Combo boxes are also 'scrollable' after you hit the down arrow.

  8. #8
    Solway is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    Quote Originally Posted by rpeare View Post
    There is essentially no difference between a combo box and a list box, you could convert the combo boxes to list boxes (right click change to list box) and it would work the same. Combo boxes are also 'scrollable' after you hit the down arrow.
    Yea i have been fiddling to try and edit your file.

    I hate asking for help, but without learning to code, i'm getting no where.


    I could do with double clicking a item in a list , would refresh the boxes instead of the "on exit"

    and the output list need to be on separate lines.
    aka.
    Fruit
    banana
    etc
    etc


    is there anyway of making it like whats in the video i uploaded?


    another thing... is there a limit to the number of characters in a list box, per line? some descriptions will be quite large


    the problem with too many list boxes takes up lots of room. and some items can have different number of related "layers".


    your help is much appreciated!!!!!!!!!!!!!!

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    just move the 'on exit' code to the 'double click' event

    To force new rows on the cut and paste text box just use & vbcrlf & between each of the four variables

  10. #10
    Solway is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    Quote Originally Posted by rpeare View Post
    just move the 'on exit' code to the 'double click' event

    To force new rows on the cut and paste text box just use & vbcrlf & between each of the four variables
    Sorry i have been trying however cant get it to work. endless errors etc when i do it.

    i converted to list boxes, and it works with the existing "on exit"

    but when i try the "on double click" i get errors.


    and i could do with the output box to be a list box that i can copy all rows.

  11. #11
    Solway is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    ive tweaked it slightly, however cant get it to do the "double click" instead of "on exit", as well as adding a additional box doesn't work right.
    Solway rev1.zip

    However the problem with the database, is there some descriptions are long, and therefore all lists and outputs needs to be word wrapped

    from a endless google search it looks like "listboxes" cant do this. and that i need something similar.

    one result said about MS flexigrid????

    Can anyone help, im out my depth here.


    I need it similar to how rpeare showed me, but with the following:


    • the list boxes need to refresh on a double click of a item in the list, therefore a user can click through to find the right "path"
    • the list boxes need to be word wrapped on the text, and therefore expands to fit said value (user need to see all of the text, this is important)
    • the final "output" box needs to a grid/list style and all the items can be copied and pasted into excel (separate cells)


    every google seems to point me to a flexi grid style??

  12. #12
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have no experience with the flex grid control, but am quite certain that a list box column cannot line wrap one row. If this is so vital, you'll need to rethink how you display the output and the end result desired. Several questions, like why don't you just dump the results in a table for copying or just send them straight to Excel? That tells me you don't really care how they end up looking in Access since you're not going to deal with them there anyway. What seems vital is the user can see the assembled data, so why not just use a continuous subform with textboxes that can grow (vertically)? Is Access really the right tool for the job? Textboxes will wrap when the line doesn't fit. I think what you describe is not possible with the standard controls and there's no way to tell if the wrapped lines in your video are stored differently than what's being shown, or if they're being constructed with line breaks in code. A table can hold a line wrap, so you might achieve what you want if you store the values so that the width of the table field suits the control column width and line wrap in the table (Ctrl + Enter). It seems I was able to effect the changes with a double click, so I don't understand what your problem is there. FWIW, I prefer the exit. Why require a double click when exiting works just fine and is less clicking?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-15-2016, 10:05 AM
  2. Replies: 8
    Last Post: 09-15-2015, 05:37 AM
  3. Replies: 6
    Last Post: 08-27-2015, 01:06 PM
  4. Output query to list box
    By shabbaranks in forum Programming
    Replies: 4
    Last Post: 02-08-2012, 09:53 AM
  5. Ho do I output query to a list box
    By shabbaranks in forum Queries
    Replies: 1
    Last Post: 02-06-2012, 07:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums