Results 1 to 7 of 7
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Auction Items Design (Access any version)

    I am working on a database for an auctioneer.

    I have an items tables, where each item has fields for AuctionID, VendorId, BidderId etc

    Generally, items are added to an Auction using a subform, and each item is automatically assigned a lot number.

    However, sometimes items are provided to the auctioneer in advance of an auction are entered into stock and allocated a stockcode. In this case the AuctionID is initially left null, and will be filled in when the item is allocated to an auction.

    The auctioneer wants to allocate stock items to an auction by entering the stockcode into a textbox on the subform that is used for entering new items, and having the system look up the details and fill in the rest of the line. Furthermore, he wants to do this line by line so he controls which lot number is allocated to each item.

    My problem is that once you start putting anything on a new line, a new item is created, and I don't want a new item.

    One solution would be to put all stock items into a separate table, and create a matching entry in the items table when the items is allocated to an auction. This would involve duplication of fields such a item description, etc.

    Another would be to create a new table ItemAuctions, and so create a one-to-many relationship between items and auctions. I don't really want this because it will involve changing lots of things that are based on the current design. Furthermore, nearly every item only goes to one auction, and we are quite happy to lose the history in the odd case that an item is resubmitted to a second auction.

    Another thought I have just had would be to just let the new record get created, and have code operate behind the scenes that deletes the old item record that was not allocated to an auction. I will try that out, but I wonder if anyone has another suggestion.
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auction Items Design (Access any version)

    How about an unbound text box or combo box in which the auctioneer can enter or select a stock number, plus a command button (or keyboard shortcut or toolbar button) that sets the AuctionID for the item with the selected stock number by executing an update SQL statement, or using DAO/ADO code. You will then have to requery the subform to display the selected item.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Auction Items Design (Access any version)

    thanks Hans

    When I put an unbound textbox in the detail section of the subform (in the line where each item is displayed) a new record is still created when I type in it.


    So to use an unbound text box, it would need to be in the header or footer. I will try this and see how it goes.

    I have been trying my idea of letting the new record get created, using DAO to insert all details of the previous record, then running some sql to delete the old record, and this seems to work OK. The only problem I have so far is that I momentarily have two records with the same stockcode, and had set these to be unique.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auction Items Design (Access any version)

    Typing in an unbound text box in the detail section in a blank record does not create a new record in my experience (I just tested to make sure.)

    But in a continuous form, the value of an unbound text box will be repeated in every visible record, since a continuous form actually contains only one set of controls, however many records are visible, so it is not very attractive.

    So I would put the text box in the subform header or footer anyway, or in the main form. Is that really unacceptable to the auctioneer?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auction Items Design (Access any version)

    You could store all the details in variables, delete the newly created record, and only then update the appropriate record.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Auction Items Design (Access any version)

    I have just fixed the problem with the index as well, by changing the order of operations a bit.

    * Open the DAO recordset as a snapshot
    * delete the old record
    * use the info in the recordset to fill in details of the new record.
    Regards
    John



  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Auction Items Design (Access any version)

    The unbound text box (in detail) was creating a new record for me, but I have just tested this in another form and it doesn't there.

    In the auction case, I was triggering the OnCurrent event that allocates the Lot Number, and once a Lot Number was created then so did the record.

    You are ,of course, right about an unbound text box displaying the same value ine ach record - I had forgotten this was going to happen.

    I will get both methods working and see which one the auctioneer prefers.

    Thanks Again
    Regards
    John



Posting Permissions

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