Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: Add New(records) Not Working

    Have created a .ADP file which to talking to MSDE Database
    can manaul add records

    but this does not
    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set Files = Folder.Files
    
       If Files.Count <> 0 Then
       Set rs = db.OpenRecordset("cd") ' <******* stop here 
        For Each File In Files
        FileName = Mid(File, 3, Len(File))
        adstart = InStr(2, FileName, "\")
        ad = Mid(FileName, adstart + 1, 7)
        rs.AddNew
        rs.Fields("ad") = UCase(ad)
        rs.Fields("cdname") = UCase(cdname)
        rs.Fields("Fullpath") = UCase(FileName)
        rs.Update
          Next
    error 91 with block variable not set ......

    what am i Missing
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set Files = Folder.Files
    
           If Files.Count <> 0 Then
               Set rs = db.OpenRecordset("cd") ' <******* stop here 
               For Each File In Files
                   With Files
                       .FileName = Mid(File, 3, Len(File))
                       adstart = InStr(2, .FileName, "\")
                       ad = Mid(.FileName, adstart + 1, 7)
                       rs.AddNew
                       rs!ad  = UCase(ad)
                       rs!cdname  = UCase(cdname)
                       rs!Fullpath  = UCase(.FileName)
                       rs.Update
                   End With
               Next
           End If
    By the way, always indent and outdent when using paired commands (if/else/endif, with/end with, select/end select, for/next, etc.) so you know you "owe" your code a closing line.

    I'm not sure of some of the code in your snippet, but after you use WITH, all properties of the object need to be preceded by a dot "." until you say END WITH. After that, you can still access the object by typing the entire name, such as "Files.FileName". All the WITH statement does is allow you to use the shortcut of ".". I bolded some of my changes for your visual aid.

    Sam

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    THANKS
    do you know why stoping @
    Set rs = db.OpenRecordset("cd")
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Trying using New in your declarations:

    Dim db As New DAO.Database
    Dim rs As New DAO.Recordset
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Thanks but no

    Get this error
    Invalid use of new keyword



    think I missing something obobius
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by myle
    THANKS
    do you know why stoping @
    Set rs = db.OpenRecordset("cd")
    Not really sure, but I noticed an Access quirk that it doesn't like the combo of DAO Database and DAO Recordset when setting variables. Try the following:

    Remove the Set db line of code, or comment it out. Then make the next line
    Code:
    Set rs=CurrentDB.OpenRecordset("cd")
    See what that does.

    By the way, is your table name really "cd"?

    Sam

Posting Permissions

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