Combo Box Value to Automatically Put Value From Different Variable into Text Field

[aikiai]

I have requested this before but the example given did not fit my situation and I had a heck of a time trying to adapt the example given to me so I will step by step describe what I need. thanks...


Can someone show me how to do the following:

1) Take a combo box that looks up a table that has 2 variables (Var1 and Var2).

2) Depending on the value chosen in the combobox (Var1) then a text field will automatically have the value (Var2) appear.

3) This information from combo box and text field will then be stored in a separate table that is linked to the form.

_______________________________________________________________

Also:

Is there a way to extend this example further by adding another combo box that takes the result from the first combo box to filter in values for the second combo box which will then automatically populate a text box which would then be stored in a separate table.

_______________________________________________________________

Someone kind of showed me before but they used unbound variables and this confused me as the combobox was unbound but how is that information stored into my table?

Pardon me for my redundancy but this is frustrating me to no end and I unfortunately have a deadline of today!!!!

I think it is the row source thing that confuses me. I take row source to mean where the data is stored. But where do you identify where to obtain the data to then store in your "inputed" table?

 

[aikiai]

I found this database on this forum that does what I want but again I don't "see" how it works. It does not seem to store the variable typedesc.


[EDIT]

Again this does not do exactly what I want. It appears it shows both the type and type desc in the combo box. I don't the person entering data to see the type desc in the combo box. Just the type with the type desc automatically filled in the text box.

ARGHGHGH!...*sigh*

 

[RichO]

Some of your terminlology may be causing confusion. A table doesn't hold variables, but rather, fields. Row Source refers to where the combo box gets its records from (name of the table or hand typed values).

Anyways, you are saying you want a combo box to auto-fill a text box upon selection. You can set up a combo box to use 2 columns but only show one by simply setting the width property of one of the columns to 0". For instance, if you only want the first row to diaplay in the combo and the 2nd row to appear in your textbox, set the width property to 1";0" and in the control source for you textbox, your can enter:

=[MyComboBox].Column(1)

Keep in mind that the columns start at 0 so column(1) is actually the second column.

 

[aikiai]

Thank you for your response. Yes I do admit my terminology is not exact and I do apologize for that. Okay so how do you get the values that are selected in the combo box and the text field to be "stored" into your "other table" Other table being the table that has all the fields with the stored data. NOT the table that is being used to look up the values for the combo box.

And the value from the combo box is stored into a variable with a different name.

_____________________________________________________________

 

[RichO]

The text box that you store the value in needs to be bound to a field in your form's underlying table. Look at the control source property for your text box and select the field you want the value stored in.

 

[aikiai]

The text box that you store the value in needs to be bound to a field in your form's underlying table. Look at the control source property for your text box and select the field you want the value stored in.

Sorry RichO but you are confusing me. Earlier you stated the control source property is the value to get the data. Now you are saying this is the value to where the data is stored. So bascially where do I put the code to grab the data? Since you have now stated I use the control source to store the data. Again sorry but I am really confused....

 

[ansentry]

Have a look at the attachment, is this what you want to do?

Note: I have cut down the number of states and towns in the list to save space.

This will show you how a cascading combo box works and how to store the value of the selection. Also how to extract the information using a query.

 

[RichO]

Control Source determines what is displayed in the text box, but if the control source is a field from a table, that is also where the contents of the text box is stored.

A combo box on the other hand has a Control Source and a Row Source. Row Source is where it gets its list of records from, and again, Control Source is where the selection made will be stored.

To clarify my statement from earlier, if you want to store a value from a combo box row into a textbox, but still have that textbox bound to a field in a table, you would need to place a piece of VBA code in the combo box's After Update event.

Me.MyTextBox = Me.MyComboBox.Column(1)

This is simply because you can't have 2 control sources for one text box.

 

[crev]

Control Source determines what is displayed in the text box, but if the control source is a field from a table, that is also where the contents of the text box is stored.

A combo box on the other hand has a Control Source and a Row Source. Row Source is where it gets its list of records from, and again, Control Source is where the selection made will be stored.

To clarify my statement from earlier, if you want to store a value from a combo box row into a textbox, but still have that textbox bound to a field in a table, you would need to place a piece of VBA code in the combo box's After Update event.

Me.MyTextBox = Me.MyComboBox.Column(1)

This is simply because you can't have 2 control sources for one text box.

This is a great thread and has solved 90% of my problem. However, if someone could just explain the above naming conventions, I should be set, and would be very grateful.

If I want to store the text box value (which is fed a value from my combo box per the following code in the text box control source.....=[Combo40].Column(2)) into a table, what VBA code do I add to the combo box's Update Event? The table I want the text box values stored in is named "QA Data". I couldn't tell based on the Me.MyTextBox = Me.MyComboBox.Column(1). It doesn't seem to mention a table. Is that what the "Me" denotes?

Summary:
Combo box name: Combo40
Name of four text boxes fed by above combo box: Region, Division, Sales Mgr, Division Mgr
Name of table where I would like ALL text box data saved to: QA Data

 

[RichO]

Is the table "QA Data" the record source for your form? If not, why are you wanting to store a single value into a table? Are you attempting to update an existing field within a record or start a new record with the entry?

"Me." simply refers to the object you are working in (form or report). When you say Me.Combo40 you are referring to the combo box named Combo40 in the form you are coding in. Although Me. is not required, I like to use it because when you type "Me." VBA gives you a list of the controls where you can type the first few letters and auto-complete the name.

 

[crev]

Is the table "QA Data" the record source for your form? If not, why are you wanting to store a single value into a table? Are you attempting to update an existing field within a record or start a new record with the entry?

"Me." simply refers to the object you are working in (form or report). When you say Me.Combo40 you are referring to the combo box named Combo40 in the form you are coding in. Although Me. is not required, I like to use it because when you type "Me." VBA gives you a list of the controls where you can type the first few letters and auto-complete the name.

I have a form that feeds data into the "QA Data" table. The table stores all kinds of sales rep information. The reason I use the combo box is because the user can select the rep name from a drop-down box (created by using lookup wizard and a simple select query), and then I have it automatically populate the Division, Region, Sales Manager, Division Mgr fields in the text boxes of the form (by using the "Combo40.Column(1-4)" trick you showed us). However, right now, on the form itself, everything populates fine as I work through the fields, but the only fields that don't get added to the table are the four I just listed--the text boxes being fed by the "Combo40.Column(1-4)" command in the control source of the properties.

Does that make sense? I've been trying to figure this out for a few hours now. So, long story short, my text boxes that are automatically populated are not feeding that data to the underlying table. I didn't add any VB to the combo box's event section because I wasn't sure what exactly to put.

Thanks in advance for your input.

 

[RichO]

Check the control source of those text boxes. Are they bound to the proper fileds (if any) in your table or are they unbound?

 

[crev]

Check the control source of those text boxes. Are they bound to the proper fileds (if any) in your table or are they unbound?

The control source for the text boxes is tied to the combo box. Each of the four text boxes has: Combo40.Column(1)....or (2) or (3) or (4) depending on the column I need. That's my problem--the control source is tied to the Combo box, and therefore whatever populates the text boxes while using the form does not write to the underlying table.

 

[RichO]

OK, you need to use VBA to populate those fields. First, select a new control source of each box to bind them to the appropriate fields in your table. Take note of the names of your text boxes and substitute them in the code below.

Then use the After Update event of your combo box:

Me.txtBox1 = Me.Combo40.Column(1)
Me.txtBox2 = Me.Combo40.Column(2) ..... etc....

 

[crev]

OK, you need to use VBA to populate those fields. First, select a new control source of each box to bind them to the appropriate fields in your table. Take note of the names of your text boxes and substitute them in the code below.

Then use the After Update event of your combo box:

Me.txtBox1 = Me.Combo40.Column(1)
Me.txtBox2 = Me.Combo40.Column(2) ..... etc....

Ok, I'm not sure I fully understood you, but here's what I did: I went to the control source of each of my text boxes (which I want fed into the underlying table) and changed each one from [Combo40].Column(1-4) to just tying right to the relevant field in the table. Then, I went to my combo box (Combo40) and added the following code to the Event tab's After Update field:

Private Sub Combo38_AfterUpdate()
Me.Region = Me.Combo40.Column(4)
Me.Division = Me.Combo40.Column(3)
Me.DOS = Me.Combo40.Column(2)
Me.RMS = Me.Combo40.Column(1)
End Sub

I get the following error when I try to use the combo box in the form:

Compile Error: Method or data member not found

Any thoughts? Should I not have the "Me" in there?

 

[crev]

Ok, I'm not sure I fully understood you, but here's what I did: I went to the control source of each of my text boxes (which I want fed into the underlying table) and changed each one from [Combo40].Column(1-4) to just tying right to the relevant field in the table. Then, I went to my combo box (Combo40) and added the following code to the Event tab's After Update field:

Private Sub Combo38_AfterUpdate()
Me.Region = Me.Combo40.Column(4)
Me.Division = Me.Combo40.Column(3)
Me.DOS = Me.Combo40.Column(2)
Me.RMS = Me.Combo40.Column(1)
End Sub

I get the following error when I try to use the combo box in the form:

Compile Error: Method or data member not found

Any thoughts? Should I not have the "Me" in there?

Not sure why the top line of code included "Combo38" but I changed it to "Combo40" and I don't get an error, but none of the text boxes populate--even in the form.

 

[RichO]

Are Region, Division, DOS and RMS the names of your table fields or the names of the text boxes in your form?

When doing this, refer to the name (in the properties Other tab). It's recommended to use names like txtRegion, txtDivision, txtDOS, txtRMS as opposed to duplicating the actual field names.

 

[crev]

Are Region, Division, DOS and RMS the names of your table fields or the names of the text boxes in your form?

When doing this, refer to the name (in the properties Other tab). It's recommended to use names like txtRegion, txtDivision, txtDOS, txtRMS as opposed to duplicating the actual field names.

I changed the names of the text boxes to txtRegion, txtDivision, etc....and NOW IT WORKS!!!!!!!! WOW!

The only "glitch" now is that when I select a sales rep's name from the drop down menu in the form (on the combo box), and then press tab, it goes right to the next record (rather than to the next field--there are several). I went into the combo box properties and changed the "Limit to List" from No to Yes. Now, it does the same thing....BUT...if I select a rep name in the combo box, and then CLICK into the next field, I can use the tab button fine for the rest of the record. So, that tells me I need to change something in my combo box to allow me to tab out of it without going to the next record. Any ideas? I really appreciate your help so far.

 

[crev]

I changed the names of the text boxes to txtRegion, txtDivision, etc....and NOW IT WORKS!!!!!!!! WOW!

The only "glitch" now is that when I select a sales rep's name from the drop down menu in the form (on the combo box), and then press tab, it goes right to the next record (rather than to the next field--there are several). I went into the combo box properties and changed the "Limit to List" from No to Yes. Now, it does the same thing....BUT...if I select a rep name in the combo box, and then CLICK into the next field, I can use the tab button fine for the rest of the record. So, that tells me I need to change something in my combo box to allow me to tab out of it without going to the next record. Any ideas? I really appreciate your help so far.

You still there RichO?

 

[RichO]

Select Tab Order from the View menu. That will allow you to change the tab order within the form. Obviously your combo box is currently the last one in the order.