Autofill Description from a different table

[tm1274]

Hello Everyone,



I am a complete newbie dealing with Access and I am having a difficult time understanding how to fill a description field on a form from a different table. The field I am trying to autofill is called fldDescription on the form called frmConfiguredProducts. The combobox is on the same form called fldPartNumber should pull the part numbers from the other table called tblInventoryItems and the description should also come from that table. I have attempted to watch videos, been reading online quiet a bit but believe I am just doing something wrong with the relationship or queries and cannot seem to get it to work. Can someone please take a look and tell me what I am doing wrong?

Attached are both a accdb and a mdb files (zipped) however I am using Access 2010 and the original was the accdb file. Any help would be greatly appreciated.

[pbaldy]

Like this?

http://www.baldyweb.com/Autofill.htm

[tm1274]

Unfortunately, I tried this method as well, with no luck. Most of the VBA I have tried was very similar and said to place the code on the on change event of the combobox such as:

Private Sub fldPartNumber_Change()
Me.fldDescription = Me.fldPartNumber.Column(1)
End Sub

I was still not able to get it to work so I finally decided to check on the forums to see if anyone would be willing to guide me to get past this stumbling block.

[pbaldy]

The description field needs to be added to the row source of the combo, and the column count changed from 1 to 2. Column widths would need to be adjusted as well.

[pbaldy]

By the way, I'd use the after update event. The change event fires with every keystroke if the user types in their selection, which you don't want for this.

[orange]

Your tables are in 1:1 relationship?
Since this is a first database, you should be advised that Access does not play well with field names that have embedded spaces or special characters (@#$%&*() etc. Adopt a naming convention that uses alphabetic and numeric characters and optionally the underscore "_". Names should not begin with a numeric character.

As Paul stated, the change event fires for each character --suggest use AfterUpdate.
Since there is only 1 column in the combo --try column(0)

Perhaps you could describe the "business" to be supported by this database. That is, just tell us how the things the tables represent fit together (simple terms).

[tm1274]

Thank you orange for asking about how the tables work in simple terms since i do not know all of the terminology yet.

The end result idea of the database to convert a number of spreadsheets to access and make it accessible on a network. The spreadsheets are what I call "configurators" for assembled products. These configurators include part numbers, descriptions, vendors, vendor part numbers, cost, selling price, quantity of each item, and weight of each item that make up the assembly. So basically a part number such as AC111111 would be the assembly and there can be many individual part numbers that are used in the assembly. The final result is a calculation of quantity of each line item x cost and then a total at the bottom. There is also a calculation for the weight to give a total weight.

I would really like to learn access and this example I posted is a small sample of how I was trying to start. Maybe not the best way but to be honest I am so new that I am somewhat confused as to how to best proceed.

What I was thinking was that if I had one table that would be updated regularly for price changes and that table would include each of the fields regarding the products then another table would hold the assembly part number and a description of the assembly along with what I believe to be a subform that would show all of the parts that are used in the assembly. The form would also include the totals and subtotals for cost, selling price and weight as mentioned above.

[pbaldy]

Should the combo row source be set to use the table "tblInventoryItems" or a query to get the data?

Typically a query (SQL in the row source) but either is fine since the table only has the 2 fields anyway.

[tm1274]

Thanks pbaldy, that actually helps a lot. I have really been confused on that part.

[pbaldy]

No problem, and welcome to the site by the way!

[orange]

tm1274,

I'm sending you this link that references several articles in various formats concerning Database Planning and Design. This is reference material. I suggest you spend an hour or so working through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the linked materials. The tutorials are paper based and have an intro, a problem description and steps leading to a solution. A solution is also provided. You will learn about design tables and relationships by experiencing the process as you work through the steps. What you learn can be used with any database. Learning about database and concepts is a good foundation before jumping in to physical Access.

Good luck.

[tm1274]

Thanks orange, I will start right now going through the materials. I really want to learn Access and usually I can understand easily for most things but for some reason Access has been a very difficult one for me to grasp. I also think that my approach to attempt to learn it has been wrong, hopefully this will get me pointed in the right direction.

[orange]

Good luck with your learning. Think of it as a long journey rather than an Event. Break the big things into pieces --.

[orange]

@tm1274 {I added the ZYX info to the link on DatabasePlanning and Design}

After reviewing your posts/description, this is what I understand you are describing:
assembled products
--"configurators"
These configurators include part numbers, descriptions, vendors, vendor part numbers, cost, selling price, quantity of each item, and weight of each item that make up the assembly.

Assembly consists of
part numbers
 descriptions
 vendors
  vendor part numbers
  cost
  selling price
  quantity of each item
  weight of each item 
  
--  Sounds like Items and parts are synonyms  
-- An AssembledProduct consists of 1 or Many Parts
-- you buy some parts from Vendor(s)
-- Vendor parts have a cost
-- vendors have their own part numbers
-- you sell something (Assembled products) to ?? Customers?
-- A Selling price exists for each sold (Assembled Product)
-- typically  a Supplier supplies materials

This data model from Barry Williams' site may be useful in relating the various facts.

[orange]

@tm1274

As discussed.