Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 43

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

  1. #1
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location

    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:
    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.
    Last edited by Aussiebear; 04-23-2023 at 08:28 PM. Reason: Adjusted the code tags

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  3. #3
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    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?
    Last edited by Aussiebear; 04-23-2023 at 08:29 PM. Reason: Adjusted the code tags

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  5. #5
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    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?

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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

  7. #7
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location

    Attached File

    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
    Attached Files Attached Files

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  9. #9
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    Ok

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.
    Last edited by OBP; 12-12-2017 at 10:30 AM.

  11. #11
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    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.

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  13. #13
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    ok, sounds good and thank you for helping me.

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  15. #15
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    Bid Entry table comes first

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Ta.

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Are the duplicated numbers in the 2 tables from the Item Dis table?

  19. #19
    VBAX Regular
    Joined
    Dec 2017
    Posts
    43
    Location
    For the item Dis table there are no duplicate numbers in the table nor will there be.

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

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
  •