Avatar of angie_angie
angie_angie asked on

Error occurs when trying to add multiple records to one recordset

I got error message saying that duplicate entries are not allowed when I tried to add 3 records to one recordset. But it works fine with just one record.

Here are the codes:

    lookFor = "aa"
    value = DLookup("[F2]", "Table", "[F1] = '" & lookFor & "'")
    rdAdd.Open "AssetInfo", CurrentProject.Connection, addOpenKeyset, adLockOptimistic
      With rdAdd
      .AddNew
      rdAdd("AA") = value
      .Update
      End With
     
    lookFor = "bb"
    value = DLookup("[F2]", "Table", "[F1] = '" & lookFor & "'")
      With rdAdd
      .AddNew
      rdAdd("BB") = value
      .Update
      End With
     
    lookFor = "cc"
    value = DLookup("[F2]", "Table", "[F1] = '" & lookFor & "'")
      With rdAdd
      .AddNew
      rdAdd("CC") = value
      .Update
      .Close
      End With
Microsoft AccessVisual Basic.NET

Avatar of undefined
Last Comment
angie_angie

8/22/2022 - Mon
peter57r

What is the primary key of AssetInfo? If it is not an autonumber field then you need to give it a value as you create each record.

CarlVerret

Can you describe the table structure and what is the primary key ?
Rey Obrero (Capricorn1)


try

'change Value to sValue (value is a reserved word in Jet and Access)
'use Nz() function to handle null values

Dim rdAdd As New ADODB.Recordset
Dim lookFor, sValue
   lookFor = "aa"
    sValue = Nz(DLookup("[F2]", "Table", "[F1] = '" & lookFor & "'"), "")

'it is not { addOpenKeyset } but  { adOpenKeyset }

'    rdAdd.Open "AssetInfo", CurrentProject.Connection, addOpenKeyset, adLockOptimistic
     
    rdAdd.Open "assetInfo", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
      With rdAdd
      .AddNew
      rdAdd("AA") = sValue
      .Update
      End With
     
    lookFor = "bb"
    sValue = nz(DLookup("[F2]", "Table", "[F1] = '" & lookFor & "'"),"")
      With rdAdd
      .AddNew
      rdAdd("BB") = sValue
      .Update
      End With
     
    lookFor = "cc"
    sValue = nz(DLookup("[F2]", "Table", "[F1] = '" & lookFor & "'"),"")
      With rdAdd
      .AddNew
      rdAdd("CC") = sValue
      .Update
      .Close
      End With
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rey Obrero (Capricorn1)

btw, the way you wrote your codes will add the records like this ,
assuming you have an ID autonumber

ID    AA    BB    CC
1      x
2               y
3                        z


is this what you want?


ASKER
angie_angie

Sorry I think I didn't make it clear.

Actually it is not multiple records. It is one record. So essentially what I want is

ID AA BB CC
1   x    y    z

I tried the following codes, but I got the error saying that cannot change record because a related record is required in another table. Is it because I set the relationship between the two table and if AA is updated in one table, it must be updated in another table as well?

    Dim lookForArray As Variant
    Dim fieldNameArray As Variant
    Dim i As Integer
    lookForArray = Array("x", "y", "z")
    fieldNameArray = Array("AA", "BB", "CC")

     With rdAdd
      .Open "tbl", CurrentProject.Connection, addOpenKeyset, adLockOptimistic
      .AddNew
      For i = LBound(lookForArray) To UBound(lookForArray)
        rdAdd(fieldNameArray(i)) = lookForArray(i)
        .Update
        .Close
      Next i
     End With
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
angie_angie

To capricorn1,

Still, it is not working. Same as before... ...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
angie_angie

I got it. It's because I put enforce referential integrity when I built up the relationships.

Thank you very much though!