Will this work and how in Access

[delephant]

Currently using Access 2003 - We need to be able to pull various components
from several tables in a couple of linked databases into one form/report.

We are making “Parts Lists†– so there is the main component and then the
other smaller parts that make up that main component listed below it. One
table holds the main configuration numbers, and another table in a separate
database holds the component parts numbers.

We need to pull the main component number along with its description as a
portion of the heading from the main component table from a linked Access
database into a form. Then we need to list the various parts pulling from
the smaller parts database into the same form (this could be anywhere from 2
components to 200 components). Once the information is pulled (which I have
not been able to get to pull the way I’d like them to - automatically), the
person needs to be able to add additional information into the form and then
be able to save and print.

We are trying to use the form option because the report option does not allow
the person to add information to the pulled list.

Is this possible in Access?

 

[George Hepworth]

Have you tried to work this out in a Main form/Sub form design?

And are you trying to create a persistent recordset for each of these parts
lists? I have to assume that would be the case, because you want to save
and print. And further, I have to assume that the "Main Component" table is
the one which contains records for the components for each parts list and
the "Parts" table is the one which contains records for the parts. If those
assumptions are valid, then what you describe (Main Component and smaller
parts which make up that main component) surely sounds like that would be a
good fit in a main form/subform design.

The main form would be bound to the main component table. The subform would
be bound to the parts table.
You can design the subform to be displayed in continuous view, or datasheet
view.

HTH

George

 

[delephant]

I have tried to set it up in a Main/Sub form design but have issues with it
printing and having the different smaller components listing without all
lines showing as the first part. I am doing something wrong here probably.
The only way I can get the smaller components to show individually is by
setting each box up as a combo box (that's a lot of setting up if you have a
part with 200 smaller components). Even when I get the boxes to work, I
still have the issue of the subform showing when I print. I'm sure it's a
simple issue of not setting the subform up correctly, but with everything
I've read, I'm still having issues. What am I missing?

Have you tried to work this out in a Main form/Sub form design?

And are you trying to create a persistent recordset for each of these parts
lists? I have to assume that would be the case, because you want to save
and print. And further, I have to assume that the "Main Component" table is
the one which contains records for the components for each parts list and
the "Parts" table is the one which contains records for the parts. If those
assumptions are valid, then what you describe (Main Component and smaller
parts which make up that main component) surely sounds like that would be a
good fit in a main form/subform design.

The main form would be bound to the main component table. The subform would
be bound to the parts table.
You can design the subform to be displayed in continuous view, or datasheet
view.

HTH

George

Currently using Access 2003 - We need to be able to pull various
components

[quoted text clipped - 24 lines]

Is this possible in Access?

 

[George Hepworth]

Wait, you don't want to PRINT the form itself. I must have overlooked that
part of the requirement, sorry.

Forms are on-screen data entry/manipulation tools. You use them on-screen to
add, update and delete records in the tables to which they are bound.

REPORTS are the tools you need to print out your parts lists. Design the
report so that it has the proper appearance for the print parts list. It
will also be a main report/sub report design.


The form is only going to be used to add the components to a parts list. Add
a button to it which triggers code to print the report for that parts list.


Again, my apologies for not picking up on this before.

George

I have tried to set it up in a Main/Sub form design but have issues with
it
printing and having the different smaller components listing without all
lines showing as the first part. I am doing something wrong here
probably.
The only way I can get the smaller components to show individually is by
setting each box up as a combo box (that's a lot of setting up if you have
a
part with 200 smaller components). Even when I get the boxes to work, I
still have the issue of the subform showing when I print. I'm sure it's a
simple issue of not setting the subform up correctly, but with everything
I've read, I'm still having issues. What am I missing?

Have you tried to work this out in a Main form/Sub form design?

And are you trying to create a persistent recordset for each of these
parts
lists? I have to assume that would be the case, because you want to save
and print. And further, I have to assume that the "Main Component" table
is
the one which contains records for the components for each parts list and
the "Parts" table is the one which contains records for the parts. If
those
assumptions are valid, then what you describe (Main Component and smaller
parts which make up that main component) surely sounds like that would be
a
good fit in a main form/subform design.

The main form would be bound to the main component table. The subform
would
be bound to the parts table.
You can design the subform to be displayed in continuous view, or
datasheet
view.

HTH

George

Currently using Access 2003 - We need to be able to pull various
components

[quoted text clipped - 24 lines]

Is this possible in Access?

 

[delephant via AccessMonster.com]

No problem - I'm assuming this means I won't be able to do what I need to
because before we print, we have to add additional information for each part
such as quantity, location in drawings, etc. This varies each time as one
part may be associated with a number of different larger parts. That is the
reason why a report won't work for us. Unable to add the info once the
report is run, unless there is another option.

Wait, you don't want to PRINT the form itself. I must have overlooked that
part of the requirement, sorry.

Forms are on-screen data entry/manipulation tools. You use them on-screen to
add, update and delete records in the tables to which they are bound.

REPORTS are the tools you need to print out your parts lists. Design the
report so that it has the proper appearance for the print parts list. It
will also be a main report/sub report design.

The form is only going to be used to add the components to a parts list. Add
a button to it which triggers code to print the report for that parts list.

Again, my apologies for not picking up on this before.

George

I have tried to set it up in a Main/Sub form design but have issues with
it

[quoted text clipped - 40 lines]

 

[John W. Vinson]

No problem - I'm assuming this means I won't be able to do what I need to
because before we print, we have to add additional information for each part
such as quantity, location in drawings, etc. This varies each time as one
part may be associated with a number of different larger parts. That is the
reason why a report won't work for us. Unable to add the info once the
report is run, unless there is another option.

If this information does not need to be stored for future reference - i.e. it
can be reentered and/or changed every time the report is run - then you could
use unbound controls on the Form, and reference them in textboxes on the
Report: a report textbox can have a control source such as

=[Forms]![NameOfForm]![NameOfControl]

However, it sounds like the number of parts or the location in a drawing is
business-relevant information that you would need to keep! Shouldn't this
information be in a table - which could be included in the report's
recordsource?

 

[George Hepworth]

Now, I'm a bit confused. Are you saying that you DO NOT store this
information regarding parts lists?
In other words, you punch in the data for a list, insert the quantities,
etc., then print that instance, then you abandon all that work when you
close the form?

I can understand that as Access will supply the various lookup values in
some of the controls,
but it does seem more appropriate, as noted elsethread, that you would want
to keep that information and be able to reprint it any time you need to,
rather than having to recreate the parts list.

George

No problem - I'm assuming this means I won't be able to do what I need to
because before we print, we have to add additional information for each
part
such as quantity, location in drawings, etc. This varies each time as one
part may be associated with a number of different larger parts. That is
the
reason why a report won't work for us. Unable to add the info once the
report is run, unless there is another option.

Wait, you don't want to PRINT the form itself. I must have overlooked that
part of the requirement, sorry.

Forms are on-screen data entry/manipulation tools. You use them on-screen
to
add, update and delete records in the tables to which they are bound.

REPORTS are the tools you need to print out your parts lists. Design the
report so that it has the proper appearance for the print parts list. It
will also be a main report/sub report design.

The form is only going to be used to add the components to a parts list.
Add
a button to it which triggers code to print the report for that parts
list.

Again, my apologies for not picking up on this before.

George

I have tried to set it up in a Main/Sub form design but have issues with
it

[quoted text clipped - 40 lines]

Is this possible in Access?

 

[delephant via AccessMonster.com]

We do store information, just not all of the minor details. We currently use
FileMaker Pro for all of these forms. They want to look at moving this over
to Access (temporarily - there is another customized database in development
and has been years in the making - a slow process) and I have been tasked to
try to get this to work similar to how the department currently uses
FileMaker Pro until the customized database is ready. A number of
individuals access this information and too much change is difficult.

Ultimately, the parts list is exported and built into a pdf drawing. The
final pdf is saved on another server as a complete drawing - so information
is saved. If another copy is needed - open the appropriate drawing to print.


I understand what you say about saving in another table - but this would be
enormous.

Now, I'm a bit confused. Are you saying that you DO NOT store this
information regarding parts lists?
In other words, you punch in the data for a list, insert the quantities,
etc., then print that instance, then you abandon all that work when you
close the form?

I can understand that as Access will supply the various lookup values in
some of the controls,
but it does seem more appropriate, as noted elsethread, that you would want
to keep that information and be able to reprint it any time you need to,
rather than having to recreate the parts list.

George

No problem - I'm assuming this means I won't be able to do what I need to
because before we print, we have to add additional information for each

[quoted text clipped - 30 lines]