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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

695 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