Results 1 to 3 of 3
  1. #1
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    225

    Change Datasource in Subform field

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,880
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    225
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change width of subform in code
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 12-02-2012, 04:57 PM
  2. change design of subform
    By sharad.rt in forum Forms
    Replies: 1
    Last Post: 07-21-2012, 01:15 AM
  3. Replies: 3
    Last Post: 07-20-2012, 11:41 AM
  4. Using an sql string as the datasource for a textbox.
    By rghollenbeck in forum Queries
    Replies: 5
    Last Post: 10-08-2011, 07:09 PM
  5. Color/font change in subform
    By AndyKim in forum Forms
    Replies: 9
    Last Post: 06-24-2009, 04:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums