Use Field1 to update Field2 on same unbound form

[hmsdefender]

I have a form with an unbound listbox "PList", an unbound textbox "PText", and a SUBMIT button. The listbox is populated using a SELECT statement against a field called "LName" in a table called "TheList". I'd like the textbox to display the value of a field called "LDesc" in "TheList" that is in the same record as the currently selected value of "LName" in the list box. When the user hits the SUBMIT button, I want to capture the value of the selected entry in "PList" and pass it to another form I open via the SUBMIT button. I have a limited knowledge of Access and SQL. What I want to do seems possible, but I don't know enough to know where to look for the right information.

Thanks,
hmsdefender

 

[fneily]

AfterUpdate of the listbox, use a Dlookup:

Me![Ptext].Value = DLookup("[LDesc]", "TheList", "[Lname] = " & Forms!Formname!Plist)

Replace Formname with the form's name.
Also, you can highlight Dlookup and hit the F1 key to see the syntax and examples of its use.

If you keep the form OPEN, you would use the
Forms!Formname!Plist
on the next form to reference it.

 

[hmsdefender]

I entered the suggested code directly into the "After Update" line in the properties for the listbox. When I open the form, the textbox is blank. When I select an entry in the listbox I get an error from access that it can't find the macro 'Me!PText.' Any ideas?

hmsdefender

 

[fneily]

Ok. Let's start over.
In design view, click on your combobox. Bring up the property sheet. Click on the event tab. Click in the box next to OnClick. You'll see a button with three dots. That's called a build button. Click on the button. You will select Code Builder. This throws you into VBA. On the right, you'll see a big pane with Sub PList_OnClick and End Sub. In between these words you would place the code. The you exit VBA and go back to Form View.