Pass Main form value to Subform or Pop-up

[tbrogdon]

I have tbl Production w/ fields Production ID (PK & autonumber), Date,
Shift , and Department.
I have tblProductionOperation linked to tblProduction on ProductionID.

For any record in Production I can have many records in
ProductionOperation (e.g., ProductionID, PartID, Qty, Operator, etc.)


I would like to do two different yet similar things:

ONE
I have frmProduction which has the same fields as tblProduction. This
form has a command button that opens frmProductionOperation when
clicked. (This currently works).

I have frmProductionOperation which includes a txt field for
tblProductionOperation.ProductionID (the field linked to
tblProduction.ProductionID - PK).

I would like to know how to, when the user enters Date, Shift, and
Dept on frmProduction and clicks the cmd button, have the txt field on
frmProductionOperation be automatically set to the value from
frmProduction.txtProductionID and have all subsequent records set to
the same value until frmProductionOperation is closed.

TWO
I would like to do exactly the same thing except with
frmProductionOperation embedded in frmProduction.

Thanks,

Tim

 

[Rhys Davies]

create an on click event on the command button and enter:
me.productionID = forms!frmproduction!txtproductionID

 

[tbrogdon]

create an on click event on the command button and enter:
me.productionID = forms!frmproduction!txtproductionID

It isn't working.

I think I wasn't clear enough. The command button is on the first form
opened (frmProduction) which acts as a Parent/Master index for all of
the records input on the next form (frmProductionOperation). When the
command button is clicked it opens the second form
frmProductionOperation.

So I have frmProduction.txtProductionID and
frmProductionOperationID.txtProductionID.

Three pieces of info are input on frmProduction which create a new
ProductionID value and the user clicks the command button which opens
continuous frmProductionOperation which also has a field ProductionID
which needs to correspond to the frmProduction.ProductID for all
records the user is about to input until the form is closed.

Hope this clarifies. Thanks,

Tim

 

[tbrogdon]

Also, I should point out that frmProductionOperation.txtProductionID
is bound and is 1 of 5 fields that acts as PK for
tblProductionOperation.

So in tblProductionOperation I can have multiple records with
identical values in tblProductionOperation.ProductionID that will
correspond to only 1 value in tblProduction.ProductionID.

Thanks again,

Tim

 

[Dennis]

Since the values are the same, I wouldn't even put it/them in fields on the
subform. I'd make them look kind-of like a "header" for the subform. Then
when the record gets written, in the BEFORE UPDATE event, I'd set invisible
text boxes on the subform equal to the parent form's data. Nice and clean,
and looks really slick from a user-standpoint.

 

[tbrogdon]

Since the values are the same, I wouldn't even put it/them in fields on the
subform. I'd make them look kind-of like a "header" for the subform. Then
when the record gets written, in the BEFORE UPDATE event, I'd set invisible
text boxes on the subform equal to the parent form's data. Nice and clean,
and looks really slick from a user-standpoint.

Dennis,

That is exactly what I want to do. I just don't know how to set it up
or set the invisible text box on frmProductionOperation equal to the
parent form's data. I do know how to make them invisible and all just
not how to pass the value so that it is recorded in
tblProductionOperation.

I apologize for the newbie question and very much appreciate the help.
Tim

 

[Dennis]

Rhys Davies' post above should have worked if you used the correct form and
field names. What error did you get when you attempted his solution?

Alternatively (and this is kludgy but would work), you can defein PUBLIC
variables in a module with names that approximate the field names you want to
pass. Then, as the "master" form fields get populated, you move those values
into the public vars. Then you have your "invisible" fields in your subform,
and in the Before Update event of the subform, insert code similar to this:

me.FirstFieldThatIWant = thePublicField1 (assuming 'thePublicField1' was set
by you in the Master form)
me.SecondFieldThatIWant = thePublicField2
me.ThirdFieldThatIWant = thePublicField3

and so on. That always works for me, though I only use Public variables when
I have to move data around multiple forms and modules. For your application,
it'd be kind-of overkill, but it'd get the job done.

 

[tbrogdon]

Hey Dennis,

I ended up making such a mess that I re-evaluated my table design a
bit and started over on my forms from scratch.

tblProduction and tblProductionOperation are linked by ProductionID
(pk in tblProduction).

I created an autoform on tblProduction which automatically inserted a
datasheet of tblProductionOperation.

I've been playing with it to try to undertand the linkage between the
two tables.

What I'd like to end up with is a main form frmProduction linked to
continuous subform tblProductionOperation.

The situation is this: ProductionID is an autonumber field in
tblProduction. I work at a plant that has 3 shifts in 2 departments
every day. Each entry for ProdutionID in tblProduction represents an
instance of 1 day, 1 shift, 1 department. So I will have 6 instances
of ProductionID per day. tblProductionOperation represents all of the
various parts produced for each instance of ProductionID.

It has been my thinking that I need to produce an instance of
ProductionID in tblProduction before I can input data in
tblProductionOperation thus the main form frmProduction which contains
the Date, Shift, and Department fields with a command button to then
go to embedded subform frmProductionOperation where I input the
various parts and employees who ran the parts...but I am now thinking
that I have a fundamental misunderstanding of how to accomplish this.

Can you give me advice on how this should be structurally setup in the
first place or does it sound like my current structure is OK?

Thank you very much,

Tim

 

[Dennis]

Really, all you have to do is add the Primary Key from the main table as a
field in the secondary table. You create the subform independently (so that
it shows you everything you want for ALL secondary table records. Once that
works, simply use the wizard to add the secoondary form as a subform to the
main form. You'll be asked what field to link the forms on. Tell the wizard
that you want to use that key field. The form will be added, and you're good
to go. All automatically.

Just BE SURE to set the subform (when you design it separately) to
CONTINUOUS FORM rather than SINGLE RECORD (or however that option is worded.)
Really, if you place the equivalent of the primary key from the main table
into the secondary table as a non-key field, the linkage is automatic once
you add the form as a subform.

Should take you about 5 minutes (once the stand-alone secondary form is
designed and operational).

 

[tbrogdon]

Should take you about 5 minutes (once the stand-alone secondary form is
designed and operational).

Dennis...Thank You!!

My obstacle before was that I overlooked setting the the subform
display to continuous as you reminded me. It is beautiful now. And it
did take all of 5 minutes (after I designed the subform)...well I have
certainly learned A LOT from this little under-taking. I can't thank
you enough!

I do have another question...

Given the criteria on the Main Form (Date, Dept., and Shift) which
together determine the current ProductionID - How can I make Access
"wait" to set the ProductionID until I have completely input the data
for Date, Dept.,and Shift? Or am I thinking about this incorrectly? As
it stands, the moment I input any data in any one of the three fields
in the Main Form, Access generates a ProductionID number. This is a
problem as for any one of the 3 shifts per day there are 2 seperate
departments. This means I need 6 instances of ProductionID per day
where I am getting 3 now. I don't want to use a composite key as the
primary key as I am pretty sure that will just cause more problems
later.

Thank you again,

Tim

 

[Dennis]

Normally, one NEVER shows the auto-number primary key. There really is no
need. Since you're using a bound subform based on that key, all you need to
show are the components that actually make up a "production unit" (if you
will). These are (per your description):

Date, Dept and Shift.

If someone wants to enter data, they'll navigate to the record based on that
information. (No one is ever interested in "record ID 1256" for example. In
your case, they'd want the production record for 12/15/2007, Dept 57, and 2nd
shift. THAT is meaningful to them.)

And since you never show the primary key, your question becomes moot.

That help?

 

[tbrogdon]

And since you never show the primary key, your question becomes moot.

Well....actually no. You're correct that the user doesn't need to see
the ProductionID but I need to make sure that the ProductionID is
different for each instance of Date, Shift, and Department.

We have two departments: Hard Tool and Soft Tool.

With the form set up the way you suggested - and it does function
beautifully - it still allows the user to enter data to both Hard Tool
and Soft Tool without changing ProductionID. I need a ProductionID for
every day, each shift, and both departments.

I've only left the ProductionID visible on the Main form so I can
check this. It is not visible on the subform.

At the top of the main form are these fields in descending order:
ProductionID (autonumber - noneditbale by user)
Date: txtProdDate; default =Date()
Shift: cboShift; could be either 1, 2, or 3
Department: cboDept; could be Hard Tool, Soft Tool, Other

I need a unique identifier for each instance before alllowing data
ebtry in the subform - for example:
ProductionID - 1
Date: 12/4/07
Shift: 1
Department: Hard Tool

and

ProductionID - 2
Date: 12/4/07
Shift: 1
Department: Soft Tool

ProductionID - 3
Date: 12/4/07
Shift: 2
Department: Hard Tool

and so on and so on...

So somehow I need to limit the ability to either edit the info in the
main form after they have made their initial 3 selections (which I
don't think is a good idea because they could enter the wrong data the
first time possibly) or I need to make it to where AFTER they enter
the Date, Shift, and Department data Access somehow checks to see if
those values already exist in that configuration before allowing a new
entry on a duplicate ProductionID.

What do you think?

Tim

 

[Rhys Davies]

Hi - assuming that each shift is created on a separate main production form
then the design should work correctly. Otherwise im unsure how youre trying
to set it up? The productionID has to be unique to ensure that each main
record created is unique and the resulting operational information ties to it
correctly on a sub form. Dennis is completely right that date, dept and
shift should then be used to filter for the records that you want, not the
productionID.

 

[tbrogdon]

Hi - assuming that each shift is created on a separate main production form
then the design should work correctly. Otherwise im unsure how youre trying
to set it up? The productionID has to be unique to ensure that each main
record created is unique and the resulting operational information ties to it
correctly on a sub form. Dennis is completely right that date, dept and
shift should then be used to filter for the records that you want, not the
productionID.

Hi Rhys,

Actually the form is currently designed with the production details
subform embedded in the Main form and that, I think, is were my issue
comes from. I know you are right - If my main form is seperate from my
subform and the subform opens from a command button I can check for
duplicate combinations of the defining 3 fields before the user gets
to the production details subform. If the user inadvertently inputs
duplicate data then I can check and give them a message to double
check their info before proceeding.

With the way I have it set up currently (i.e., embedded subform in
main form) the user can go back to the Date, Shift, and Dept fields
and change either the Shift or the Dept and then go back to the
subform and continue entering data with no change in ProductionID
number - that means that a user can input data for 12/5/07, Shift 1,
Dept Hard Tool; go to the subform, enter some data, and then realize
they made a mistake in either the Shift or the Dept; go back to the
main form section, make the change without changing the ProductionID,
go back to the subform and continue entering detail data - but then I
end up with detail data that is mixed on either shift or dept and
associate with a single ProductionID.

In other words it is currently possible to have the following which is
unacceptable:

ProductionID: 1
Date: 12/5/07
Shift: 1
Department: Hard Tool

AND

ProductionID: 1 (This should be different depending on when the
supervisor for that shift and that dept. gets around to entering the
data)
Date: 12/5/07
Shift: 2
Department: Soft Tool

OR any combination thereof.

I am really sorry that I am doing such a poor job of describing my
problem and I am very grateful for both of your continued input and
patience with a newbie.

Like I said, I am going to try making the forms seperate connected by
a command button which checks the date, shift, and dept and if not a
duplicate then opens a new record in the subform or if the data is
duplicate on those three fields the user receives a dialog box giving
them further direction.

Again...thank you ever so much for your guidance, advice, and
patience.

Tim