populate a texbox using selection from listbox

From: Alexander Heimann (AlexanderHeimann_at_yahoo.com)
Date: 05/28/04 

Date: 27 May 2004 20:12:59 -0700

I will apologize first if this is longwinded here is my situation. I
wanted to create a form where users can add comments about part
numbers in a database. There are hundreds of part numbers and 25
users. I set the database up as

Part Number table has three fields
PK Part_no
   user_name
   code

comments table has two fields
  Part_no
  comment

there is a one to one relationship.. only one comment per part number.
there is no need to save older comments. new ones will erase old ones

for ease of use i created a combo box which uses a query to populate
all the users name. once a user selects the name it fills a listbox
with the parts that the user is associated with, I did this by putting
[Forms]![formName]![comboBoxName] in the criteria

i got this far ok, now i want to create a textbox which will display
the comment associated with the part selected in the listbox, i tried
using the same query and criteria method above for the comment but I
realized that i can't make updates to the table that way because i
need to use the table as the recordsource i believe. i am having
trouble coding what i need to select the comment for the part number
selected and allowing me to edit and change it

do i need to use a subform? i don't think i do since this is a one to
one relationship?

thanks in advance..

alex