We help IT Professionals succeed at work.

Filling Memo sized Fields using a Combo Box Selection

JWSENDAI
JWSENDAI asked
on
533 Views
Last Modified: 2011-10-03
Is there a way to select a value from a combo box and have the selection trigger the filling of a memo sized text field?  For instance, if I were to choose the word "Boston Terrier" from a combo box can I cause the action to fill a field on my form that is a description of the dog that is longer than 255 characters?  I have the filed "Dog_type" and the description of the dog in the same table.  I tried to pass the memo field "Description" through the combo box with the "Dog_type" field but learned that combo boxes only handle text size fields of 255 characters max.  Thanks.

Jack
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
If you were trying the "push" method, you're probably right. YOu could try this method:

assumptions: the first column of your combo is the ID of the record containing the memo field you want to find

Sub YourCombo_AfterUpdate()

Dim rst As DAO.Recordset

set rst = currentdb.OpenRecordset("SELECT YourMemoField FROM YourTable WHERE YourID=" & Me.YourCombo.Column(0)

In not (rst.EOF and rst.BOF) Then
  Me.YourTextField = rst("YourMemoField")
End If

rst.close
set rst  = nothing

End Sub

Commented:
Me.MyMemoField = DLookup("MemoField", "MyTable", "ID=" & Me.cbxDogID.Value)
Mike EghtebasDatabase and Application Developer

Commented:
Hi Jack,

It seems your memo filed contents are generic descriptions, if so do following:

- Make a table called tDescription with three fields: DescID (autonumber), DescName (text) ,and Desc (memo).
-Change the row source of your cbo box (cboSelectMemo) to:  
             Select DescID, DescName, Desc From tDescription Order By DescName
-Add a new field to your data table (in the record source of your form).  Call this field Desc_ID (number/long).
-Add txtDesc_ID to your form (bound to Desc_ID)
-Add tDescription to your record source and link Desc_ID to DescID and Desc to include it as control source to text box on your form called txtMemo.

-In the after update event of cboSelectMemo include:

txtDesc_ID =cboSelectMemo

- In the on current event of your form add:

cboSelectMemo=txtDesc_ID
------------
This way, if a memo has to be revised, you will do it on time and in only one place.  Also, you can add a DescSort field to you tDescription to make sure info get sorted the way you would want it to.

Mike
jadedataMS Access Systems Creator
CERTIFIED EXPERT

Commented:
Greetings JWSENDAI!

  I might have missed something in the reading but would a table of these description that are linked(joined) in upon selection save some database space and be more effective in reporting??

  Use the combobox to select a "short" description or ID, that is fully described in a Memo field in the reference table.

  Just a thought.

regards
Jack

Author

Commented:
When attempting to use the method described in the first suggestion I get a error message that states "User-Defined type not defined".  The VB Editor box also comes up and the "rst as DAO.Recordset" is highlighted.  I assume that this means that I do not have a reference to the correct library.  What is the work around here?

Jack
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
The "workaround" is to set the reference to the DAO library, which is named "Microsoft DAO xx Object library" where xx is your particular version number. Open you code window, click Tools - References and find that library and check it.

Author

Commented:
My version is 9.0 and it is set.  Is the syntax that you provided version 7.0, and if so should it be written out the same as you described?

Sorry for the trouble, but thanks so much for the help.

Jack
Database and Application Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.