Using a combo box to select multiple records on a form

[reverze]

No reason to apologize, as I really appreciate your time in attempting to help me solve this problem.

I'll check back periodically and see if you have any more ideas.



Thanks again

[Rawb]

Okey, here ya go.

Turns out the problem was fairly simple. You were directly accessing the Table from the Form instead of how I usually do it. This isn't the wrong way, it's just now how I typically do things so I don't tend to think like this.

Anywho, here's the "fixed" version. Since you're opening the Records directly, you have to Clone them and then Filter the Clone. If we would have continued trying to do it my way, we would have been REPLACING the current Record with whatever we changed the Form's fields to (which is bad!).

[reverze]

Ah, great...Thank you so much! I really appreciate your help!

I'm about to take a break for the day, so I'm going to do some more work tomorrow. I will keep this thread open incase I see any other problems I may have.

Thanks again... and I really appreciate your help!

[reverze]

PS. my thing for tomorrow is to add a "New" and "Save" button and a few other things...so that the user can add new entries by using the form...and save directly on the form etc...

So I'll be brushing it up tomorrow and adding stuff to my actual DB I've been working on.

I'm sure I'll be back.

Thank you so much again, I appreciate the help so much!

[Rawb]

I'll be here!

[reverze]

I'm back!

Ok, so I decided that I need to go a little bit more complicated. And now I'm really stuck on how to do this.

Let me explain:

I have two tables total. One table contains all of my data (Name,Leadership,Description,etc.) and the other table contains only a primary key and a year.

On my form, I have two combo boxes.

The first one is where you select the year and the second is where you select the name of entity.

So basically, you see what I'm trying to do here. The selections will depend on each other in order to display the correct data.

I know it isn't possible to have two primary keys in Access so I'm not quite sure how to go about doing this or if it is even possible.

I'm just looking for a suggestion on if it is possible and how I could go about doing it.

As always, I appreciate the help.

[Rawb]

I'm not sure I understand what you want. What exactly is the second table for and how does it relate to the first one (and the Form, I'm assuming we're working on the same one as earlier)?

[reverze]

I'm not sure I understand what you want. What exactly is the second table for and how does it relate to the first one (and the Form, I'm assuming we're working on the same one as earlier)?

Basically the second table only has years in it.

Right now I just have data in the main table for one year.

I want to have it so I can have different data for different years.

So the name won't change, but the description could.

So for 2009 for EntityA...the description could say: "blah"

2010 for EntityA... description: "woo"

Hope that makes sense? Not sure how to explain this very well so I apologize...

[Rawb]

I think I know what you're looking for, but I'm still a little foggy on your Tables and Form setup.

How do you record the different descriptions? Is there a Record for each Name for each year (Does "Mr. Guy" have two Records, one for 2009 and one for 2010?) or is the description controlled by how long they've been there (If they've been there for 1 year, they get DescriptionA, 2 years = DescriptionB, etc.)?

[reverze]

If it were by person, they would have a different description each year which will be pulled for the table. So yes, they will have different records for each year.

So for example, you go and click "John Smith" and then you go and click year "2009"

For description it sais "he was lazy."

You go back up, and select "2010" and it reads: "he worked harder"

Am I making any sense? I'm trying, so I apologize lol

[Rawb]

OK, I think I have all the information I need now.

However, while we can do it this way, it would lead to a lot of unnecessary extra data. I'd recommend turning your year Table into a Description Table.

Add 1 Memo/Text field. Call it "desc" or something.
Change the Primary Key from just your autonumber ID field to your ID field AND the year.
Create a one to Many relationship between your data Table's ID field and this Table's ID field (so this Table can have multiple instances of the same person's ID from the data Table).

Then you can get rid of the description field in your main data Table and use the person's ID & the selected year to search through the Description Table and get the description that way. This way you'll only need 1 Record in your main data table for each person but you can still have multiple descriptions per person based on the selected year.

Let me know which way you wanna do it and then we can get started!

[reverze]

OK, I think I have all the information I need now.

However, while we can do it this way, it would lead to a lot of unnecessary extra data. I'd recommend turning your year Table into a Description Table.

Add 1 Memo/Text field. Call it "desc" or something.
Change the Primary Key from just your autonumber ID field to your ID field AND the year.
Create a one to Many relationship between your data Table's ID field and this Table's ID field (so this Table can have multiple instances of the same person's ID from the data Table).

Then you can get rid of the description field in your main data Table and use the person's ID & the selected year to search through the Description Table and get the description that way. This way you'll only need 1 Record in your main data table for each person but you can still have multiple descriptions per person based on the selected year.

Let me know which way you wanna do it and then we can get started!

When I was saying description, I was only giving that as an example. This could be a problem. I have about 20 other rows that also need populated other than just description.

Perhaps I could just create a different table for each year with all of the different data in it.

How would I make the combo box know where to pull the data from though? Agh this seems difficult. Hmm having trouble thinking today too.

Would that work?

[Rawb]

Well then. . .

There are really two ways to do this. The first is to have a Table for each field (a descriptions table, a pay Table, etc. You generally don't want to make tables based on dates though, because that means you'd need to continually add Tables and change your Queries). Each table would have the necessary "reference" fields (in this case, the person's ID number and a year) and then the field that could contain the unique data.

Example:

Description Table
PersonID  Year  Description
1         2008  OMG he's lazy!
1         2009  Significant improvement from last year.
1         2010  He's on the fast track to management!

Pay Table
PersonID  Year  Pay
1         2008  $5.75
1         2009  $6.75
1         2010  $9.00

The second is to have an "updates" Table that can contain any of the fields that need to be updated. In this scenario, you'd have a single table with three fields: a reference field (in this case though, you'd need two reference fields), a "key" field that holds the name of the data Tables field that we're changing, and then a value field where we store the data itself.

Example:

Updates Table
PersonID  Year  FieldName    FieldValue
1         2008  Description  OMG He's Lazy!
1         2008  Pay          $5.75
1         2009  Description  Significant improvement from last year.
1         2009  Pay          $6.75
1         2010  Description  He's on the fast track to management!
1         2009  Pay          $9.00

Personally I prefer the second method, even though it's a little more complex to code around. It keeps everything in a single table. The downside is that, if one of your fields is a Memo field, you either need to make a separate table for just that field, or make ALL your updates Memos (which wastes a lot of space).

[reverze]

Great, thank you so much!

I'm going to work on this and see how it goes. I'm sure I will be back later today or tomorrow.

Thank you again for your help!

[reverze]

New question while I am thinking about it.

Is there a way to have a button that will create a report depending on what is currently selected in the combo box?

I'm looking now at the wizard for the button and it only shows "Open Report"...

Is that possible?