I need help, newbie status admitted.
I have a access db with two tables, one named "systems", one named "system_type".
"system type" has two fields, (name), (part_num).

In the form for entry into systems table, I have a list box for the systems(system_name) field, that queries the system_type(name) field.
When selected it enters the data in system_type(name) into systems(system_name).
What I need is for the field system(part_num) in systems table to be auto filled with the value in system_type(part_num) that corresponds to the selection made in the list box.

Can anyone help?
Thank you,

Hi
You have a number of methods.
1)
Have a hidden field in your listbox that contains the new field value (part_num).
2)
If using an RDBMS, use a trigger on the main table when updated, this will execute sql in order to populate the other field.
3)
Use the listbox_OnValidate or OnClick methods to execute code to populate the new field from your recordset.
public sub List1_Click
  dim rs as recordset
  set rs=rsMain.Clone
  me.txtField(Part_Num) = rs("Part_Num")
  set rs=nothing
end sub
Of course, this assumes that you have already retrieved the part-num in your main recordset, otherwise
public sub List1_Click
  dim sql as string
  sql="SELECT Part_Num from MyTable WHERE System_Type = " & me.system_type.value
  dim rs as recordset
  set rs=myConnection.ExecuteSQL(sql)
  me.txtMyPartNum=rs("Part_Num")
  set rs=nothing
end sub
Hope this helps
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)