Sorry got to go now.
Sorry got to go now.
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.
No worries will pick up tomorrow
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.
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.
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?
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.
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.
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.
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.
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.
Do you want to delete the Bid Entry number or the amount bid or the whole record?
the Bid Amount for that item
OK.
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
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.
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?
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.
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.
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.