Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006

    Unanswered: Automatically detect existing records - how?

    I am using a parameter query where I return all ended ebay listings within a given date range. Once this has occurred, we need to be able to enter the buyer's username (sourcing this field from tblBuyers.BuyerUsername).

    Ideally I'd like to:

    a) Set up a subform for the query, using a Requery button to return those results to the subform.

    b) Have another subform that links qryEndedListings.BuyerUsername to tblBuyers.BuyerUsername.

    c) When I enter a buyer username in the query results, the database should 'detect' whether that username already exists in the database - that is, whether that buyer has already purchased from us or not - and, if so, retrieve that buyer's record and place it in the BuyerInformation subform. If not, a new record for that buyer should be created in the BuyerInformation subform, with their newly entered BuyerUsername already filled out.

    Is this easily done? How can I set this up? Many thanks for your help.

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    We don't know anything about what your subforms, queries and tables are composed of. Could you provide a bit more detail about what's under the hood before we start randomly throwing wrenches at it?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006

    lol, of course that would help!

    Hi Teddy,

    Sorry, I guess I was getting a little carried away with my own thoughts there...hopefully the following is more clear.

    The relevant tables, subforms and queries are:

    [1] tblItems - ItemID (P), ItemTitle
    [2] tblListings - EbayItemNo (P), ItemID (F), ListingStartDate, ListingEndDate
    [3] tblSales - EbayItemNo (P), BuyerUsername (F), SaleDate, FinalSalePriceAU, FinalSalePriceUS
    [4] tblBuyers - BuyerUsername (P), BuyerName (etc)

    [5] qryEndedListings - ItemTitle, ItemID, EbayItemNo, ListingEndDate, SaleDate, FinalSalePriceAU, FinalSalePriceUS, BuyerUsername

    [6] sfrmEndedListings - containing the same fields as in qryEndedListings

    [7] sfrmBuyerInformation - containing BuyerUsername, along with fields like BuyerName, BuyerAddress, etc

    So, I want to set up a parameter query (date range search) within qryEndedListings.ListingEndDate - so that all listings due to end between the given dates will be displayed in sfrmEndedListings (using the Requery function).

    Consequently, I would enter information like SaleDate, FinalSalePriceAU, FinalSalePriceUS, and BuyerUsername.

    I want to be able to enter the buyer's username (e.g "sweetfreak") into the sfrmEndedListings.BuyerUsername field. I would then like one of the following to happen:

    a) If "sweetfreak" already exists in the database (because she's bought something previously), then her information would automatically be shown in sfrmBuyerInformation (after entering her username into sfrmEndedListings.BuyerUsername).

    b) If "sweetfreak" doesn't already exist in the database, then her username would be 'copied' over to sfrmBuyerInformation, ready for a new buyer record to be created.

    Hope this is more helpful for you!

  4. #4
    Join Date
    May 2006
    Not too bring back an old thread but I'm looking for basically exactly what you are developing here...could we talk?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts