Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check if record exists (MSACCESS 2003)

    I am trying to find the cleanest method to check if a record exisits first before adding it? Any advice appreciated. Thanks

    SQL = "Select * From tblDirectory" ' This code puts parent directory name (Sport) into tblDirectory
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)

    ' Dump main directory data into table
    rst.AddNew
    rst![MainDirName] = sfl.Name
    DRNAME = sfl.Name
    PK = rst("DirID") ' Primary Key generated by added record
    rst.Update

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    SQL = "Select * From tblDirectory" ' This code puts parent directory name (Sport) into tblDirectory
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)

    If rst.BOF And rst.EOF Then' No records in recordset <--- <font color=red> This does the trick for me </font color=red>
    'do whatever
    Else
    ' Dump main directory data into table
    rst.AddNew
    rst![MainDirName] = sfl.Name
    DRNAME = sfl.Name
    PK = rst("DirID") ' Primary Key generated by added record
    rst.Update
    End If
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    Thanks Steve. I might be wrong, but if there are records in the file then it will put the record in, which could be a duplicate. I think I need to have something in the SQL statement to get the record first and if it's not in there then add it.? Regards

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

    Re: Check if record exists (MSACCESS 2003)

    Something like this?

    SQL = "Select * From tblDirectory" ' This code puts parent directory name (Sport) into tblDirectory
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
    ' Try to find record with sfl.Name
    rst.FindFirst "MaindirName = " & Chr(34) & sfl.Name & Chr(34)
    If rst.NoMatch Then
    ' Not found, so dump main directory data into table
    rst.AddNew
    rst![MainDirName] = sfl.Name
    DRNAME = sfl.Name
    PK = rst("DirID") ' Primary Key generated by added record
    rst.Update
    '...
    End If

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    Thanks Hans. I am having problems with this line

    rst.FindFirst "MaindirName

    The MaindirName comes from

    For Each sfl In fld.SubFolders

    sfl.Name (Main directory name)

    I tried putting sfl.Name into a variable and then into the offending line but could not get the correct syntax to accept it. Thanks again

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

    Re: Check if record exists (MSACCESS 2003)

    The line I posted is longer than

    rst.FindFirst "MaindirName

    Did you copy it correctly?

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    Hi Hans, yes I used rst.FindFirst "MaindirName = " & Chr(34) & sfl.Name & Chr(34)
    I just showed the part that errors where I am trying to put in sfl.name. Thanks

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

    Re: Check if record exists (MSACCESS 2003)

    The code is correct, so I don't understand what's going wrong. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    Hans, it's me being stupid. I thought I had to replace MaindirName with the name of the directory I was looking for. I tried it again with your syntax in place and it works!! Can I use MaindirName for a subdirectory pre chack in the same way or is their a reserved word for that. Best regards

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

    Re: Check if record exists (MSACCESS 2003)

    MainDirName is the name of the field that you're adding the data to.

    If you want to perform the same kind of check when adding data to another table, you should use the name of the relevant field in that table.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    Many thanks again Hans, now its clear. Have a good weekend.

  12. #12
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    Come unstuck on this one.

    SQL2 = "Select * From MainDirDocs"
    Set rst2 = db.OpenRecordset(SQL2, dbOpenDynaset)

    For Each fil In fld.Files

    rst2.FindFirst "ReferenceName = " & Chr(34) & sfl.Name & Chr(34)

    If rst2.NoMatch Then
    ' Dump data to table
    rst2.AddNew
    rst2![ReferenceName] = fil.Name
    rst2![DocPath] = FG
    rst2("DirID") = PK
    rst2.Update

    End If

    Next fil

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

    Re: Check if record exists (MSACCESS 2003)

    In this code, the text that you want to add to the ReferenceName field is not sfl.Name but fil.Name, so you should check for the latter:

    rst2.FindFirst "ReferenceName = " & Chr(34) & fil.Name & Chr(34)

  14. #14
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if record exists (MSACCESS 2003)

    Thankyou very much Hans. Don't you ever leave this forum!! Regards

Posting Permissions

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