show 2nd field when combo box choice is made

Greetings:

I have a db for clients and their purchases. In my attempt to further
normalize my tables, I have created a new table to list the products clients
can purchase. So now i have 3 tables. One has the client info, the 2nd has
the individual orders (ID, client foreign key, product (stores combo box info)
, order date, quantity ordered and a price number field to hold the info this
question is about) and the 3rd has the list of products and the price of each
product.

I have a form to enter the client info and a subform to enter each client's
order info. The order subform has a combo box to choose the product ordered.
When I choose the product,I would like the price from the product table to go
into a field in the subform and be recored in the order table. Is this
possible?

Thanks,
Scott

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

0
Scott_Brasted
5/21/2010 8:44:39 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
1460 Views

Similar Articles

[PageSpeed] 24

Set up your combobox using the wizard and include the fields you need, from
Left-to-Right. 

If in the Combobox they appear as

Product   Price

the code would be

Private Sub YourComboBoxName_AfterUpdate()
   Me.SubformPriceField = Me.YourComboBoxName.Column(1)
End Sub					

The column index is zero based, so the first column would have an index of 0
(zero). You sound as if your combobox is bound so that the Product is saved
directly to your underlying table, but if you wanted to assign the Product to
a textbox, you would use

Me.SubformProductField = Me.YourComboBoxName.Column(0)

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

0
Linq
5/21/2010 9:18:30 PM
In the after update event for the combo, you can use code to copy the price 
from a hidden column of the combo to the price textbox.

Me.PriceTextboxName = Me.ComboName.Column(2)

For a sample database that shows this in action go to

http://allenbrowne.com/TechniqueEnterCalcText.html

Don't be put off by the title which is about entering text in calculated 
controls, this database does show how to get the price into the orders 
table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Scott_Brasted via AccessMonster.com" <u56211@uwe> wrote in message 
news:a85a0a19e7f37@uwe...
> Greetings:
>
> I have a db for clients and their purchases. In my attempt to further
> normalize my tables, I have created a new table to list the products 
> clients
> can purchase. So now i have 3 tables. One has the client info, the 2nd has
> the individual orders (ID, client foreign key, product (stores combo box 
> info)
> , order date, quantity ordered and a price number field to hold the info 
> this
> question is about) and the 3rd has the list of products and the price of 
> each
> product.
>
> I have a form to enter the client info and a subform to enter each 
> client's
> order info. The order subform has a combo box to choose the product 
> ordered.
> When I choose the product,I would like the price from the product table to 
> go
> into a field in the subform and be recored in the order table. Is this
> possible?
>
> Thanks,
> Scott
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1
> 


0
Jeanette
5/21/2010 9:19:36 PM
That's it exactly. I am trying to understand exactly how to do this. I will
re-read it a couple of times before I throw my hands up and ask for more help.


Many thank to you both.

Best,
Scott

Jeanette Cunningham wrote:
>In the after update event for the combo, you can use code to copy the price 
>from a hidden column of the combo to the price textbox.
>
>Me.PriceTextboxName = Me.ComboName.Column(2)
>
>For a sample database that shows this in action go to
>
>http://allenbrowne.com/TechniqueEnterCalcText.html
>
>Don't be put off by the title which is about entering text in calculated 
>controls, this database does show how to get the price into the orders 
>table.
>
>Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>> Greetings:
>>
>[quoted text clipped - 21 lines]
>> Thanks,
>> Scott

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

0
Scott_Brasted
5/22/2010 3:04:26 PM
Scott:

The sample Northwind database illustrates another way of doing this by
looking up the unit price from the Products table by means of the DLookup
function.  The code for this is in the AfterUpdate event procedure of the
ProductID control on the Order Details subform. In earlier versions this is
done directly in the event procedure, but in Access 2007 then code calls the
GetListPrice function, which in turn calls the DLookupNumberWrapper function.

Note that the Northwind database has Orders and Order Details tables, the
latter modelling the many-to-many relationship between orders and Products.
This allows each order to include more than one product.  In your case you
are allowing only one product per order, which may always be the case in your
business model.  If not you should decompose your Orders table along the
lines of those in Northwind.  Otherwise your Orders table is not fully
normalized as it would contain redundancies in the event of one order
covering more than one product.

Ken Sheridan
Stafford, England

Scott_Brasted wrote:
>Greetings:
>
>I have a db for clients and their purchases. In my attempt to further
>normalize my tables, I have created a new table to list the products clients
>can purchase. So now i have 3 tables. One has the client info, the 2nd has
>the individual orders (ID, client foreign key, product (stores combo box info)
>, order date, quantity ordered and a price number field to hold the info this
>question is about) and the 3rd has the list of products and the price of each
>product.
>
>I have a form to enter the client info and a subform to enter each client's
>order info. The order subform has a combo box to choose the product ordered.
>When I choose the product,I would like the price from the product table to go
>into a field in the subform and be recored in the order table. Is this
>possible?
>
>Thanks,
>Scott

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
5/22/2010 4:27:18 PM
Reply:

Similar Artilces:

can I have a multiselect option for a list box in excel 2003
I am working with Excel 2003, I created different list boxes for different fields but I wanted to know if I could select more then one answer by using CTRL command? Is that only available for later versions? thanks Yes, you can. Depending on what type of listbox it is and where it's located, the instructions to modify the settings would be different. If it's a listbox from the Forms toobar placed on the sheet, then rightclick on it and choose Format Control, then Control tab and choose Multi. If it's a listbox from the Control toolbox toolbar placed on a workshee...

Too many fields defined
I am currently using access 2003 and I am getting a too many fields defined error when attempting to combine 5 sepereate queries into 1. I have tried doing the compact and repair and then rebuild the query that is listed as a suggestion on on other questions and that didn't work. I have listed my SQL information below.. any help would be great. Thanks SELECT [Employee Hierarchy - Current Month].[Performance Year], [Employee Hierarchy - Current Month].[Performance Month], [Employee Hierarchy - Current Month].ATTUID, [Employee Hierarchy - Current Month].Skill, [Employee ...

Outlook From Field Disappears
The from field for the inbox disappeared. When I try to re-add it, Outlook crashes. Office XP Service Pack 3 on a Windows XP Pro workstation. Error Signature: AppName: outlook.exe AppVersion: 10.0.6626.0 ModName: outllib.dll ModVersion: 10.0.6515.0 Offset: 0028e199 I have uninstalled Office, ran regclean and reinstalled to no avail. I also tried replacing the dll from another install of Office. Any ideas? Please email me if possible. Thanks, Rob Coombe ...

text field with input mask
Hi all,I have a text field with field size of 6, input mask of &99.99. So it is ok for me to data entry data like E55.66, or 777.88. Now I need enter E123.45. If I change it to field size 7 and input mask of C999.99, I can enter E123.45, but all of my old data will be wrong. What should I do to keep all the old data right and also be able to enter my now data?Thanks,Blinda "Blinda" wrote:> Hi all,> > I have a text field with field size of 6, input mask of &99.99. So it is ok > for me to data entry data like E55.66, or 777.88. > > Now I need enter E123.45. If...

Fields... what's going on
Hi Sorry, I expect this is really basic. I'm trying to write fields in Word 2000 with the IF command. The problem is that, suddenly, when I try to write them (CTRL+F9), I get the braces fine, but can only input the letter "I" - nothing else. 1 character, and it can only be a capital "i". Can someone tell me why this is, and how to stop it? It worked fine until 1/2 hour ago. *sob* Thanks in advance Steve This newsgroup is for discussions about Microsoft Access, a relational database program. A Word newsgroup would be a better choice for your question. "Cr...

fill in a text box
Hey short question. How do I fill in a text box with TekstAandeel being the name of the textbox in my userform. This is wat I have. In the beginning It's set at "" (empty) but I want to fill it in witch access stuff. Set A = DB.OpenRecordset("SELECT Aandeel FROM DDM Where Ticker='" & TekstTicker.Text & "'", dbOpenSnapshot) TekstAandeel.Value = ??????? ...

Pivot Table Field Names
In previous versions of Excel when dragging a filed to a Row or Column position in a Pivot Table it would automatically draw the field name from the underlying table, in other words if I were to take the field 'Facility Name' from the source data and make it a row item, the header for that row would be 'Facility Name' In Excel 2007 when I do this it creates a header called 'Row Labels'. The same behaviour takes place for Column Labels. I then have to type in the field name I want for each Row or Column Header. Is there any way to have Excel 2007 assum...

Open Other User�s Folder doesn�t show Sent Items
I am looking for a registry hack on the Open Other User�s Folde function in Outlook. My user needs to access the SENT ITEMS folder o her supervisor, but the default folders that are available are onl Calendar, Contacts, Inbox, Journal, Notes, Tasks. This is in th drop-down when you go to File>Open>Other User's Folder. Does anyone know how to alter this behavior and expand the choices o available folders -- bankboysbPosted from - http://www.officehelp.i You will need to use a different approach. To access a shared folder = that isn't one of the folders listed on the File | Op...

using office mac on 2nd computer product codes #2
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Hi-I have Microsoft Office 2008 for Mac (Home & Student Edition). It came with 3 product codes. I installed the 1st product code on a mac lap top and then installed the software on a 2nd lap top, which did not ask for product code. Now, I can't use the 1 computer if the other software is in use on the 2nd or vice versa. I tried to delete the software on 1st computer and reload hoping it would ask for new product code which it did not. How can i reload software and have it ask for different product code? ...

How do I show number of records that meet criteria filter
I have a large spreadsheet containing several hundred rows of data. I want to filter the list and I want to be able to show the number of records that match the filter criteria. For example, un-filtered the total number of records in the list is displayed and, when filtered, the number of records in the list that match the criteria is displayed. Can onyone help me please? Kind regards, Cliff Cliff, Here is a little function that returns the count, and works from VBA. Just pass the header cell to the function Function FilteredListCount(rng As Range) Dim iLastrow As Long Dim rngTemp ...

Need help with changing multiple page fields. #2
I have found some code on the web that will allow me to link pivot table menus together. This way if I have three tables all based on the same info and say change the date in one, the date changes in all of them. I have tested out the code and it works well but... when I pull data from an outside source the code fails because it can not find the data fields. Is it possible to link related pivot table fields using an outside data source like SQL? The code I am using is listed below and this is the link from where I got the code from. It's a great site. Change Multiple Page Fields -- C...

Married names shows in outlook
We have a couple of users that have either married or divorced in the last few years. In Outlook when you enter their name it will display the old name in <> of course this causing some confusion. How do you deal with this. As an example if you look at a user it would be Jill Smith <Joldname> when you enter their name in outlook. We have located this with our AD msExchADCGlobalNames but we feel there has to be some way of handling peoples name changing so that they display properly. We use Exchange 2k3 with 2k3 DC's. "scott f" <scottf@discussions.micro...

scales of 1st and 2nd series
I have a excel 2003 graph with 1st and 2nd series and scales on Y left and Y right axis How can I make it auto scale but equal on left and right ? My chart is dynamic with data change Can I clear the scale (right click at y axis, clear or chart option, unmark the value y axis titles) Question: If I clear the scale of 2nd series will it follow the scale of first series? See your other thread. -- David Biddulph "Daniel" <Daniel@discussions.microsoft.com> wrote in message news:73104665-3FF7-49D7-AE17-DEC5176097AD@microsoft.com... >I have a excel 2003 graph...

Multiple text boxes on a shape?
I am a new Visio user, and was wondering if you can put more then one text box on top of a shape...every time I try it, one of them goes to the back. I can't seem to have them both in the front at the same time and I am pulling my hair out! Help! Thanks in advance, Heather By definition, a shape can only have one text box, but you can group shapes. Within the group, you can specifiy the front to back order. For the component shapes that are text only, set the fill and line colour/pattern to none. John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need V...

Combo Box returning results
Hi, I have multiple combo boxes on a form which I open from the swithboard in add mode. The combo boxes and list boxes are all tied to fields on the same table, which I want updated with a new record when I pull up the form. I want the combo box fields static and to choose from the linked fields and the list box values to be inputed. The list boxes work fine, but while the combo boxes let me choose from the values in the linked fields why don't they show up in the new record? Try under the "OnCurrent" event Me.combobox1.requery HTH, Nick. "aponne1" wrote: ...

How to carture the newly autonumber in the primary field?
In a one to many table relationship, an "Insert" statement to the "one" table would craete a new autonumber for the primary key on a row for the "one" table and this autonumber will then be used as secondary key on the "many" table. What I need is to retrieve that autonumber right after the "Insert" statement and show it to a textbox. Say I have the following codes: Docmd.RunSql "Insert Into Table1...bha bha bha" Me.Autonumber = newly created autonumber Should I use the DMAX function to capture the max. Autonumber or something I...

missing name box
Has anyone ever lost the name box in the upper left portion of the window where the cell location is displayed? Only if the formula bar itself is not displayed (View, Formula bar). -- Jim Rech Excel MVP "John E. Kruskie" <anonymous@discussions.microsoft.com> wrote in message news:259d01c427d5$f62cb960$a501280a@phx.gbl... | Has anyone ever lost the name box in the upper left | portion of the window where the cell location is displayed? ...

sending emails from a pop up email box has never worked for me.
I have just made some changes. Making live.com my default and MSN my home page w/bing as my search engine. I did keep my yahoo account as a secondary. Everytime I tried to email anything from one of these pop up email outgoing forms, it would say who I was trying to send it to but alway said my name and yahoo address could not be recognized as a sender. I'm sending this to you to have a record of it, hopefully the changes I just made will work. If not, maybe you can tell me what I'm doing wrong. Thanks -- hope ...

excel date shows 01/00/1900!
I have a whole spreadsheet where when I type in the date i.e. 3/5/05 it then shows 1/00/00 as the date! I have tried changing the format to the many different ways to show the date, but I can't get it to show 3/5/05! Is there something I am missing? Thank you so much for your help! Katie I'm guessing that you entered the date in the cell like this =3/5/05 If so, a calculation is being done. I get 1/0/1900 when I enter it that way. Try just entering it as 3/5/05 with no equal sign. "KateZito" wrote: > I have a whole spreadsheet where when I type in the date...

Can not delete messages from Sent box in Outlook 2003
I am helping a friend with a computer problem. She is running Windows XP Pro. Within Outlook 2003, she is unable to delete, permanently delete, or move files from her Sent box. She receives the error "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." She has tried running her Inbox Repair Tool, as well as Scan disk. Anyone have any ideas on how to fix this problem? Try creating a new mail profile under control panel->mail icon->show profiles. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep t...

no revenue showing, why?
I'm having trouble properly recording my business income (cleaning service). I enter the deposit (manual check deposit) under 'Service Income', but still in the business snapshot I have zero revenue! If I set up recurring clients it will show up under 'Payables due by...' and like ($240) <== in red, meaning it's a negative bill?! It would be great to see my revenue and expense in the same snapshot. Can anyone help me with that? ...

Last item in Combo Box
I have a combo box where you can select the employee assigned to a project. In the combo box there is also a "Not Assigned to Company" optin that can be selected. Is there a way to sort by employee, but also have this "Not Assigned to Company" as the last item listed on the drop down list even though it not last if put in Ascending order? I've done this by adding a numeric Sort column to the combox's source table and using that in the order by portion of the combox row source. Supe wrote: >I have a combo box where you can select the employee assigned to a...

Creating separate tables for multi-select list box selections
I have read from some of the other posts that it is necessary to create a separate table for each of the selections made in a multi-select list box from a form. I don't understand this concept! I have a form with 4 multi-select list boxes and many other fields that populate a table with a simple query attached to it. How do I create this separate table for the selections from the list boxes and link it to the main table and query? I am not an Access programmer and am learning as I go along. Please explain step by step and as simply as possible. Thanks for any and all help in a...

pictures not showing
And constant notices when replying to emails that a picture is not showing up. I think my settings are correct. This problem began a few days ago--until then I could see pics. Is it a msft update problem? sue xp pro; sp2, ie7, oe6.2900,2180 (wish i could copy all that) It's not an update. You haven't even installed Service Pack 3 yet. Are you having trouble receiving and sending pix? Make sure you are sending and reading in HTML and not plain text. Tools | Options | Send | HTML Settings. Check: Send pictures with messages. Send a pix to yourself. Do ...

Message box with Combo box function
Is it possible to create a combo box within a message box i.e. whenthe user clicks on preview report a message box appears and says"which report do you want to open?" and a combo box displays thedifferent reports available with the user clicks, upon clicking therelevant report opensThanks * Asif wrote:> Is it possible to create a combo box within a message box i.e. when> the user clicks on preview report a message box appears and says> "which report do you want to open?" and a combo box displays the> different reports available with the user clicks, upon clickin...