populate a texbox using selection from listbox

[Alexander Heimann]

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

 

[Marshall Barton]

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?

Since the comments are in a separate table (which is a good
idea), a subform is the standard way to do this. The
subform's record source will need to use two criteria, the
combo box for the user name and the list box for the part
number.

There will be an issue of allowing a new record to be added
to the comments table. You want to allow it when there is
no existing comment, but you don't want to allow it if a
comment already exists. I'm not sure what you want to do if
one user has a comment for a part and another user tries to
create a comment for the same part. If that's not going to
happen, then you could just create a unique two field index
on the user and part fields. Otherwise, you have to use
some code with the logic to determine if the subform's
AllowAdditions property should be set to True or False.