DLookup Based on Combobox Selection (1 Viewer)

[NewUse]

I need to populate a textbox in my form based on a field called "CourseDescriptions" from a table called "Courses." The ComboBox lists the the courses for the user to choose and once they've made a selection, I want the textbox to populate the CourseDescription. The ComboBox is named "CourseName" For other fields I'd been using =[CourseName].(column)(1,2,3 etc). with my row source set for the combobox. I can't use this for the "CourseDescription" textbox because it truncates the text. Therefore I had been using a DLookup to pull the whole memo field from my table.

However, I cannot get the code to pull from my CourseDescription field according to the Combo Box selection. What criteria expression should I use to accomplish this? Please help?!

 

[missinglinq]

You're correct in thinking that the DLookup() Function is the answer to your dilemma. Assuming that the first Field/Column in the Combobox is the Bound Field and holds a Unique Identifier for the Record (I inferred this because you didn't reference CourseName.Column(0) in your post) this code should do the trick. Since that Field name was the one thing you didn't give us, for simplicity sake I'll call it UniqueIdentifier. The exact syntax will vary, depending on the Datatype for the Unique Identifier.

If UniqueIdentifier is defined as Text

Private Sub CourseName_AfterUpdate()
 Me. CourseDescription = DLookup("CourseDescription", "Courses", "[UniqueIdentifier]= '" & Me.CourseName & "'")
End Sub

If UniqueIdentifier is Numeric

Private Sub CourseName_AfterUpdate()
 Me. CourseDescription = DLookup("CourseDescription", "Courses", "[ UniqueIdentifier]= " & Me.CourseName)
End Sub

I believe I got all of the other names correct, but it has been a long day! Substitute your actual name for the Unique Identifying Field in the code and give it a whirl!

Linq ;0)>

 

[NewUse]

I'm sorry, maybe the problem that I'm having is more fundamental because I don't know where I should put that code to have it execute properly. I'd been using the simple Control Source function but even when I went into the BeforeUpdate VBA and entered that code nothing happened...

Currently in the Control Source Box I'd been using something similar to what you gave me but it was not working to pull the correct description from my table (or to pull anything).
=DLookup("CourseDescription", "Courses", "[Course Code]= '"& Me.CourseName &"'")

Course Code is defined as text.