Two tables, one combobox

[gaker10]

Sorry I forgot to mention, that's pretty much how it works. Whatever is put into the form is also put into the subform, and the form is attached to a table. The subform is just a look-alike of the final table. It cannot be edited or anything directly, and so I have buttons on the form to populate/change the subform fields. The database is modified directly for now. The form inputs only one assembly at a time right now.

I'm going to have to do this for every component that is in a particular assembly though. Is there a report layout that I'm overlooking that will have the exact same layout as in the report screenshot without the components directly under the assembly?

I had to change each individual field color to black font in Report design mode. Can I make this automatic?

[June7]

If the components are not directly under the assembly then that is not the exact same layout. Where else should the components be if not under the assembly?

What do you mean by make it automatic? Set the property in design view and save the report.

[gaker10]

Ill be making a new report for every assembly. There are usually about 5 assemblies. Do I need to set the font color every time? Attached screenshot. The comp1 and comp2 in red do not need to be there. Ideally, I would like comp1 and comp2 under assembly and indented, then alternates under comp and also indented.

[June7]

Why a different report object for each assembly? Why not one report and apply filter criteria when report is opened?

Expression in textbox: =IIf([Assembly] LIKE "Comp*", Null, [Assembly])

Why does the Assembly field have values "comp1" and "comp2" and why some Null? This data structure is just odd. Trying to remember history of this thread - was this data imported? I think I am seeing subheader rows that really should be fields of data. For instance, the EA and FT designations should be in a field called Units and should be on every record. The identifier Assembly1 should be on every record. Then the two subheader records with comp1 and comp2 as Component should be deleted.

[gaker10]

Because these assemblies need to be exported to an excel, and then pasted into a pre-determined template with equations. Also, the comp1 and comp2 are just dummies for realistic part numbers. Part numbers vary in the extreme as far as format.

I have no idea. I was trying to play with the subdatasheet to get the Component field to have a subdatasheet of Components/Descriptions etc. This data was technically from a database of another format. Someone imported the info to an access database so that everyone can learn Access entry after I mess with it and make entry faster. Yes, EA and FT are actually labeled UOM and are in the correct locations. I typed them in manually just to see where they would go. I just squeezed a lot of stuff into one page. Everything is where it should be except the extra comp1 and comp2 in the red circle. Assembly1 only needs to be typed in once because a reference in the excel template autofills a section of the excel file with the designated assembly via copying and pasting into the tagged cell. Having the assembly on every line in the access report will cause a circular reference error in the excel template.

[June7]

Nothing in that post convinces me you need 5 or more report objects. One report, apply filter.

After looking at the image in post 12 again, I am still confused. The data in UOM of EntryFormTable does not appear to be the same type as in UOM field of the related subtables.

Multiple similar fields (UnitPriceA, UnitPriceB, UnitPriceC) indicates a non-normalized data structure.

I am afraid I will not be able to help further. Just too confused by the data structure and I certainly don't know what Excel is doing with the data. Designing a database to fit an Excel template is not what I would do.

[gaker10]

Thinking about it, I think you're right. One report might do it.

Yes, I just wanted to see how the report would place everything. I was just messing with the different UOM labels and the associated components. Basically I want the final report to read like indented code. Assembly is our "function", Component is our "if statement", Component's description and UOM etc is the if statements "arguments", and Components alternates are the body of the if statement. Best formatting example I can think of, hehe. So like this:

Assembly1
Component1, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component1AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component1AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Component2, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component2AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component2AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Assembly2
Component1, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component1AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component1AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Component2, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component2AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component2AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Components 1 and 2 could be the same or not regardless of my terrible labels. They can vary by assembly though.

The database itself never touches Excel. Whats pulled out of the database and into a table, then into a report for formatting reasons is what will be put into an excel template for pricing. Once I get everything working correctly, I think everything will be done in Access (entry and pricing/quantities), and then all the info will be exported to the excel template, either by copying and pasting or somehow placing everything automatically (not likely), and calculations are done in Excel (add prices up, input necessary labor using labor template sheets etc).

As it is was before I used Access to speed up data entry, assemblies were put directly into excel by hand. In other words, everyone was typing every part number, description, UOM etc. The purpose of using Access to do this is to automate things that do not change and prevent typing the same thing 10 times, and eliminate the frustrating typos in the final reports. However, calculations still need to be done, and those calculations are exclusive to Excel.

[June7]

Okay, one more stab.

Explain to me how the ALTERNATES and DRAWINGS tables are linked to the EntryFormTable? What are the PK/FK fields?

Why are calcs exclusive to Excel? Why cannot be done in Access?

[gaker10]

Ok so here's the form everyone works in:


You type in a part or drawing numbers (depending on whats on our list of stuff), and when it is complete, the description and UOM autofill. If we type in a part number, and it has alternates, I want AlternateA field and AlternateB field to be filled using ALTERNATES table:



However, if a drawing number is chosen, I want the component textbox to be filled with the Primary field (overwrite the drawing number), autofill description and UOM. Also, AlternateA and AlternateB fields are filled with AlternateA and AlternateB from the DRAWINGS table, along with descriptions and UOM which can be found in the main database:



Any alternates that are entered I want to be filled into subdatasheets on the corresponding table:


Sorry, what are PK/FK?

Calculations depend in part on info in an excel template that I did not make and am not in charge of.

[gaker10]

Thinking about it, I think you're right. One report might do it.

Yes, I just wanted to see how the report would place everything. I was just messing with the different UOM labels and the associated components. Basically I want the final report to read like indented code. Assembly is our "function", Component is our "if statement", Component's description and UOM etc is the if statements "arguments", and Components alternates are the body of the if statement. Best formatting example I can think of, hehe. So like this:

Assembly1
Component1, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component1AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component1AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Component2, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component2AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component2AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Assembly2
Component1, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component1AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component1AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Component2, Desc, AssemblyQty, UOM, Price, Qty, etc.
Component2AltA, DescAltA, AssemblyQtyAltA, UOMAltA, PriceAltA, QtyAltA, etc.
Component2AltB, DescAltB, AssemblyQtyAltB, UOMAltB, PriceAltB, QtyAltB, etc.

Components 1 and 2 could be the same or not regardless of my terrible labels. They can vary by assembly though.

The database itself never touches Excel. Whats pulled out of the database and into a table, then into a report for formatting reasons is what will be put into an excel template for pricing. Once I get everything working correctly, I think everything will be done in Access (entry and pricing/quantities), and then all the info will be exported to the excel template, either by copying and pasting or somehow placing everything automatically (not likely), and calculations are done in Excel (add prices up, input necessary labor using labor template sheets etc).

As it is was before I used Access to speed up data entry, assemblies were put directly into excel by hand. In other words, everyone was typing every part number, description, UOM etc. The purpose of using Access to do this is to automate things that do not change and prevent typing the same thing 10 times, and eliminate the frustrating typos in the final reports. However, calculations still need to be done, and those calculations are exclusive to Excel.

I just realized that post #22 did not indent like I wanted it to -_- Sorry about that. I'll screenshot what I mean:

[June7]

PK/FK

Primary Key / Foreign Key

[gaker10]

Ah ok.
ALTERNATES: PK = PartNumber
DRAWINGS: PK = Drawing
EntryFormTable: PK = Component
CADatabase: PK = PartNumber

PartNumber and Component mean the same thing here. This is actually why I cannot do more than one assembly at a time (make a report of several assemblies), because some assemblies might use the same components, and if I try to put in the same component twice, I get a duplicates error, which I should. This is to prevent entry error.

I don't know Foreign Keys very well, so I avoided them.

[June7]

Can't have a relational database without foreign keys. Foreign key fields save the primary key of another table to establish a link between related tables.

[gaker10]

Yeah I kind of understand what they do but I didn't see any functional difference when I tried to use them. Where should I have FK?

[June7]

I thought the EntryFormTable would have FK field for the ALTERNATES/DRAWINGS PKs but this is not workable if ALTERNATES and DRAWINGS use autonumber as PK.