FK as if Foreign Key? I have heard of it but never used it so Idk what that is
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
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.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.
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 :|
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.
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.
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.
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.
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.
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.
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