copy selected records on subform

  • Thread starter Thread starter Diana Criscione
  • Start date Start date
D

Diana Criscione

I have a subform that has records detailing special
customer pricing for parts. Each customer may have more
than one branch. My client would like to be able
to "mark" multiple pricing records in order to copy those
records to all of the other branches. This saves him
from having to enter these special prices for each
branch. I have code to copy the *current* record to all
of the other branches but am having trouble figuring out
how to provide a way for him to "mark" (say with a
checkbox) multiple records and then copy all of them to
the other branches.

Any suggestions?

Thanks in advance!
Diana Criscione
 
I have a subform that has records detailing special
customer pricing for parts. Each customer may have more
than one branch. My client would like to be able
to "mark" multiple pricing records in order to copy those
records to all of the other branches. This saves him
from having to enter these special prices for each
branch. I have code to copy the *current* record to all
of the other branches but am having trouble figuring out
how to provide a way for him to "mark" (say with a
checkbox) multiple records and then copy all of them to
the other branches.
Click to expand...

The simplest would be to just add a yes/no field Marked in the pricing
table. Run a Query selecting on that field to update the branch ID or
to create an Append query to move the records (though the need to do
so would suggest an incorrect table design!)
 
-----Original Message-----


The simplest would be to just add a yes/no field Marked in the pricing
table. Run a Query selecting on that field to update the branch ID or
to create an Append query to move the records (though the need to do
so would suggest an incorrect table design!)



.
Click to expand...
Thanks John. I think that will work for me. I'll just
grab the Marked pricing records, then append the same
records to the pricing table, modifying the BranchID to
the other branchIDs. I'll end it all with an update to
set all marked records to No. Cool...and easy.

I appreciate your help!

Diana
 
Diana Criscione said:
Thanks John. I think that will work for me. I'll just
grab the Marked pricing records, then append the same
records to the pricing table, modifying the BranchID to
the other branchIDs. I'll end it all with an update to
set all marked records to No. Cool...and easy.

I appreciate your help!

Diana
Click to expand...

Diana, like John says, could what your explaining be dealt with by using a
many-to-many relationship ??

Neil.
 
-----Original Message-----



Diana, like John says, could what your explaining be dealt with by using a
many-to-many relationship ??

Neil.
Click to expand...
Neil,

That would probably be a better design BUT that's the
only way I could figure out how to handle it. This
project in particular has plenty of other issues. Not
only am I converting 10 years of data from a Paradox 7
database (that is a normalization nightmare) but I've got
a client who always wants to be able to "tweek" here and
there. (including having some design control when I'm
done so he can modify forms if he wants to) Having the
one-to-many makes it easier on me when he wants to modify
one of those "copied" records to something just a little
different. For example, add a dollar to the price or
change the quantity break. As it is, he creates new
parts records by copying a part record that is similar
and then modifying the desc, etc. I wrote code to copy
the record, get the new Part Number, and then append the
record. I've had to create alot of magic in the back to
do things the way he wants them done.

Perhaps the way I'm doing this is not the best way, but
sometimes I have such a mental block on this project
because of client demands, conversion issues, and lack of
sleep. I've been working on this project for a year and
a half because the client can only afford to pay me for 6-
10 hrs of work per week -- sometimes it takes me an hour
just to figure out what I was doing the last time I had
the db open!

Any suggestions you have would be greatly appreciated.
I'm just ready to finish the project and move on.

Diana
 
Diana Criscione said:
Neil,

That would probably be a better design BUT that's the
only way I could figure out how to handle it. This
project in particular has plenty of other issues. Not
only am I converting 10 years of data from a Paradox 7
database (that is a normalization nightmare) but I've got
a client who always wants to be able to "tweek" here and
there. (including having some design control when I'm
done so he can modify forms if he wants to) Having the
one-to-many makes it easier on me when he wants to modify
one of those "copied" records to something just a little
different. For example, add a dollar to the price or
change the quantity break. As it is, he creates new
parts records by copying a part record that is similar
and then modifying the desc, etc. I wrote code to copy
the record, get the new Part Number, and then append the
record. I've had to create alot of magic in the back to
do things the way he wants them done.

Perhaps the way I'm doing this is not the best way, but
sometimes I have such a mental block on this project
because of client demands, conversion issues, and lack of
sleep. I've been working on this project for a year and
a half because the client can only afford to pay me for 6-
10 hrs of work per week -- sometimes it takes me an hour
just to figure out what I was doing the last time I had
the db open!

Any suggestions you have would be greatly appreciated.
I'm just ready to finish the project and move on.

Diana
Click to expand...

Hello Diana,

I know exactly how that feels. Change this, do that, make it land my car on
the moon :-).

The way that a many to many relationship for you would work is you would
have a form with all the customers in. There would then be a subform on the
customer form which holds all the pricing information. You would then
typically have a drop down combobox for all prices availabe for the user to
link to the customer. The way access knows which customer has which prices
is because a 3rd table would be involved that would hold all the linking
information (and if set up right is handled by Access). All you would do to
duplicate records is to add records to this table. If the boss wants to
change a price then you would copy a price record and then change this as
normal. It's hard to explain, one of those things where you have to see it
working instead of having someone explain to you in writing. If your
interested in finding out more info, then i found the below mentioned
website and there is a working example of DB with a many-to-many
relationship in there.

http://www.microsoft-accesssolutions.co.uk/many_to_many_example.htm

However, if it aint broke and all that, then i'm sure it might be best to
leave it the way the boss is use to. Might of just come in handy tho to
implement the next thing the boss wants to do...:-)

Good luck,

Neil.
 
-----Original Message-----



Hello Diana,

I know exactly how that feels. Change this, do that, make it land my car on
the moon :-).

The way that a many to many relationship for you would work is you would
have a form with all the customers in. There would then be a subform on the
customer form which holds all the pricing information. You would then
typically have a drop down combobox for all prices availabe for the user to
link to the customer. The way access knows which customer has which prices
is because a 3rd table would be involved that would hold all the linking
information (and if set up right is handled by Access). All you would do to
duplicate records is to add records to this table. If the boss wants to
change a price then you would copy a price record and then change this as
normal. It's hard to explain, one of those things where you have to see it
working instead of having someone explain to you in writing. If your
interested in finding out more info, then i found the below mentioned
website and there is a working example of DB with a many- to-many
relationship in there.

http://www.microsoft- accesssolutions.co.uk/many_to_many_example.htm

However, if it aint broke and all that, then i'm sure it might be best to
leave it the way the boss is use to. Might of just come in handy tho to
implement the next thing the boss wants to do...:-)

Good luck,

Neil.
Click to expand...
Neil,

Thanks for the tip! I have done forms like you're
describing. However, I'll have to leave this the way it
is. My client has too many special prices -- wait,
that's not technically correct -- he has more special
prices than he's willing to scroll through in a combo
box. I have had to do so many things so he won't have to
scroll. (and don't tell him he could just type a "W" to
get to the W's) I've had to add a "sort order" field to
a dozen tables so he can sort the items in a combo box
the way he wants so what he "normally" chooses is at the
top and he doesn't have to scroll. Yeah. Believe it or
not.

So again, thanks for the tip! Pray for me!!

Diana
 
Back
Top