Page 5 of 5 FirstFirst 12345
Results 61 to 71 of 71

Multi Select Listbox parameter for Query

  1. #61
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ItsMe View Post
    It is pretty simple. Use the table provided to you by me and store that table's PK in the relative FK of Table1. The difference being a Normalized DB that is more manageable and a Non-Normalized DB that is difficult to develop Public Interfaces for.
    FK as if Foreign Key? I have heard of it but never used it so Idk what that is

  2. #62
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    I researched it a bit...and here is what i did:
    Created a new field on Table1 (which will be key from the table you provided)
    under realtionships I put the 2 tables down and created a relationship from the PK of your table to this new field on Table 1 I created.
    I don't quite understand how this will help with the being able to filter the form using another listbox though

  3. #63
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    If the appropriate Key value is used in Table1 you can create a query that uses the key value to retrieve the record. Because the Key value is a number, your DB will index the value more efficiently and VBA will be easier to write, especially when you need to concatenate the value in string variables.

    Use a combobox for the user to associate a state to the appropriate records in Table1, storing the PK in the FK of Table1. This will make your cascading comboboxes/listboxes easier to build.

  4. #64
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ItsMe View Post
    If the appropriate Key value is used in Table1 you can create a query that uses the key value to retrieve the record. Because the Key value is a number, your DB will index the value more efficiently and VBA will be easier to write, especially when you need to concatenate the value in string variables.

    Use a combobox for the user to associate a state to the appropriate records in Table1, storing the PK in the FK of Table1. This will make your cascading comboboxes/listboxes easier to build.
    Yea that's how the actual DB is... When entering values the user can only select "state", can't type in him/her self.
    I am just trying to figure out the code now to work with a new listbox. Like how you figured out how to use "state" and which ever state or states are selected only those results are returned on the new form. So now I want to be able to select another thing with the listbox (in this example I chose color). So select something from the two listboxes and only that data is returned in the form.
    I see we use list0 (the listbox for state) and I see u used [state] a lot in your code... So I am wondering how that can be adjusted to fit in another listbox.
    I hope I am being clear :|

  5. #65
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    The association with color can probably be stored in the States table. Again, store a number (Key) value from a table that lists the avaiable colors. Use an FK field in the States table to store the PK from your new "tblStateColors" table.

  6. #66
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Sigh
    IDK what this means....
    store a number (Key) value from a table that lists the avaiable colors. Use an FK field in the States table to store the PK from your new "tblStateColors" table.

  7. #67
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    KBAR12, Primary Keys and Foreign Keys are the essence of what Relational Databases are. They create the association between records from one table and records in another table.

    So for example if you have a Customer with multiple phone numbers, your customer will have a field on the "Customer" table called "ID". This ID field will hold a UNIQUE NUMBER that specifically identifies that particular customer. Then in your PhoneNumbers table you can have a field which holds the value of the customer's ID. That way you know to whom that phone number belongs. The field in the PhoneNumbers table which holds the customer's ID value is called the "Foreign Key" field.

  8. #68
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Ohhh okay thanks for clearing that up!
    I will try to work on the database now that I have a better understanding. I highly doubt I will have it working. But I will try my best.

  9. #69
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I think if you consider using a more "Normalized" approach you will be happier in the long run. Do some research. You will have new questions. Create a new thread. It may be about dependent combos, it may be about PK's and FK's and their data types.

  10. #70
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    I feel like FK is used to eliminate having repeating data on same table. Like in your example, to avoid putting down the customers name again and again with differen phone # you can create a FK from another table and build the relationship...
    That's not quite the scenerio in my DB. The customer name does not repeat for any reason. I am working on a sample DB right now to really understand this. So I have a customer and the state he is from and his color... other customers can be from the same state and have the same color too. So ITS ME has been amazing and figured out how I can search only the customers I want by using states (So which ever states I select... the result will give me all the customers from that state). However now I would like to make a color be a part of it too. So the results should only include the selected States & selected color.

  11. #71
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ItsMe View Post
    I think if you consider using a more "Normalized" approach you will be happier in the long run. Do some research. You will have new questions. Create a new thread. It may be about dependent combos, it may be about PK's and FK's and their data types.
    Yea That's what I will do. Cuz I realized There is more I want to do. I will try to work on it when I get home and prolly either tomorrow or Friday I will create a new thread.
    THANKS SO MUCH FOR YOUR HELP

Page 5 of 5 FirstFirst 12345
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multi-Select Listbox
    By RayMilhon in forum Forms
    Replies: 5
    Last Post: 03-04-2014, 11:54 AM
  2. Search using a multi select listbox
    By noobaccess in forum Access
    Replies: 13
    Last Post: 12-04-2012, 07:06 AM
  3. Replies: 6
    Last Post: 11-02-2012, 12:48 PM
  4. multi select listbox
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 07-28-2012, 01:48 PM
  5. Replies: 11
    Last Post: 09-22-2011, 01:13 PM

Tags for this Thread

Posting Permissions

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