Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rating: Thread Rating: 26 votes, 5.00 average. Display Modes
Old 07-05-2012, 02:22 AM   #1
cyril.casia
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
cyril.casia is on a distinguished road
Auto populate text field on form based on Combobox selection

Hi All,

I'm trying to create a database for the memberships in our office. I'm new to db and thus confused at many instants.

Let me explain the scenario:
I have a Table "tblmemberships" which has fields such as Membership No(Primary Key), Member Name, Membership Plan, Date of Joining, DOB,...........Client ID, Associate Name.....etc. Also have created a Form "frmmemberships" to input data into this.

Now I need to keep track of the profits earned for every member under the Client ID. I have created a new Table "tblTradingAnalysis" which has fields such as Client ID, Membership No, Member Name, Date, Product, Buy, Sell, Profit..... and I have created a Form "frmTradingAnalysis" to input data to this table.

As soon as the user enters the ClientID "txtClientID", I want the MembershipNo & MemberName to be auto populated in the text fields, "txtMembershipNo" & "txtMemberName".

Kindly help me as to how to do this... Any advice would be appreciated...!!!!

Regards
Cyril

cyril.casia is offline   Reply With Quote
Old 07-05-2012, 02:31 AM   #2
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Auto populate text field on form based on Combobox selection

You can use the following to populate an unbound text box based on the selection made in your Combo Box;
Code:
=[ComboBoxName].Column(X)
Where X is number of the column that holds the data you wish to display in the text box. Remember that the columns in a Combo Box or even List Box are numbered from Zero on up.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-05-2012, 02:57 AM   #3
cyril.casia
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
cyril.casia is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

Thanks for your reply.

I tried it, but it doesn't work for me....

Now I have a Combobox "cboClientID" in "frmTradingAnalysis" with properties as follows:
Control Source: ClientID
Row Source: SELECT tblmemberships.[Client ID], tblmemberships.[Membership No], tblmemberships.[Member Name] FROM tblmemberships;
Row Source Type: Table/Query
I have kept the Column Count as '3' and the Column Width as 1";0";0".

Now when I open the form I get the Client ID's in the Combobox and the txtMembershipNo shows "#Name?" as I open the form.

Pls help....

cyril.casia is offline   Reply With Quote
Old 07-05-2012, 03:05 AM   #4
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Auto populate text field on form based on Combobox selection

Have a look at the attached sample.
Attached Files
File Type: zip Pop TextBox from Combo.zip (529.2 KB, 5021 views)
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
The Following User Says Thank You to John Big Booty For This Useful Post:
cyril.casia (07-05-2012)
Old 07-05-2012, 04:00 AM   #5
cyril.casia
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
cyril.casia is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

Please find the attachment. Have a look at it and you may then understand the need clearly.

Regards
Cyril
**************
Sorry... had thought of uploading it here but it doesn't permit as its more than 2MB... Please share if any other option...

Last edited by cyril.casia; 07-05-2012 at 04:03 AM. Reason: forgot to attach file
cyril.casia is offline   Reply With Quote
Old 07-05-2012, 07:48 AM   #6
Sketchin
Newly Registered User
 
Join Date: Dec 2011
Location: Calgary
Posts: 575
Thanks: 107
Thanked 41 Times in 40 Posts
Sketchin is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

Is the control source of your textbox,
Quote:
=[cboClientID].[Column](1)
?

Last edited by Sketchin; 07-05-2012 at 07:48 AM. Reason: wrong column #
Sketchin is offline   Reply With Quote
The Following User Says Thank You to Sketchin For This Useful Post:
deliaraz (07-20-2017)
Old 07-06-2012, 12:21 AM   #7
cyril.casia
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
cyril.casia is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

I have got it sorted... Thank you for your help.

I have another doubt... Pls help if you dont mind...

As I had mentioned I have the table "tblTradingAnalysis" which has fields such as
TransactionID*, ClientID, TradingDate, Product, BUYprice, SELLprice, NETValue, Weight, Unit, Gross P&L, Brokerage, Total...

And I have the form which has bound textboxes for the user to input data into this table. The user needs to provide data for the fields ClientID, TransactionDate, Product, BUYprice & SELL Price only and the remaining fields must be automatically calculated based on formula.

Now I'm trying to input a formula for the textbox "txtNETValue" which will generally be =(txtBUYprice * 5) + (txtSELLprice * 5) but I do not know how to write a code for this and where to input it. I want the txtNETValue to display the result automatically once the user inputs both the txtBUYprice and txtSELLprice and also this data must be stored in the particular field in the table "tblTradingAnalysis"...

I'm just learnin db and stuck up here... kindly help....

cyril.casia is offline   Reply With Quote
Old 07-06-2012, 12:35 AM   #8
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Auto populate text field on form based on Combobox selection

You could just put your formula in the Control Source of an unbound Text box. Something along the lines of;
Code:
=([txtBUYprice] * 5) + ([txtSELLprice] * 5)
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-06-2012, 12:38 AM   #9
cyril.casia
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
cyril.casia is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

I treid... but this doesn't work for me....

It shows "#Name?" in the txtNETvalue.

I would like this value to also get saved in the table.... Pls help....
cyril.casia is offline   Reply With Quote
Old 07-06-2012, 12:47 AM   #10
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Auto populate text field on form based on Combobox selection

Try using the Ellipsis button next to the right of the Control Source field to build the expression.

Storing calculated values as you are planning to do is generally considered to be in breach of the rules of Data Normalisation, given that you are able to calculate the field in this instance, you will be able to calculate it in all future instances, assuming your DB is properly normalised.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-06-2012, 02:15 AM   #11
cyril.casia
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
cyril.casia is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

If so, then can I use the formula in the table to calculate the field and store it there... May be then I was retrieve all these data through a Query...

i.e. User will input the ClientID, TradingDate, Product, Unit, BUYprice & SELLprice and the table will have the other fields too where the data after calculation is stored and later retrieved... Is it possible this way?

Please help....
cyril.casia is offline   Reply With Quote
Old 07-06-2012, 06:22 AM   #12
Sketchin
Newly Registered User
 
Join Date: Dec 2011
Location: Calgary
Posts: 575
Thanks: 107
Thanked 41 Times in 40 Posts
Sketchin is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

You shouldn't do it that way. Like John says, the only data you need to actually store is buy and sell price. '

I would create a query and perform all of your calculations there and reference them in textboxes on your forms and reports. Storing calculated values is a very bad idea.
Sketchin is offline   Reply With Quote
Old 12-10-2012, 11:25 AM   #13
sun_3
Newly Registered User
 
Join Date: Nov 2012
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
sun_3 is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

Quote:
Originally Posted by John Big Booty View Post
You can use the following to populate an unbound text box based on the selection made in your Combo Box;
Code:
=[ComboBoxName].Column(X)
Where X is number of the column that holds the data you wish to display in the text box. Remember that the columns in a Combo Box or even List Box are numbered from Zero on up.
Hi,

I have tried this but it keeps saying to debug the error?
How do you know what number the column is?
Will they still be numbered from 0 up if you originally have a normal form, but afterwards change the field in the form to a combo box in order to auto populate it?

Thanks
sun_3 is offline   Reply With Quote
Old 12-10-2012, 12:43 PM   #14
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Auto populate text field on form based on Combobox selection

Have a look at the Combo's Row Source in design view, figure out which column is holding the data you wish to display in the text box and count the columns.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-19-2017, 06:29 PM   #15
deliaraz
Newly Registered User
 
Join Date: Aug 2015
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
deliaraz is on a distinguished road
Re: Auto populate text field on form based on Combobox selection

Quote:
Originally Posted by Sketchin View Post
Is the control source of your textbox, ?
I know this post is OLD but I just have to say THANK YOU!! I've been struggling for months trying to get my combo box to update my text box. I could NEVER make it work in MS Access 2016 AT ALL and it worked in 2013 but stopped working the moment I created a copy of the database or opened it with 2016.

In the end, your suggestion has finally given me peace and my headache is gone. THANK YOU!!!

deliaraz is offline   Reply With Quote
Reply

Tags
auto populate , automatic insertion , combo box , data entry

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto populate fields based on drop down selection VainDM Forms 1 11-01-2011 08:16 AM
AutoPopulate field on form based on combobox selection scatz Forms 3 08-20-2008 01:02 PM
Auto-populating form fields based on the selection in another field Rabbitoh Modules & VBA 0 05-23-2007 12:50 PM
Populate a (different) text box based on selection made in a list box - same form. marydoyle9 Forms 5 01-19-2005 08:36 AM
populate text field based on list box selection Stew Forms 2 12-15-2000 06:30 AM




All times are GMT -8. The time now is 04:27 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World