selecting name on one form, displaying it in another

[roddy17]

hi there, again many thanks to those who continously take time to volunteer answers to others techy problems.
On the primary form i have a combo box where i can select an investment advisor to be associated with a particular product. The product table stores the Investment Advisor selected in the combo box. That is good. Beside the combo box however, i have a command button which is supposed to open another form displaying all related information about that investment advisor. The related information is displayed, but instead of the investment advisor's name showing up (in the name field), i get the autonum associated with that investment advisor.
The following is the code associated with the event procedure on the command button.

' Need to be sure that the "Investment Advisor" textbox is populated so that
' there is information to be displayed on the form to be opened.
If Me!cboInvestmentAdvisor <> &quot;&quot; Then
DoCmd.OpenForm stDocName
Forms![Investment Advisor form]![txtInvestment Advisor] = Me.cboInvestmentAdvisor
Else
DoCmd.RunMacro &quot;MsgBox - No InvestAdvisor&quot;
End If

Just wondering if something could be added to display the Investment Advisor name instead of the autonumber.
The control source of the Investment Advisor name on the second form is set to the Investment Advisor Name field of the Invest Advisor table and not the autonum.
thanks vm ........... roddy

 

[fchan]

Are the values from the combo box stored in a separate table? If they are you might want to create a query that pulls all the related information and then set your forms source to the query. Then you should be able to display the advisors name.

 

[roddy17]

hi there fchan
yes the values from the invest advisor combo box are stored in a separate table. I have the tables relating to each other (I'm not sure if that is necessary though). The main table has a field which uses a query for its row source in order to look up the value in the Investment Advisor table.
As well, the combo box field on the primary form is based on a query which displays the records from the Invest Advisor table.
Let me know if i am being unclear, looking at this explanation, it seems as if it might be.

 

[MangroBongacello]

Roddy

obviously you have the combo box bound to a autonum field. Perhaps it would solve your problem if you just specify from which column in the combobox to take a value to the second form.

Forms![Investment Advisor form]![txtInvestment Advisor] = Me.cboInvestmentAdvisor.Column(x)

where x is the sequence number of the column in combo box, in which the Investment advisor's name is displayed. Note that columns start counting from 0, so the first column has index 0, the second 1 and so on.

Hope it helps.

 

[roddy17]

hey there, me again,
thanks for the advice, i went to implement your suggestion and it worked. woo hoo!! (my best homer simpson)
The obvious next question, how do i tie in the rest of that record so that it also is displayed in the form along with the Investment Advisor name. I just realised that the fields being displayed on the form were the info from the first record in the Invest Advisor table, not necessarily the record i had selected to view.
I will show you the code i have tried to manipulate:

If Me!cboInvestmentAdvisor <> &quot;&quot; Then
stLinkCriteria = &quot;[Investment Advisor] = &quot; & Me![cboInvestmentAdvisor].Column(1)
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog
Else
DoCmd.RunMacro &quot;MsgBox - No InvestAdvisor&quot;
End If

I've tried a couple of different things with this code, but i've gotten a few error messages.
thanks

 

[MangroBongacello]

I'm not sure here what you want to do. From your quetion I would assume you want to diplay some other data about Investment Advisor in the form you are opening, but why do you then use acFormAdd as the argument? This will only allow you to add new records. If my assumption is correct

1. Drop the acFormAdd argument
2. If Column(1) is text, use single quotes when assigning a value to stLinkCriteria variable
&quot;[Investment Advisor] = '&quot; & Me![cboInvestmentAdvisor].Column(1) & &quot;'&quot;

Investment Advisor is the name of the text box in the second form where the Advisor's name is displayed, right?