AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Expert: Scottgem Date: 7/4/2008 Subject: Auto Populate
Question QUESTION: Hey. I've designed a simple database here but I have one little problem. I have a Transactions Table. Two of the fields are Product Price and Product Name.
On a form , I am putting a drop down menu for the Product Name field. What would like to know how to do is that when i slecet a product from the drop down menu, the Product Price pertaining to the product name will populate.
I have done some googling and keep coming up with things along the line off Forms!Transactions![Value]=Forms!Transactions![Product Price].Column(1)
I try these things but MS Access keeps giving me errors. Any help would be appreciated.
Thank you in advance
ANSWER: In the After Update event of the Product combo use the Code Builder to add a line like:
Me.txtPrice = Me.cboProduct.Column(1)
where txtPrice is the name of the Price control and cboproduct the name of the Product combobox. This also assumes that the Price column is the second column in the RowSource of the combo.
I would suggest checking the Northwinds sample database that comes with Access as it has examples of this.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: I played around and did what you said. I added the line
Me.Product Price = Me.Product Name.Column(4)
When I drop down the menu and select a product I get an error about macros. It says that "If 'Me' is a new macro or macro group,make sure you have saved it and that you have typed its name correctly"
ANSWER: I'm afraid you didn't do what I said. I said to use the CODE BUILDER to enter that line of code. You just typed that line of code into the After Update property box. So access thinks its a macro name and looks for a macro with that name.
A combo box has a Rowsource property. That defines what appears in the dropdown list. Generally, its a SELECT statement (query). The number that defines the column property is the column within that rowsource. The count starts at zero so the first column is actually 0, the second: 1, etc.
You may have to reinstall office to to install the Northwinds sample. Otherwise just search for Northwinds and see if it was installed.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Ok I understand now. I put it in code builder but when i select a product from the drop down menu i get a run time error 438 - object does not support this property or method.
I'm sorry for all this stress :(
Answer Try it this way:
Me.[Product Price] = Me.[Product Name].Column(4)
For just this reason, its is recommended NOT to use spaces in object names. The brackets will tell aaccess you are referring to a control.
Also I do not recommend using the field names as control names. I suggest you do research in using a Naming convention and name your controls according to it.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
