Change Datasource in Subform field

[goodguy]

I have one main table which holds details of all inventory ever purchased:
tblStocks
SerialNumber - PK - (Required) - Serial number of the product
ProductType - FK - (Required) - Type of product, from Products table
SalesmanID - FK - (Optional) - Name of the salesman, from Salesmen table
LocationID - FK - (Optional) - Location where this product is issued to the salesman
DateIssued - (Optional) - Date of issue
DateSold - (Optional) -Date of sale
OtherDetails - (Optional) - Any other details

The optional fields are updated only when stock is issued to our travelling salespeople and the last two when there is a sale. Since all data hinges on the serial number, I decided against splitting the optional fields to a second table on a one-to-one relationship and kept all fields together in one big table.

I have created queries to retrieve records for issued Stock, Unissued Stock and Sold Stock, and I am using a subform of the Items table to display a salesman's portfolio, likewise for a location.

When a user is adding a record on the subform, the effect is basically to edit its record in the Items table, as only existing Items can be allocated to salesmen.

I want the user to see matching serial numbers as he starts typing in the serial number field, and I want his choices to be restricted to Unissued items only.

The subform is based on the full Items table although only matching records are displayed due to the link with the parent Salesmen table. How can I change the rowsource for the serial number field to read from Unissued Stock and how do I display matching records based on user input?

All help will be appreciated.

[June7]

RowSource can use whatever table you want as source, can even be joined tables/queries.

For one method of filtering records by user input, review http://datapigtechnologies.com/flash...tomfilter.html

Another method is VBA code to set Filter and FilterOn properties of form.

However, subform can still only display records associated with the main form record.

[goodguy]

Thanks, that helped a lot. It is funny how I forget all the small details every time I take a long break away from it.