Data entry auto-populating controls

[DancesOnTables]

Hi everyone!

I am having a surprisingly hard time with a particular form I have been asked to change for a db we use here to list worksheets.

There are several tables involved: Orders which contains a link to the customer table and to the jobsheet detail table. The jobsheet details table contains all of the line items that appear on the jobsheet (linked by ordernumber) for the order such as product (links to the products table) quantity price and total (quantity times price) and date.

I want the form to allow for free format data entry but also want to allow for the user to be able to select from the products table using a combo box which will provide a product selection based upon a category selection (categories table is linked to the products table) and then for that selection to provide for autofilling other controls.

I have the main form listing the order header details with name address and job number and in the details section of the form I have a subform based on a query containing the fields I want to list from the jobsheet details, product and categories tables.

two problems:

The existing entries in the jobsheet for the order are not being shown on the subform.

I can get the categories combo box to drop down and provide a selection of categories but I can't get the products displaying and filling correctly based on the selection made in the categories combo box. I don't want to restrict the selection to only those in the list but I do want to autofill other controls on the line such as Unit of sale, price (which can be overriden) based on any selection that is made.

Does anyone have any example of these kind of features that I can view or any tips on autofilling?

I'd be very grateful as I am baffled about the approach I should be taking on this.