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

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:

Code:

---

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.

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.

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.

Code:

---

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?

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.

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?

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

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

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.

Ok

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.

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.

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.

ok, sounds good and thank you for helping me.

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.

Bid Entry table comes first

Ta.

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.

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

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

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?