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
285 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
3 Comments
 
LVL 39

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

867 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now