Slecte Multiple Records and Update

[showri]

Select Multiple Records and Update

Hi,

Can anyone help me with the following Question?

I have a table which holds different policies. Every policy has an associated sales person with it.

The scenario is like this:

If an existing sales person leaves and if we get a new sales person ( in the place of old sales person) all the corresponding records have to be updated.
The catch: There should be an option to select multiple records (which are not continous) and update them with the new sales person. All the records with the old sales person can be updated with the new sales person or only some of them will be updated.

I need help in building a form which captures these scenarios.

I do have a query which asks the for the old sales person ID to pull out the necessary records to be updated.

thanks

 

[vbaInet]

It would be a daunting task if one sales person has thousands of policies linked to them, or what's the maximum number they can have?

Also, how is the Sales Person's ID generated, Auto Number, manually fed number, or a string that contains part of the Sales person's name?

 

[showri]

Re: Select Multiple Records and Update

At the Max there will be around 2000 policies, so I think it should not be a big problem to update.

In the table the sales ID for each policy is stored manually (when a new policy is created). There is a seperate table which has the ID, The first name and Last name of the sales person.

 

[vbaInet]

That's not too bad then, you can use a multi-select listbox for this or if you're familiar with the listview control, it's great for this.

Is the ID of a Sales Person a number? Autonumber for example?

 

[showri]

The Sales ID is a Number.

Can you give me the steps as to how to design this kind of a form.

Things I am looking for are:

A form that display the records based on the query crteria.

User can select the records that needs to be updated (how to do this?)

An update query to update the sales ID field name for the selected records.

 

[vbaInet]

I can only give you the ideas and answer specific questions. I don't think I will have enough time to relay a step-by-step guide.

The question I asked wasn't answered. There's a reason why that question was asked?

 

[showri]

It is a Number and is entered manually (Not an auto number)

 

[vbaInet]

Ah, good.

For what you want to achieve you need to have enforced referential integrity with Cascade Delete applied as well. Have you got that set already?

So what's the current scenario, a Sales person joins and you add a NEW record for that Sales Person?