update record with button

[LucasH]

Hi all,

Kind of a novice here in vb but here is what I am trying to do. I have a child table of say, sales territories, that I want to assign to sales reps. Sales reps can have multiple territories but territories cannot be owned by more than one sales rep at the time. And I want to be able to view the distribution of territories at any time. So I have a parent table of sales reps and a child table of sales territories.

I created a form for the reps and a subform to display any territories assigned to the reps. But I am having trouble assigning new territories or un-assigning territories.

If I delete the territory record from the subform, that deletes the entire record from the territory table, where I only want to make it unassigned at the point so I can assign it to a different rep.

To assign a territory I tried to create a combo box to display all the territories so I can pick one I want to assign to the given rep. But that doesn't seem to work as well. I get an error when I try to pick a territory from the combo box.

It seems to me this is a fairly common inventory puzzle that I just can't seem to figure out.

 

[GOstlund]

What you need is a third table to link your many-to-many relationship. The table need only have two columns, one referenced to the sales rep, and the other referenced to the territory. The table will have a One-to-Many relationship from the sales rep table, and another One-to-Many relationship from the territory table. If you set the cascade options then it will also handle most of your integrity issues. Then you can add/delete entries in that table at will, without deleting the territory or the rep, but if you delete a rep, or a territory, it will delete the link from that table for you.


----------
HTH
Gavin Ostlund

 

[LucasH]

Gavin,

That sounds like it may work. Can you explain it a little more. I have a third table, storing only SalesRepID and TerrID, correct? Then what do I display the territory on a subform? Do I create a query or something that displays the territory details based on the third table? I still don't understand how if I delete a territory from the subform, how it won't delete the entire territory record. Hence, my query thought. I don't know, I am confused

 

[GOstlund]

The subform uses the third table as it's record source, and then you just link the appropriate master and child fields. To do this in design view, make sure you're editting the properties of 'Subform/Subreport: SUBFORM_NAME_HERE' and that'll show you the link fields properties.


----------
HTH
Gavin Ostlund

 

[LucasH]

Alright, I think I got it working, kind of. I can definitely delete records now and it doesn't delete the actualy territory. But now I want to add a territory. I would like to display a combo list of all the territories and assign one of them to a rep. Do I base the combo list on the same query the subform is based on?

 

[GOstlund]

Well, you can set the properties of the columns in the third table (henceforth called the 'link table') so that their values are taken from combo boxes which are linked to the appropriate fields in the other tables and you can force it to limit to the list.
To do so, open the link table in design view and click the 'Lookup' tab, this will usually say 'Text box' by default, now just change that to 'Combo box' and a whole whack of options will appear. You can leave the source type alone, but we'll change the actual row source to the table which corresponds to the field we're editting. Then chose the appropriate field number (not zero based btw) and you'll get a drop down list when you go to enter data into the table. Repeat the process for the second field in the link table.
Now, to get this to show up right on the actual form/subform, you'll probably need to delete the text box controls and replace them with combo boxes, set the appropriate fields correctly and you should be in fine shape.

----------
HTH
Gavin Ostlund

 

[LucasH]

Great, Thanks Gavin. I think I am looking pretty good, here.

My last problem is wondering how to limit it so that a territory can only be assigned once. The way I have it working now, I can pick from a list of the territories just fine, and when I delete it, I am only deleting the link, but I am able to add the same territory to multiple reps. The link table just keeps growing and growing.

 

[GOstlund]

Glad things are working out for you there! Now, in your link table, I imagine you've got it set so either both or none of the fields are primary keys. The former would be fine, and is most common in this type of implimentation (bridge of a many-to-many relationship). Since you need to limit your territories to only be assigned once, there are two ways to do this, firstly you can set just the territory field to be primary key and this will enforce it through simple unique PK rules. Second option is to open the table in design view again, and on the 'General' tab, set 'Indexed' to 'Yes (No Duplicates)' and that should also work. Which way is best, or what are the pros/cons of either choice? Couldn't tell you, but I am confident that either of those methods will meet your needs!


----------
HTH
Gavin Ostlund