Solved

How do I return a parsed value in an access form textbox based off of the value that the user selects from another combo box on the same form

Posted on 2011-03-20
3
301 Views
Last Modified: 2012-05-11
Hi Experts,

I have the following values in a table:

ID         Name
1          1 - Elmo
2          2 - Cookie Monster

I have these two fields on a form in access.  When a user selects the ID from a combo box on the form, I would like for the textfield under the combo box to populate with the name.  So, if I choose 1 from a combo box, then I would like for 'Elmo' to automatically populate in a textbox on the form below it.  Is this possible?
0
Comment
Question by:daintysally
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 167 total points
ID: 35178346
If both columns (ID and Name) are selected in combobox, you can set in after update event for your combobox:
Me.MyTextBoxName = Me.MyComboBoxName.Column(1)
0
 
LVL 9

Assisted Solution

by:borki
borki earned 166 total points
ID: 35179132
You were looking to parse the actual combo field? Assuming the name field data is always in the format:

<number><space><hyphen><space><name>

Your ComboBox_AfterUpdate event procedure must parse the name column as 'als315' suggests, but you may want to add:

Dim i As Integer
i = Nz(Instr(1, Me.MyComboBoxName.Column(1), " - "),0)
If (i > 0) Then
   Me.MyTextBoxName = Mid(Me.MyComboBoxName.Column(1), i)
Else
   MsgBox "Error in data!"
End If

HTH
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 167 total points
ID: 35218511
I'm not sure what you are after.
You have a combo box.
Does it display more than one column
ie
1   |   1 - Elmo
2   |   2- Cookie Monster

Or does it just display
 
1
2

Now, < textbox on the form below it>

Is that a separate control on the same form?
Is that a separate control on a subform?
Is that the dropdown list of the combo box?

As for having it display 'Elmo', why not have your table structured like this?
MonsterID   MonsterNumber  MonsterName
1                     1                       Elmo
2                     2                       Cookie Monster

Your MonsterID should be an autonumber, that the user never sees
MonsterNumber will be a number that you give that the user does see
and MonsterName.

Your Combo box could have three columns
Bound Column 1
Width 1.25"
Column widths 0;0.25;1

Now, there is nothing to parse.
The after update event just becomes
Me.MyTextBoxName = Me.MyComboBoxName.Column(2) 'column numbers start at zero, this is the text from the third column of the selected combo box item.

If that textbox is on a subform, the syntax is a little more tortured.
Let me know if that's the scene

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question