adding records to table from text box values

[Shannan Casteel via AccessMonster.com]

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplace d). The
ClaimNumber is an autonumber and the primary key in both tables. I made a
one to one relationship between the two tables. I have a form for the parts.
It includes 25 text boxes for both the part numbers and the quantities, so 50
total. I set the control sources for each of the part number text boxes to
PartNumber from the PartsReplaced table. I set the control sources for each
of the quantity text boxes to QuantityReplace d from the PartsReplaced table.

When I enter a part number and press tab to enter the quantity, every part
number text box on the page changes to match the first. In other words, if I
enter "1234" in the first box all other 24 text boxes change to read "1234".
The same thing happens for the quantities.

Should I make the relationship one - to - many? Not really sure how to do
this...

I need to be able store up to 25 parts with their respective quantities for
each warranty claim I will have. I will have run reports to tell me which
parts have been entered the most, as well as what parts were replaced for
each respective claim.

Please help...Thanks for your time,

Shannan
--
Message posted via http://www.accessmonster.com

[Phil Stanton]

Not the way to do it I suspect. I would suggest:-

One table for the Claim:- TblClaim - ClaimID, ClaimReference Date, Claimant
etc
One table to list all the parts:- TblParts - PartID, PartNumber,
PartDescription .
One table to link them together:- JoinClaimPart - ClaimID, PartID,
QtyReplaced.
Set the ClaimID and PartID as joint keys with no duplicates allowed. This
will stop a part being entered twice for any claim.

You then have a Main form for the Claim and on it a subform for the Parts
Replaced and quantity replaced. Use a combo box to select the parts
replaced. You might as well set the default quantity to 1. You also need to
set the LinkMasterRecor d and LinkSlaveRecord to PartID

Using a query based on the Parts and JoinClaimPart tables you will be able
to get the statistics.

It will also allow you to use as many or as few parts as you want for each
claim

HTH

Phil
"Shannan Casteel via AccessMonster.c om" <fo***@AccessMo nster.com> wrote in
message news:53******** ***@AccessMonst er.com...

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplace d). The
ClaimNumber is an autonumber and the primary key in both tables. I made a
one to one relationship between the two tables. I have a form for the
parts.
It includes 25 text boxes for both the part numbers and the quantities, so
50
total. I set the control sources for each of the part number text boxes
to
PartNumber from the PartsReplaced table. I set the control sources for
each
of the quantity text boxes to QuantityReplace d from the PartsReplaced
table.

When I enter a part number and press tab to enter the quantity, every part
number text box on the page changes to match the first. In other words,
if I
enter "1234" in the first box all other 24 text boxes change to read
"1234".
The same thing happens for the quantities.

Should I make the relationship one - to - many? Not really sure how to do
this...

I need to be able store up to 25 parts with their respective quantities
for
each warranty claim I will have. I will have run reports to tell me which
parts have been entered the most, as well as what parts were replaced for
each respective claim.

Please help...Thanks for your time,

Shannan
--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable. Also how do you change all the LinkMasterRecor d and
LinkSlaveRecord stuff?

Thanks,
Shannan

Phil Stanton wrote:

Not the way to do it I suspect. I would suggest:-

One table for the Claim:- TblClaim - ClaimID, ClaimReference Date, Claimant
etc
One table to list all the parts:- TblParts - PartID, PartNumber,
PartDescriptio n.
One table to link them together:- JoinClaimPart - ClaimID, PartID,
QtyReplaced.
Set the ClaimID and PartID as joint keys with no duplicates allowed. This
will stop a part being entered twice for any claim.

You then have a Main form for the Claim and on it a subform for the Parts
Replaced and quantity replaced. Use a combo box to select the parts
replaced. You might as well set the default quantity to 1. You also need to
set the LinkMasterRecor d and LinkSlaveRecord to PartID

Using a query based on the Parts and JoinClaimPart tables you will be able
to get the statistics.

It will also allow you to use as many or as few parts as you want for each
claim

HTH

Phil

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplace d). The

[quoted text clipped - 29 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

Also, your probably right., but I'm not a big fan of subforms. I would
rather have the user press a button taking them to another form where they
can enter the part numbers then go back to the main form.

Shannan

Shannan Casteel wrote:

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable. Also how do you change all the LinkMasterRecor d and
LinkSlaveRecor d stuff?

Thanks,
Shannan

Not the way to do it I suspect. I would suggest:-

[quoted text clipped - 27 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Tim Marshall]

Shannan Casteel via AccessMonster.c om wrote:

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable.

Hopefully you have a separate table dealing with parts available and are
not, as it looks from your first post, though I may have read it
incorrectly, combining information into the one table.

Do you have some sort of classification or part type scheme to make
things easier for your users? Like electrical, building materials,
transistors, cleaning supplies, etc, etc. This might be a good idea and
would require another table:

Part_Types

TypeName
TypeId

This would lead to the requirement for a required field in your parts
table that holds the TypeId information.

If so, you can use multiple combo boxes to choose a part type, then an
actual part.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Phil Stanton]

I was just thinking of using a combobox on each line of the subform for
inputting the parts. Didn't realise you had 100,000 possible parts. How on
earth do you get the correct ones? As is mentioned in Tim's posting I should
imagine there are some groupings which can be applied, so that possibly you
would need 2 combo boxes on each line, the first to select the categoy, and
the second would display only those part within that category.

The LinkMasterRecor d and LinkSlaveRecord are a couple of fields in the
subform. If you create the main form, create the subform then in design view
drag the subform onto the mainform, frequently these fields will sort
themselves out. If not there is a builder ( ...) at the side of these fields
and they should offer you the PartID as the link.

The trouble with having boxes for 25 returns is what happens on that
terrible day when you get 26 parts to exchange? Redesign? Not a good idea.

Phil
"Shannan Casteel via AccessMonster.c om" <fo***@AccessMo nster.com> wrote in
message news:53******** ***@AccessMonst er.com...

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the parts? I
have
about 100,000 parts to choose from. I don't think a combo box would be
feasable. Also how do you change all the LinkMasterRecor d and
LinkSlaveRecord stuff?

Thanks,
Shannan

Phil Stanton wrote:

Not the way to do it I suspect. I would suggest:-

One table for the Claim:- TblClaim - ClaimID, ClaimReference Date,
Claimant
etc
One table to list all the parts:- TblParts - PartID, PartNumber,
PartDescripti on.
One table to link them together:- JoinClaimPart - ClaimID, PartID,
QtyReplaced .
Set the ClaimID and PartID as joint keys with no duplicates allowed. This
will stop a part being entered twice for any claim.

You then have a Main form for the Claim and on it a subform for the Parts
Replaced and quantity replaced. Use a combo box to select the parts
replaced. You might as well set the default quantity to 1. You also need
to
set the LinkMasterRecor d and LinkSlaveRecord to PartID

Using a query based on the Parts and JoinClaimPart tables you will be able
to get the statistics.

It will also allow you to use as many or as few parts as you want for each
claim

HTH

Phil

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplace d).
The

[quoted text clipped - 29 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

After reading several replies, I don't think I'm making clear what exactly it
is that I need.
Let me explain...

I have a database for warranty claims. We reimburse according to labor and
parts. For the parts section of the database, all I want is to be able to
enter any number such as "987978A1" along with its quantity, let's say 2 for
this example, and if on another claim someone else lists this same part with
a quantity of 1, I need to see a report saying:

PartNo Quantity
987978A1 3

However, I need to be able to enter more than one part number on each claim.
I have no way of narrowing down parts. Although I've used Access to build a
rather complicated database in the past, I'm no expert, but I would think
this would be rather simple. (A group of text boxes that stores in the same
field in a table.)

I really appreciate all the time everyone has put forth. Thanks for your
help,

Shannan

Tim Marshall wrote:

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable.

Hopefully you have a separate table dealing with parts available and are
not, as it looks from your first post, though I may have read it
incorrectly, combining information into the one table.

Do you have some sort of classification or part type scheme to make
things easier for your users? Like electrical, building materials,
transistors, cleaning supplies, etc, etc. This might be a good idea and
would require another table:

Part_Types

TypeName
TypeId

This would lead to the requirement for a required field in your parts
table that holds the TypeId information.

If so, you can use multiple combo boxes to choose a part type, then an
actual part.

[Shannan Casteel via AccessMonster.com]

If I have 26 parts, I would just split it into 2 claims...bosses suggestion!

Phil Stanton wrote:

I was just thinking of using a combobox on each line of the subform for
inputting the parts. Didn't realise you had 100,000 possible parts. How on
earth do you get the correct ones? As is mentioned in Tim's posting I should
imagine there are some groupings which can be applied, so that possibly you
would need 2 combo boxes on each line, the first to select the categoy, and
the second would display only those part within that category.

The LinkMasterRecor d and LinkSlaveRecord are a couple of fields in the
subform. If you create the main form, create the subform then in design view
drag the subform onto the mainform, frequently these fields will sort
themselves out. If not there is a builder ( ...) at the side of these fields
and they should offer you the PartID as the link.

The trouble with having boxes for 25 returns is what happens on that
terrible day when you get 26 parts to exchange? Redesign? Not a good idea.

Phil

Phil,

[quoted text clipped - 43 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Bob Quintal]

"Shannan Casteel via AccessMonster.c om"
<fo***@AccessMo nster.com> wrote in
news:53******** ***@AccessMonst er.com:

Also, your probably right., but I'm not a big fan of subforms.
I would rather have the user press a button taking them to
another form where they can enter the part numbers then go
back to the main form.

Shannan

Whether or not you are a fan of subforms, they are the right
tool for this application. You could base your main form on a
tab control, with the claim info on one tab, and the subform for
the parts on the other.

And to answer a question you asked previously, your relationship
should be one to many, because you have one claim, and many
parts.

Shannan Casteel wrote:

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the
parts? I have about 100,000 parts to choose from. I don't
think a combo box would be feasable. Also how do you change
all the LinkMasterRecor d and LinkSlaveRecord stuff?

Thanks,
Shannan

Not the way to do it I suspect. I would suggest:-

[quoted text clipped - 27 lines]

Shannan

--
Bob Quintal

PA is y I've altered my email address.

[Phil Stanton]

Sorry for delay in replying, boating this weekend.

So how do you expect to get reliable statistics of parts used if there is no
check that you have entered a valid part number. Can you guarantee to enter
987978A1 and not 987987A1 by mistake. As quite a few replies have
indicated, you will have a main form for the claimant, and each line of the
subform would have a different part number which is valid (Ok I agree you
could key in the incorrect part number, but it would still have to be a
valid one) and the quantity. You could also display the appropriate
description ( pulled from the part number table) for that part number, so
that the person doing the entry has a chance of checking whether it is a
sensible part.
If you also hold the prices on the parts table, all the material sums are
done automatically as well.
The whole thing depends on the JoinClaimPart table which appears
meaningless. What you would see is something like this
ClaimID PartID Qty
1 1476 2 This is claim 1 the 1476th item in the
parts table 2 of them
1 12 1 This is claim 1 the 12th item in the
parts table 1 of them
1 1111 3 This is claim 1 the 1111th item in the
parts table 3 of them
2 12 3 This is claim 2 the 12th item in the
parts table 3 of them
2 1111 2 This is claim 2 the 1111th item in the
parts table 2 of them

The parts used report is based only on the PartID and would look like this
PartID SumQty
12 4
1111 5
1467 2
By using the description and price in the parts table this could be expanded
to
PartID partName SumQty Price Total
12 Nut 4 £1.40 £5.60
1111 Screw 5 £0.70 £3.50
1467 Fan Belt 2 £3.40 £6.80
Total £15.90

And as I said, by having some form of grouping of the parts, this could be
for car warranty work
A similar report would be for Television repair work

Phil


"Shannan Casteel via AccessMonster.c om" <fo***@AccessMo nster.com> wrote in
message news:53******** ***@AccessMonst er.com...

After reading several replies, I don't think I'm making clear what exactly
it
is that I need.
Let me explain...

I have a database for warranty claims. We reimburse according to labor
and
parts. For the parts section of the database, all I want is to be able to
enter any number such as "987978A1" along with its quantity, let's say 2
for
this example, and if on another claim someone else lists this same part
with
a quantity of 1, I need to see a report saying:

PartNo Quantity
987978A1 3

However, I need to be able to enter more than one part number on each
claim.
I have no way of narrowing down parts. Although I've used Access to build
a
rather complicated database in the past, I'm no expert, but I would think
this would be rather simple. (A group of text boxes that stores in the
same
field in a table.)

I really appreciate all the time everyone has put forth. Thanks for your
help,

Shannan

Tim Marshall wrote:

What exactly did you mean by using a combo box to select the parts? I
have
about 100,000 parts to choose from. I don't think a combo box would be
feasable.

Hopefully you have a separate table dealing with parts available and are
not, as it looks from your first post, though I may have read it
incorrectly , combining information into the one table.

Do you have some sort of classification or part type scheme to make
things easier for your users? Like electrical, building materials,
transistors , cleaning supplies, etc, etc. This might be a good idea and
would require another table:

Part_Types

TypeName
TypeId

This would lead to the requirement for a required field in your parts
table that holds the TypeId information.

If so, you can use multiple combo boxes to choose a part type, then an
actual part.