Populate two fields with one combo box

[Michael Adams]

I have been working on this form for quite some time with alot of help from this forum and its users. Everyone has been very helpful and there are great solutions from everyone. I also posted a useful pice of code for querying a date range along with three different requirements. You can find that solution here:

http://bytes.com/topic/access/answers/891805-using-dates-search-query-records

Now for my question: I would like to be able to write to my form two separte fields. The first data is in the combo box being selected by the user, the second would be according to the first, ie. MWB(1st) (combobox) Mountain West Bank (2nd). The data would populate the fields [branch] and [bank] respectfuly.


Looks as though the command I am looking to execute is an insert/inserttext one.

Thanks in advance

[jimatqsi]

Perhaps what you want is to make the combo box query have two columns. The first column will hold the MWB value, the second column will have the "Mountain West Bank" value. The combo box will be bound to field [branch].

Separately, you will need a textbox txtBankName bound to [bank].

In the AfterUpdate event of the combo box, add this code:

me!txtBankName = me!comboboxname.Column(1)

.

That will plug the Bank name into the text box whenever a selection is made in the combobox.

Jim

[Michael Adams]

I might have explained it wrong, either that or I am not understanding the answer Jim. I have a form that populates a table and in this form I have a combo box with branch data(we have over 100 branches; however, we only have 11 banks) so, in other words there are 100 different rows to one field in a table and only 11 rows in another table.

What I would like to do is when the user selects the branch the work was done at the form will populate both fields in a master table. How do I code that to happen in vba on the back end?

[Michael Adams]

I was going down the wrong path, ie. trying to use a wildcard in a select case statement. Just FYI Wildcards are not recognized in a case statement!

This is what my final code looks like thanks to Jim.

Private Sub Bank_Combo_AfterUpdate()
Me.Bank.Value = Me.Bank_Combo.Column(1)
End Sub