Results 1 to 2 of 2
  1. #1
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41

    Recordsource for data entry form

    I have a simple vendor-product database with three tables.



    tbVendors has fields VendorID (autonumber primary key) and VendorName.

    tbProducts has fields ProductID (autonumber primary key) and ProductName.

    tbVenProdLink ties the first two tables together to indicate which vendor provides which products. The table has these fields:

    1. VenProdLinkID -- autonumber primary key
    2. VenID -- VendorID from table tbVendors
    3. ProdID -- ProductID from table tbProducts
    4. VenProdDateBegin -- date field indicating when the vendor began providing the product
    5. VenProdDateEnd -- date field indicating when the vendor stopped providing the product


    I need advice regarding data entry. My thinking is something like this:

    1. The user would first be presented with a continuous form displaying a list of vendors
    2. User selects a vendor and clicks on a command button
    3. User is presented with a continuous form displaying all of the products with these columns: ProductName, VenProdDateBegin. If the selected vendor already provides some products, the VenProdDateBegin (from tbVenProdLink) will be entered into the VenProdDateBegin field for that product (so the user can see what the selected vendor is already providing). For products which the user wants to assign to the selected vendor, the user will enter today’s date into the VenProdDateBegin field.
    4. After entering today’s date into the VenProdDateBegin date field for the appropriate products, the user clicks on a command button (let’s say in the form header) to write the updates to table tbVenProdLink and close the form.


    I need some help in particular with Item 8. What is the recordsource for the form such that “If the selected vendor already provides some products, the VenProdDateBegin (from tbVenProdLink) will be entered into the VenProdDateBegin field for that product (so the user can see the products the selected vendor is already providing)”?

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The user would first be presented with a continuous form displaying a list of vendors
    I'd be more inclined to use a combobox or listbox

    User is presented with a continuous form displaying all of the products with these columns: ProductName, VenProdDateBegin. If the selected vendor already provides some products, the VenProdDateBegin (from tbVenProdLink) will be entered into the VenProdDateBegin field for that product (so the user can see what the selected vendor is already providing)
    I'd be inclined to use a listbox with 2 columns. the second column showing that its already provided.

    For products which the user wants to assign to the selected vendor, the user will enter today’s date into the VenProdDateBegin field.
    I'd use a multiselect listbox. After making selections then click an Add button to add a date to the selected products with a null begin date( the null begin date criteria is in case they select a product already being provided). Then requery the combos/lists or close the form

    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 2
    Last Post: 12-11-2015, 02:55 PM
  2. Replies: 3
    Last Post: 02-25-2015, 04:48 PM
  3. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  4. Replies: 7
    Last Post: 02-08-2014, 12:31 PM
  5. Replies: 5
    Last Post: 08-12-2013, 12:53 AM

Posting Permissions

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