Auto populate text field on form based on Combobox selection

[cyril.casia]

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

 

[John Big Booty]

You can use the following to populate an unbound text box based on the selection made in your Combo Box;

=[ComboBoxName].Column([B][COLOR="Red"]X[/COLOR][/B])

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.

 

[cyril.casia]

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....

 

[John Big Booty]

Have a look at the attached sample.

 

[cyril.casia]

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...

 

[Sketchin]

Is the control source of your textbox,

=[cboClientID].[Column](1)

?

 

[cyril.casia]

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....

 

[John Big Booty]

You could just put your formula in the Control Source of an unbound Text box. Something along the lines of;

=([txtBUYprice] * 5) + ([txtSELLprice] * 5)

 

[cyril.casia]

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....

 

[John Big Booty]

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.

 

[cyril.casia]

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....

 

[Sketchin]

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.

 

[sun_3]

You can use the following to populate an unbound text box based on the selection made in your Combo Box;

=[ComboBoxName].Column([B][COLOR=red]X[/COLOR][/B])

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

 

[John Big Booty]

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.

 

[deliaraz]

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!!!

 

[candigirld]

I was able to use the "=[ComboName].Column(X)" expression successfully but it won't work when the field contains an email address. Is there a way around this?