Using listbox to select and edit records

[simon]

Hi there,

I have 3 tables and at the moment I'm focussing on two of them. One
full of Suppliers, another with their Product Codes and Descriptions.
These are joined by Supplier name.

1) My form has a combo box which lets you select your desired
supplier.
2) This then enables a list box once selected which shows you all
their Product Codes and Descriptions for that supplier.
3) Above these I have two text boxes. I would like it that once a line
in the list box is selected, the relevant Code and Description for
that record is displayed in the text boxes for editing purposes.

How can I achieve this?

I have been using pieces of code similar to:

LSQL2 = "SELECT SupplierProductDescription.SupplierProductCode,
SupplierProductDescription.ProductDescription FROM
SupplierProductDescription WHERE
SupplierProductDescription.SupplierName = '" & Combo87 & "'"
Debug.Print LSQL2
List107.RowSource = LSQL2

in order to populate things like the listbox (as with the above) and
similar pieces of code for the combo box which has the supplier
selection in.

Also, another thing I would like to know is how I could directly edit
the record by typing into the listbox itself.

My initial idea was to just have a listbox which you could directly
edit and add lines to for new records.

I couldn't figure it out, hence having to add in text boxes for adding/
editing records. If you can provide a solution for this, it would
bypass alot of this messing around. Plus it would clear up the form of
textboxes etc.

Thanks

Si

 

[simon]

Self created solution:

Use a subform!

Initial testing looks ok - I can add in records and it updates as
expected, just need to add some control measures. ie. no updating
without prompting, ability to delete records, move between and save
records with command buttons.

I then need this subform to be able to act as the basis for a FURTHER
subform (prices over time for selected product), but I'll post the
troubles I'll have with that once I encounter them.

The only problem I have with my solution is the big ugly grey stripe
at the right of my subform where the scroll bar appears.