Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 43

Thread: Adding record from one table to another where the Employee ID is the same

  1. #21
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry got to go now.

  2. #22
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    ohh ok, so I have the two tables Bid Entry and Winner_Pick the tables are set up to where each column correlates to an item number i.e. the column named 200 is the column designated for item 200 so that if anyone places a bid for item 200 it will show up there and their ID will go to Bidder ID field. The way we have the auction set up is a silent auction where employees get a slip that has their bidder number on it and a list of items for Auction, they will go down the list and write their bid amounts for each item that they want and then give it to an HR rep to enter their bids into the bid table by using the Bid Entry Form all at once, this is how the bid Entry Table gets its information. Once all the bids have been entered and the auction closes we will go to the Winner_Pick Form and select the numbers that are to the left on Winner_Pick Form which pulls up the list box with the current information in it for that column/item and we select as many winners from that list box until the box titled "Amount available" Equals the Box that says "Amount used" then it shouldn't allow any more selections. The selected winners Bid amounts I want to show up in the Winner_Pick Table with their ID on the same row and removes that specific Bid amount from the Bid entry table but not all their bid amounts just the ones we select from the various List Boxes.

    I know this sounds weird and confusing so please feel free to call me and I can do my best to sort it out.

  3. #23
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    No worries will pick up tomorrow

  4. #24
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Looking at the design of your database & Forms I assume that you are an Excel Programmer and transferred that ideology and skill to Access, the problem is you miss out on all the built in design features of Relational databases.
    I will try and get VBA to work with what you have, but it is not straightforward due to using a mixture of Field Values and Field Names.

  5. #25
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    KDC, here is the first phase, which is the VBA to Add a new record or Edit an existing record, it appears to be doing what you want, but I have only added it to Button BT200. What I can't do is Compact & Repair the database as it is a later Access version than I have.
    I have also removed the Ambassador Name from the Winners table as it can be picked up from the Linked Bid Entry Tables in a Query.

    Now let me explain some of the short comings of the design you are currently using.
    1. Your Winners form needs 43 Buttons to cover your current fields in the bid & winners tables plus the code to action them. The amount of code can be reduced by using Subroutines or other Subs.
    2. The list for the Bidders will need to be as long as is necessary to include all employees.
    3. What happens if you need to add a new Auction Item? Will you be available to add a Button & Code?
    I understand that if you are an Excel user you are used to thinking in Columns & Rows to store data, but that is not normally how Access works alone, it creates links to subtables.
    Attached Files Attached Files

  6. #26
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    you are correct I usually work in excel and have done some work with access and want to get better at it so I do really appreciate the feedback.

    I have tried out the code and it does do what I want and its super awesome. I did want to ask if there is a way to have the record removed from the list box once clicked and the record has been copied to Winner_Pick?

  7. #27
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    That should be possible to do, do you want it removed from both lists?
    Will you also be selecting another bidder from the Winner list?

    Did you see the problems with using the Buttons to select the Bid Item?
    Last edited by OBP; 12-13-2017 at 10:54 AM.

  8. #28
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I can see another minor problem with the way that you are doing this, to remove the bid from the list you either need to remove that Record or have a "Bid accepted" field to allow it to be filtered out.
    One of the advantages of Access is being able to analyse the History of the transactions, this is useful for tracking Sales Trends, Price Trends and Bidder performance and Preferences etc.
    The point is you do not have an Auction # to store historical data.

  9. #29
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    I would like the Bid amount Removed from Bid Entry Table when clicked which would take it out of the Listbox due to the Record source for the listbox not allowing any thing Equal to 0.

    I do see the problem with the way the database is and i plan on redoing the database in January to function more like Access and not Excel, for right now this will have to work because the charity Auction is this upcoming Tuesday and my time frame is short.

  10. #30
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I could probably get it ready before that.
    How many products will there be and how many Bidders?
    Because in the time it takes to recreate all the buttons & VBA I could probably redo the database.

  11. #31
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    There could be up to 40 Items and unlimited amount of bidders, we have over 2,000 employees who could enter this Auction. i would love to see how you would design it but would you also be able to send me the code that would delete the bid number from the bid entry table just as a backup.

    the first part of the database i could not send because it is attached to our network and has our employees info in it, but what it does is it finds the employees info by searching for their badge number in the network and then assigns them a bid number starting at 100 and going up by 1 and inputs them into a table.

    the bid entry form is where i connect the bidder Number with the bids that the bidder wrote down on a bid form then we would enter it into the form and it would be inputted it into the bid entry table.

  12. #32
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Do you want to delete the Bid Entry number or the amount bid or the whole record?

  13. #33
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    the Bid Amount for that item

  14. #34
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK.

  15. #35
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Here is the code for setting the amount to 0.

    Dim db As Database, SQL As String, fieldcounter As Integer
    Dim rec As Object, rec1 As Object
    On Error GoTo ErrHandler
    If Me.BX200 = Me.Amount_Available Then
        MsgBox "All units have been claimed for this item"
        Me.Warning.Visible = True
        Exit Sub
    End If
    Me.Control_Source_1 = Me.Winner_200.Column(1)
    Set db = CurrentDb
     SQL = "SELECT Winner_Pick.* " & _
      "FROM Winner_Pick " & _
      "WHERE [Bidder Number] = " & Me.Winner_200.Column(1)
    Set rec = CurrentDb.OpenRecordset(SQL)
    If rec.RecordCount = 0 Then
        With rec
            .AddNew
            .[Bidder Number] = Me.Winner_200.Column(1)
            For fieldcounter = 1 To rec.Fields.Count
                If .Fields(fieldcounter).Name = "200" Then
                    .Fields(fieldcounter).Value = Me.Winner_200.Column(2)
                    Exit For
                End If
            Next fieldcounter
            .Update
            .Bookmark = .LastModified
        End With
    Else
            With rec
            .Edit
            For fieldcounter = 1 To rec.Fields.Count
                If .Fields(fieldcounter).Name = "200" Then
                    .Fields(fieldcounter).Value = Me.Winner_200.Column(2)
                    Exit For
                End If
            Next fieldcounter
            .Update
            .Bookmark = .LastModified
        End With
    
    End If
    Me.List207.Requery
     SQL = "SELECT Bid_Entry.* " & _
      "FROM Bid_Entry " & _
      "WHERE [Bidder Number] = " & Me.Winner_200.Column(1)
    Set rec = CurrentDb.OpenRecordset(SQL)
            With rec
            .Edit
            For fieldcounter = 1 To rec.Fields.Count
                If .Fields(fieldcounter).Name = "200" Then
                    .Fields(fieldcounter).Value = 0
                    Exit For
                End If
            Next fieldcounter
            .Update
            .Bookmark = .LastModified
        End With
    
    rec.Close
    Set rec = Nothing
    Set db = Nothing
    Exit Sub
    
    ErrHandler:
        If Err.Number = 3022 Then
            MsgBox "Ambassador Already Entered"
            Exit Sub
        End If
    MsgBox Err.Description

  16. #36
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    Thank you i will keep it as a backup.

    thank you again for your help your a life saver and for taking the time to give me feedback i am really interested in seeing how this bid tables should have been set up as.

  17. #37
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Can a Bidder in an Auction bid for more than 1 of the same Item, or are they limited to 1 each?
    Are the bidders always given the same Bidder Numbers?

  18. #38
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    This is roughly what a Relational Database would look like.
    Bear in mind that this is a basic version without frills and it has lots of ID fileds that would normally be set to invisible so that users do not see them, I have left them visible so that you can see how the Relationships and Form Master/Child links work to bring the data together.
    This is especially useful in queries.
    There are 2 Append queries that create new records bringing Items and Bidders together.
    That is not the usual way of creating you would normally use Combos to select them as needed, but I wanted to make it similar to your Sheet style.
    Attached Files Attached Files

  19. #39
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You can ignore the previous version, this one has a few more refinements added.
    I can't compact & repair this database due to it having later features than Access 2007, perhaps you could do so.
    Attached Files Attached Files

  20. #40
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    Hey OBP, this looks awesome and way more stream lined. looking at it you have the main form and then sub forms that come up on the main form when a button is clicked.

    Thank you for showing me this I have a lot to learn and I need to not think like rows and columns like I do in excel.

Tags for this Thread

Posting Permissions

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