Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post Unanswered: Crazy Listbox Question

    Hello there,

    i was wondering if its possible to populate a listbox ONLY with values from a table that HAVE NOT been selected for other records.

    i guess what i would need to do is somehow check which values are saved in a field and then only display records that are not, so basically selecting a value from the dropdown would save that value to a field AND take that value off the dropdown list.

    is this possible? any ideas would help,

    thanks in advance,

    Jonah

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Crazy Listbox Question

    Originally posted by jonah9
    Hello there,

    i was wondering if its possible to populate a listbox ONLY with values from a table that HAVE NOT been selected for other records.

    i guess what i would need to do is somehow check which values are saved in a field and then only display records that are not, so basically selecting a value from the dropdown would save that value to a field AND take that value off the dropdown list.

    is this possible? any ideas would help,

    thanks in advance,

    Jonah
    Can you give example or illustrate your request with variables?

  3. #3
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post example

    sure.

    a siplified version goes something like this:

    ive got 2 tables and one form.

    table 1 is called "pictures" and is full of picture filenames. its purpose is to populate a dropdown box on my form.
    table 2 is called "links" and it stores the cities and pictures selected from the form.

    what i have on the form is a dropdown box with a list of cities, and i want to make sure i have one unique picture associated with each city. so if a user selects the city "BEIJING" they can choose a picture of "BEIJING" from the picture list.
    BUT
    if the next person also selects "BEIJING" i DONT want them to be able to select the same pictre. SO i want the dropdown to only give them a list of pictures that arent already being used (saved in table "links")

    hope this makes sense. and i appreciate your help,
    sincerely,
    Jonah

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Here is an Example, of two List Box (You can change them later to ComboBoxs), one has car marks, the other has the model of the selected mark. Hope that is what you need.
    Attached Files Attached Files

  5. #5
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post

    hey hammbakka,
    thanks for the reply. that file you gave me was pretty cool, and ill probably be able to use it, but what i wanted to do was something like this:

    In the example you gave me with the car makes and models, imagine that these are cars that im selling, and i only have ONE OF EACH
    model. so if someone selects the Toyota Camry, it will be taken off the list. So the next person who wants to buy a car from me can select the Supra, Celica, Corolla etc, but NOT THE CAMRY. you know what i mean?

    What i was hoping was that there would be some way to save the user's selection to a "cars bought" table, and then the next time someone accesses the form, the dropdown box would check the "cars bought" table and only show cars that ARE NOT on the table. is there some way to do this with a select statement or something?

    thanks for the help, hopefully this makes sense,
    Sincerely,
    Jonah

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Originally posted by jonah9
    hey hammbakka,
    thanks for the reply. that file you gave me was pretty cool, and ill probably be able to use it, but what i wanted to do was something like this:

    In the example you gave me with the car makes and models, imagine that these are cars that im selling, and i only have ONE OF EACH
    model. so if someone selects the Toyota Camry, it will be taken off the list. So the next person who wants to buy a car from me can select the Supra, Celica, Corolla etc, but NOT THE CAMRY. you know what i mean?

    What i was hoping was that there would be some way to save the user's selection to a "cars bought" table, and then the next time someone accesses the form, the dropdown box would check the "cars bought" table and only show cars that ARE NOT on the table. is there some way to do this with a select statement or something?

    thanks for the help, hopefully this makes sense,
    Sincerely,
    Jonah
    Yes, I understand what you mean, to achieve that you should insert a new logical field in the Model table, for example lChosen or some thing like that, then check this field if the user choose that modell of car. And in the listBox of the models you should take in consideration that lChosen

  7. #7
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Talking got it

    thanks again for the reply,

    the problem was not that i didnt know what to do, the problem was that i didnt know how to do it.

    eventually i got it figured out by applying the following statement to the dropdown box:

    SELECT [table1].[field1] FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE [table2].[field1] = [table1].[field1])

    thanks anyway,

    Jonah

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: got it

    Originally posted by jonah9
    thanks again for the reply,

    the problem was not that i didnt know what to do, the problem was that i didnt know how to do it.

    eventually i got it figured out by applying the following statement to the dropdown box:

    SELECT [table1].[field1] FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE [table2].[field1] = [table1].[field1])

    thanks anyway,

    Jonah
    That's fine and great, try also

    SELECT [table1].[field1] FROM table1 WHERE NOT IN (SELECT * FROM table2 WHERE [table2].[field1] = [table1].[field1])

Posting Permissions

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