Help Needed Pulling Data From Two Different Tables For My Form

[davec69]

Sorry for the long explanation. I've been working on this problem for a few days now, but it's obviously beyond my minimal MS Access skills. Thought I'd ask the experts on this forum for help.



I am creating a production database for my companies products, using two tables in Access 2010, (fyi, Windows 10 64 bit machine). So far, I have been able to connect Access to Quickbooks via ODBC, and create a table which contains all of our products. (Full Item Table). We also keep a Google Sheet, online, Which contains a list of all of our components used to make these product. I couldn't find a way to connect Access directly to our Google components sheet, so I exported it in Excel format to a folder on my local computer, and linked that file to Access (Google Sheets Component) table.

Next, I created a form in Access (See pic below). The top portion of the form shows the product details, and is based on the Full Item Table, and allows me to scroll through all of our products. This appears to be working correctly. I can scroll through all of the products, and see all the detail.

Next I wanted to add the "Component Breakdown" section to the form, based on the (Google Sheets Component) table. All I need from that table is a dropdown of the component name, and another column with the quantity. I added the dropdown box using a combo box which pulls the component name from the component table, and at the same time, writes the selection to fields which I added in the full item table which contains the detail. With my limited knowledge of Access, I was surprised that it actually worked. I can select components using the dropdown, and they are written to the full item table. As I scroll through the products, I can see the various components changing in the component breakdown section, for each record in the full item table.

Here's the problem.......I cannot for the life of me figure out how to get the qty column to show the correct component quantity. I've tried various different ways, but i must be doing something wrong. The (Google Sheets Component) table has a description column (used as the dropdown), and a total qty column. What I need to happen is that when a component is selected via the dropdown, that the corresponding qty appear next to it, in another box.

My question...How do make both the component description via the dropdown, and the corresponding quantity from the same table and same line, appear on my form? I've tried just adding a simple text box next to the dropdown, and pointing it to the qty field on the table, but it's either blank, or it has something like #NAME in the field instead of the quantity. I've played around with relationships. Not sure what's going on.

One other piece of information that may help. The form itself uses the Full Item Table as the record source. The two fields for the Component breakdown section are pulling from the Google Sheets table.

[NTC]

the first thing that confuses me is in your post is that there are 14 component fields but only 1 quantity field; so that when you write "when a component is selected via the dropdown, that the corresponding qty appear next to it"....this implies there should be a QTY field next to each component field.

[davec69]

Sorry for the confusion. I was actually working on the form, when I did the screen capture. I had just removed the other quantity fields, since they were not working. I figured that if I could get one quantity field working, then the others would be fine.

BTW, the quantity fields were not combo boxes, they were regular text boxes. I was just trying one, to see if it worked.

the first thing that confuses me is in your post is that there are 14 component fields but only 1 quantity field; so that when you write "when a component is selected via the dropdown, that the corresponding qty appear next to it"....this implies there should be a QTY field next to each component field.

[NTC]

When you select a value from a table - in this case the component - one is using either a LookUp field type (implemented at the table level) or a combobox field control (implemented at the form level) - either way it doesn't matter - this is fundamentally a query and you can obtain any/every field in the record selected.

Only 1 field can display inside of the combobox itself - but the other fields can display or get written into other text box. In either approach you must identify the field correctly with this syntax:

me.ComboboxName.Column(X) where X is an integer of the field order left-to-right

Typically a record is laid out like this: key, Field1, Field2, Field3

You adjust what is visible in the combobox thru its column width properties and as to what field is bound.

You then either have unbound text boxes or bound text boxes along side to permanently display the other fields of that record. Use unbound if the data never is to change with syntax in its data source property i.e.: =me.ComboboxName.Column(3)

or use a bound field (this field of course must exist in the form's record set) if the data is to be called in but then may change - to do this you must put the code in the AfterUpdate Event of the combo box to write the information i.e. Me.Field3 = me.ComboboxName.Column(3)