Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO add new record (2k / 2003)

    I'm using the code below, to add a record into a table. However i'm getting an error on the .update. because its saying its causing a conflict, with the primary key etc. It basically copies the current record, and makes more records the same.

    There are two tables. the main table primary record number is copied into the data table. hence the T1 = Forms!tblsamples2.samID

    Does the .addnew move the record on one more record, (And hence move the data table (tbltestr), on one more record, i do i have to do something else. ?

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Set cnn = CurrentProject.Connection

    Dim T1 As Long
    Dim T2 As String
    Dim T3 As String
    Dim T4 As String
    Dim T5 As String
    Dim T6 As Variant
    Dim T7 As Date
    Dim T8 As String
    Dim T9 As Date
    Dim MyLoop As Integer
    Dim dbs As DAO.Database

    T1 = Forms!tblsamples2.samID
    T2 = Me!testfor
    T3 = Me!tmethod
    T4 = Me!tincutime
    T5 = Me!mbatchno
    T6 = Me!MediaActualNumber
    T7 = Me!Platedon
    T8 = Me!PlatedBy
    T9 = Me!mexpdate


    rst.Open "tbltestr", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew

    'rst!tsamid = T1
    rst!testfor = T2
    'rst!tmethod = T3
    'rst!tincutime = T4
    rst!mbatchno = T5
    'rst!MediaActualNumber = T6
    rst!Platedon = T7
    'rst!PlatedBy = TheUser
    'rst!mexpdate = T9

    rst.Update
    rst.Close

    Set rst = Nothing
    Set cnn = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO add new record (2k / 2003)

    I don't really understand what you're saying in "Does the .addnew move the record on one more record, (And hence move the data table (tbltestr), on one more record, i do i have to do something else. ?"

    What is hte primary key of tbltestr?

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO add new record (2k / 2003)

    tblsamplesr is the main table, with primary key 'samID' (number)

    tbltestr is the data table that i need to create the new record in, its primary key is 'tresultid'
    the second field of tresultid is called 'tsamID' and is where the SamID (number) needs to be copied to.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO add new record (2k / 2003)

    Is tresultid the name of a field or the name of the primary key?
    If it is the name of a field:
    1) What is the data type of this field?
    2) What do you mean by "the second field of tresultid"? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    If it is the name of the primary key:
    Please list the name and data type of each field in the primary key.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO add new record (2k / 2003)

    sorry hans, i mean, its the second field of the table.

    In the main table, on the left of the picture, needs copying into the second field of the data table on the right.

    So the code copies some of the data from the previous record (stored in the T variables), and i will have it loop 9 times to make 9 more records the same. (apart from the tresultID primary field of the data table, which needs to increase)
    The bit i didn't understand, is does the .addnew command, increase the autonumber field in the data table.
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO add new record (2k / 2003)

    The moment you use AddNew, the AutoNumber field is automatically assigned a new value.

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO add new record (2k / 2003)

    This is the full text of the error i get with this code.
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO add new record (2k / 2003)

    Can you add records to the subtable directly?

    There were some problems with AutoNumber fields in early versions of Jet 4.0. Make sure that you have a recent version - see How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO add new record (2k / 2003)

    Hans, something bizaare happens.. when i try to add a record manually...

    i put the data in, the last record is #35559 but the new record, has the number #10300 !

    which of course is already used in the primary field wya back.

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO add new record (2k / 2003)

    It looks like i don't have a jet40.dll....

    although i do have a 'jet500.dll' 'JET ENGINE DLL' with a version 5.1.2600.0

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO add new record (2k / 2003)

    The AutoNumber seed in your table has somehow been set to an incorrect number. As I mentioned, this has been a problem in some versions of Jet 4.0. You'll find repair code and instruction on how to use it in Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates by Allen Browne.

  12. #12
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO add new record (2k / 2003)

    cheers hans.. will sort it........

    It looks like the primary key of the main table is at that number... the primary key 'number' has been transferred to the data table.


    Whats bizarre, is that the database itself is working fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •