Simplifying Form structure

[Ian120]

Hi all, I have a rather complicated question here.

There are 3 tables concerning my question (in ascending order in the top left of the picture): 04_Purchase_Orders, 04_Purchase_Details, and 06_Inventory. On the right side is a Form I used to print a specific purchase order Report (One unique Purchase_ID) with the help of a query not shown here. Fields with identical names are linked. And just fyi, the query at the bottom left is how I link the two 04 tables and make the Form (on the bottom right) to fill in data for them both. Like when I fill in the form, both 04_Purchase_Orders and 04_Purchase_Details will be updated.

The problem I am facing is that when I put a subform of 04_Purchase_Details into the RHS form and try to print it out, it shows only the Parts_ID but not Parts_Name. Since 04_Purchase_Details contains data of parts ID instead of parts name, I can't make a combo box for that either. The current solution I can make is to put another subform (of 06_inventory) in there, which results in what you can see right now. But that is just... not aesthetic and requires some more button clicks when I purchase many stuff in one go (one Purchase_ID).



Is there a way to make the form somehow show the Parts_Name with only one subform, or some other way that the effort is worth the improvement? Thanks for the help guys! Really appreciate it!

[Gicu]

Ian, yes, there is an easier way to do that, replace the Parts_ID textbox with a combo box; set the combo's row source to "SELECT Parts_ID, Parts_Name From 06_Inventory Order BY Parts_Name;".Set the Column Count to 2, Column Widths to 0;2 and bound column to 1. Now you will see the part name but the field will properly store the Parts_ID.

[Ian120]

Ahh Thanks Gicu! It worked. I was wondering how to insert a combo box in this case since Parts_Name isn't a field in the table. I thought the Control Source must be Parts_Name to make the thing work.

However, now the Parts_Name column appears behind the latest column, which is behind "Currency", although I put the combo box and its label at the top, where the original Parts_ID textbox was. Now "Arrived?" appears at the very front. Do you know how to fix this? I would like the Parts_Name at the first column.

[Ian120]

I figured out that it has to be related to the order that the fields are put into the form. So the order is right once I delete the existing fields and fill them in again. I wonder if there is another way to do it though. Thanks Gicu, for the assistance as always.

[Minty]

Look at the form sections tab order. That controls the order you tab around.
The other option to look at is under the arrange section tab in the options for form design, where there are options for Bring to Front and Send to Back.

[Gicu]

In a datasheet form you can simply drag the columns in their desired locations and save the form (Save or CTRL+S).

Cheers,

[Ian120]

Thanks! I will give it a try.