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

[KDC900]

Hello,

I am stuck on a problem that I have research but have not been able to fix I have a code that adds the info from a list box to a table where the Bidder Id is the index key I have 4 of these List boxes, but if I go into the second text box or any of them and try adding different data but the same bidder ID I get the error message "Index Key cant have duplicate values" so I read online to try an append query that appends the one column that I want to add to the row that matches the bidder ID but I get the error message that it will create a duplicate index key even though it doesn't append the index key so my next hope is that I can add on to the VBA code that I already have to be able to find the matching bidder number in the table with the bidder number of the list box and add the data to the column named "200". here is my code below:

Private Sub Winner_200_Click()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * From Winner_Pick")
rec.AddNew
rec("200") = Me.Winner_200.Column(2)
rec("Ambassador Name") = Me.Winner_200.Column(0)
rec("Bidder Number") = Me.Winner_200.Column(1)
On Error GoTo ErrHandler
ErrHandler:
If Err.Number = 3022 Then
DoCmd.OpenQuery ("SL200")
Me.Winner_200.Requery
Exit Sub
End If
rec.Update
DoCmd.OpenQuery ("Update200")
Me.Winner_200.Requery
Set rec = Nothing
Set db = Nothing
End Sub

SL200 is the append query that runs when the error message comes up saying "Duplicate index Key" but the append query doesn't work.

Winner_200 is the list box.

update200 is used for changing the value of table where the list box gets its info from so that it drops off the list box once that data has been entered into the new table

Winner_Pick is the table where I have the info going to once I click the row in one of the list boxes

any help would be greatly appreciated this is for a charity Auction that my job is hosting.

[OBP]

Sorry for the delay in responding I have been busy elsewhere.
It sounds like you have the ID as a Key Field in the second table, it has to be Number Type Long.

[KDC900]

Hello OBP,

Thank you for the response I have added to the code a little more and have had some success but what I am missing now is the part of the code that tells it where to place the data.

Private Sub Winner_200_Click()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * From Winner_Pick")
rec.AddNew
rec("200") = Me.Winner_200.Column(2)
rec("Ambassador Name") = Me.Winner_200.Column(0)
rec("Bidder Number") = Me.Winner_200.Column(1)
On Error GoTo ErrHandler
ErrHandler:
If Err.Number = 3022 Then
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * From Winner_Pick")
where tblWinnner_pick.Bidder_Number = [Forms]![Winner_Form]![Control_Source_1]
rec.Edit
rec("200") = Me.Winner_200.Column(2)
rec.Update
DoCmd.OpenQuery ("Update200")
Me.Winner_200.Requery
Set rec = Nothing
Set db = Nothing
Exit Sub
End If
rec.Update
DoCmd.OpenQuery ("Update200")
Me.Winner_200.Requery
Set rec = Nothing
Set db = Nothing
End Sub


I have it set up where if the employees ID number is not in winner_Pick table then it adds their ID, Name, Bid Amount. but if the employees ID Number is in the table then it will just add Bid Amount to where the Employees ID is and the column for the item. it works in the sence that it adds the data to the right column but not where the ID Number matches the control source.

I believe my Where statement is wrong how would I get the VBA code to work with the Where Clause?

[OBP]

First of all you should have 2 Recordsets Rec and Rec1, Rec is for the Table for extracting the data to be added and Rec1 is for the Table receiving the data.
You should have a Loop to iterate through table one one record at a time to select the records that meet your criteria.
Within the loop you should have the VBA code to either Edit the Record or Add the record.

Can you upload a copy of the database with some dummy data in (No personal data allowed) in Access 200-2007 format?
I could thencreate the VBA code and test it.
Other than that I can provide similar VBA which might put you on the right track.

[KDC900]

I can send a copy of the file but I cant upload it to this site because it says the file is to large. how would you like me to get it to you?

[OBP]

To upload it Compact & Repair it and then Zip it to see if it then meets the size limit.
If not you can send it me at
osborn . ac @ gmail.com
with the spaces removed

[KDC900]

okay I have attached the file to this reply.

I have set the AutoEcex macro to open to the form I am working on, to the left of the form are some button that bring up the List box with the data in it. Each list box has the event "on click" on it and the list boxes get their info from Bid Entry Table.

thank you for your help it is greatly appreciated and if I am missing any info please let me know.

KDC

[OBP]

OK, I have the database, it is 8:00pm here in the UK, so I will take a look at it tomorrow and get back to you then.

[KDC900]

Ok

[OBP]

To do what you are trying to do, ie Create a Record or Edit a Record can be done without Recordsets, by simply using a Subform with Master Child Links set.
So I could either do it that way or with 2 Recordsets, or both if you just want to learn how.
I notice that you do not have any Relationships set between the tables.
Main Tables with the correct relationships set up will provide the Master/Child links.
Unfortunately it is my Poker Night tonight so I only have another 2 hours.

[KDC900]

If its not to much trouble I would love to see both so that I can learn more about access but if not I would say the recordset plan based on the fact I have no relationships in the database set up.

[OBP]

I will do both, the relationships and use of Main & Sub forms is actually more practically useful than the more advanced VBA.
Once you have the basics of Relational Database Design the power of the database can then be greatly enhanced by the use of Queries, SQL and VBA.
So you should really learn the basics first.
What I will do is provide both, but I doubt if I will get them both done before 7:0pm here.
So I will try to get the Main/Subform setup first and post it tonight and then move on to the Recordset version for posting tomorrow.

[KDC900]

ok, sounds good and thank you for helping me.

[OBP]

I notice that you still have the Both Tables with Bidder Number where they are Key Fields.
The Key Fields are both set to "No Duplicates", which will still give you the same problem that you couldn't enter more than one record.
So which of the 2 tables is the Master Table, ie which data comes first in terms of data entry the Winner Pick or the Bid Entry?
By the way the Ambassador Name does not need to be in both tables just the Main or Master one.

[KDC900]

Bid Entry table comes first

[OBP]

Ta.

[OBP]

How many picks can a person make in the Winner Pick table in one record?
I am trying to understand how the database works, to see if the design can be optomised.

[OBP]

Are the duplicated numbers in the 2 tables from the Item Dis table?

[KDC900]

For the item Dis table there are no duplicate numbers in the table nor will there be.

[OBP]

Sorry I meant the numbers 205 - 395 in the other 2 tables are they from the Dis table?
Could a single record in the 2 tables hold more than 1 of those numbers.
ie in one record the Ambassador bids on more than one and I now see that they do in the Bid Entry table.
It is a rather unusual setup for storing the data because the relationship between the bids is the Field Name in the other tables
It means that the VBA code has to use the Field name to know where to put the data.
Let me explain how the data would normally be entered, it would normally be brought together using the key fields in a many to many relationship like this
Ambassador Key.
Item Dis Key
Amount

So that would constitute a Record

Would you want this database to control the Stock of Items as well?

[OBP]

Sorry got to go now.

[KDC900]

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.

[KDC900]

No worries will pick up tomorrow :)

[OBP]

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.

[OBP]

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.

[KDC900]

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?

[OBP]

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?

[OBP]

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.

[KDC900]

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.

[OBP]

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.

[KDC900]

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.

[OBP]

Do you want to delete the Bid Entry number or the amount bid or the whole record?

[KDC900]

the Bid Amount for that item

[OBP]

OK.

[OBP]

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

[KDC900]

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.

[OBP]

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?

[OBP]

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.

[OBP]

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.

[KDC900]

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.

[OBP]

The power is in the Master child links, which links the Auction to the items and bidders and the Items to the bidders and winners, which are actually the same thing just presented differently.
There is also the power of the Queries that supply the forms, they can present the data in different orders, so the Winners form on it's own tab just presents them as they happened, but the one on the Item Dis form presents them with the last one at the top.
There is some fairly simple VBA to update the forms by requerying them etc.
It doesn't at the moment check if the Auction Item quantity is greater than the Item Stock Quantity but it can.
It could also control the Item stock level when you set the auction level.
Once the data is in there and you have the Auction you can then run analysis on what sells best and at the most profit, Who is consistently the best bidder etc.
How many more products could have been sold if you had more stock.

[KDC900]

Hey OBP, thank you for taking the time to show me and explain access relationships, subforms and the best way to run access. I am going to work on learning more about relationships between tables because I know I lac in that area.

Thank you again you are a life saver!

[OBP]

KDC, you are welcome, it is my retirement hobby.
A good old Access book would help a lot, it won't have all the new fangled stuff that has been added to Access over the years, but it will have the Basics of Relational Database, Relationships, query, Form and Report Design.
Or you can ask me on here or by email if you need clarification on something.
I have attached a summary that I wrote many many years ago that might provide you with a starting point (or not).