Getting three unique records from one form page

[leavandor]

I have been working at my company for over a year now using a 6 year
old Access system. We are a Roll-Wrap Manufacturing Warehouse (we make
paper that covers other non consumer paper, the huge 3 ton rolls).
Currently I'm in the process of migrating the entire system into a
single file (was about four seaparate MDBs, all of which were closely
tied to one another) and putting the data on an MSDE server.

First, I'd like to know if anyone agrees that the above is a good move.
Consolidating the forms/reports into one adp file, and having them all
reference the same MSDE DB.

Second, I'm currently stuck on a problem I'm having with trying to get
one of these old forms to do what I want. The form itself is your
standard entry type deal, and its there to record the intricate details
of our finished product that our accounting system cannot. Basically,
you enter a Parent Roll ID first and then enter anywhere from 1-3 Child
Rolls that the original was made into.

Each child roll has 10 fields of information. The way the database
currently handles this task is by having 30 different fields in the
same table, 10 for each of the three possible rolls. Now, this is as
you can see, a bit redunandant for what it needs to be. What I want to
do to is use something similar to the form we currently have, but each
of the three child rolls goes into its own database.

Now, I've looked into just simply creating self-validating code that
would UPDATE any new child rolls table with however many were made from
the parent. But knowing my coding skills, I would be able to do it but
there would be something I would forget that would later cause problems
later on. Maybe not, I haven't gone that far yet, but you know the
deal.

Regardless, I was curious if anyone else had done something like this
before. I've tried nested forms inside the form, but it doesn't work
out too well considering that each of the three nested forms would be
calling onto the same database. That, and the fact that those who use
the form are addicted to being able to copy/paste the last record for
less data entry, and you can't do that with nested forms.

Thanks in advance for any help, and please shoot any clarification
questions my way, I can suck at explaining things properly.

 

[leavandor]

Sorry, it's early. I meant INSERT, not UPDATE. That could be
confusing.

 

[Chaim]

Comments inline.

I have been working at my company for over a year now using a 6 year
old Access system. We are a Roll-Wrap Manufacturing Warehouse (we make
paper that covers other non consumer paper, the huge 3 ton rolls).
Currently I'm in the process of migrating the entire system into a
single file (was about four seaparate MDBs, all of which were closely
tied to one another) and putting the data on an MSDE server.

First, I'd like to know if anyone agrees that the above is a good move.
Consolidating the forms/reports into one adp file, and having them all
reference the same MSDE DB.

Sorry, no experience with MSDE. But I'm sure someone will have a solid
opinion to express
about this.

Second, I'm currently stuck on a problem I'm having with trying to get
one of these old forms to do what I want. The form itself is your
standard entry type deal, and its there to record the intricate details
of our finished product that our accounting system cannot. Basically,
you enter a Parent Roll ID first and then enter anywhere from 1-3 Child
Rolls that the original was made into.

Do each of the Child rolls get a separate unique child ID? Or can one be
created by adding 1, 2, or 3 to the Parent Roll ID?

Each child roll has 10 fields of information. The way the database
currently handles this task is by having 30 different fields in the
same table, 10 for each of the three possible rolls. Now, this is as
you can see, a bit redunandant for what it needs to be. What I want to
do to is use something similar to the form we currently have, but each
of the three child rolls goes into its own database.

Do you mean database or table? And why, if you can determine which of the
Child Rolls you're talking about, would you not have a ChildRoll table and
differentiate them based on Child ID?
ParentRoll table --- 1::N ---> ChildRoll table
(ParentID PK) ([(ParentID FK, ChildID (1-3)] PK)

Now, I've looked into just simply creating self-validating code that

What do oyou mean by 'self-validating'?

would UPDATE any new child rolls table with however many were made from
the parent. But knowing my coding skills, I would be able to do it but
there would be something I would forget that would later cause problems
later on. Maybe not, I haven't gone that far yet, but you know the
deal.

Regardless, I was curious if anyone else had done something like this
before. I've tried nested forms inside the form, but it doesn't work
out too well considering that each of the three nested forms would be
calling onto the same database. That, and the fact that those who use
the form are addicted to being able to copy/paste the last record for
less data entry, and you can't do that with nested forms.

I'm not sure I follow this part. Normally to model a 1 to many you use a
form to capture the parent/1-side of the relationship and a subform to
capture the 'many' side. If the (up to three) child rolls need for some
reason to be separated, you could use a tab control and put the subforms
there.

 

[leavandor]

Do each of the Child rolls get a separate unique child ID? Or can one be
created by adding 1, 2, or 3 to the Parent Roll ID?

That's how they work now, and I actually have already gone as far as
creating an AfterUpdate function on the Parent Roll ID field that will
enter X number of child IDs (X is equal to the number of fields that
already contain prefilled Customer info, these end up as labels in the
end). And that function basically takes the Parent ID and changes the
third to last letter to B, C, or D (Parent uses the 'A' identifier).

Do you mean database or table? And why, if you can determine which of the
Child Rolls you're talking about, would you not have a ChildRoll table and
differentiate them based on Child ID?
ParentRoll table --- 1::N ---> ChildRoll table
(ParentID PK) ([(ParentID FK, ChildID (1-3)] PK)

Yeah, a table. It was too early then.

The plan is to create a new table which contains these child rolls,
instead of having them in the same table with the rest of the data.
I've already created Insert queries to build that table when I want,
but I have to manually do it before I want to work with any updated
data on that table. What I'm trying to do is eliminate that step and
have it work all behind the scenes, instead of relying on my hacks to
this old database. And hopefully learn a lot in the process.

What do oyou mean by 'self-validating'?

I mean some work in VBA that would essentially update the child table
every time the form's record was changed, and insert those new rolls
into it. Then, call back on that table based on some as-of-yet
undetermined linking when they need referenced again. That was my
original idea, but I wasn't sure if I was missing some inherint feature
of Access or I'm totally going about it the wrong way.

I'm not sure I follow this part. Normally to model a 1 to many you use a
form to capture the parent/1-side of the relationship and a subform to
capture the 'many' side. If the (up to three) child rolls need for some
reason to be separated, you could use a tab control and put the subforms
there.

The main problem I've encountered with using subforms is that it
eliminates the ability for the user to cut and paste the previous roll
information when adding a new record. That's something I think the
folks who current use the system wouldn't take too kindly. Granted,
I'm certain there are much more streamlined and automated ways to enter
redundant data, but again, I'm more in the market for an alternative
solution to my original idea of adding a good bit of code and reworking
the table structure, etc.

Thanks for the help.