Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    In Access 2000, I need to take a table with 3 fields Data_ID, Dealer, User
    and append another table with the same fields in a pipe "|" separated format.( this will be used in another application.)

    Example:
    Data_ID Dealer User
    1234 ABC Dealer smitha
    3423 DEF Dealer smitha
    5545 GHI Dealer smitha
    3323 JKL Dealer jonesd
    3856 MNO Dealer jonesd
    ....

    Output:
    Data_ID Dealer User
    1234|3423|5545 ABC Dealer|DEF Dealer||GHI Dealer smitha
    3323|3856 JKL Dealer|MNO Dealer jonesd

    The following function works until the last record. It errors with no current record and the last record isn't written.
    I don't know how to get it to stop at the end and write the record.


    Function CreateDealerList()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim strUsername As String
    Dim StrSql As String

    Set db = CurrentDb
    StrSql = "SELECT tblDealers.DATA_ID, tblDealers.User, tblDealers.Dealer " & _
    "FROM tblDealers WHERE (((tblDealers.Status) = 'Active'))ORDER BY tblDealers.User, tblDealers.Dealer;"

    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete tblDealerList.* From tblDealerList;"
    DoCmd.SetWarnings True
    Set rs = db.OpenRecordset(StrSql)
    Set rst = db.OpenRecordset("tblDealerList")

    Do While Not rs.EOF
    strUsername = rs!User
    rst.AddNew
    rst!User = rs!User
    Do Until strUsername <> rs!User
    rst!Dealer = rst!Dealer & rs!Dealer & "|"
    rst!Data_ID = rst!Data_ID & rs!Data_ID & "|"
    rs.MoveNext
    Loop
    rst.Update
    rs.MoveNext
    Loop
    End Function

    Thanks for any help,
    Scott

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by ready4data View Post
    Hi,
    In Access 2000, I need to take a table with 3 fields Data_ID, Dealer, User
    and append another table with the same fields in a pipe "|" separated format.( this will be used in another application.)

    Example:
    Data_ID Dealer User
    1234 ABC Dealer smitha
    3423 DEF Dealer smitha
    5545 GHI Dealer smitha
    3323 JKL Dealer jonesd
    3856 MNO Dealer jonesd
    ....

    Output:
    Data_ID Dealer User
    1234|3423|5545 ABC Dealer|DEF Dealer||GHI Dealer smitha
    3323|3856 JKL Dealer|MNO Dealer jonesd

    The following function works until the last record. It errors with no current record and the last record isn't written.
    I don't know how to get it to stop at the end and write the record.


    Function CreateDealerList()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim strUsername As String
    Dim StrSql As String

    Set db = CurrentDb
    StrSql = "SELECT tblDealers.DATA_ID, tblDealers.User, tblDealers.Dealer " & _
    "FROM tblDealers WHERE (((tblDealers.Status) = 'Active'))ORDER BY tblDealers.User, tblDealers.Dealer;"

    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete tblDealerList.* From tblDealerList;"
    DoCmd.SetWarnings True
    Set rs = db.OpenRecordset(StrSql)
    Set rst = db.OpenRecordset("tblDealerList")

    Do While Not rs.EOF
    strUsername = rs!User
    rst.AddNew
    rst!User = rs!User
    Do Until strUsername <> rs!User
    rst!Dealer = rst!Dealer & rs!Dealer & "|"
    rst!Data_ID = rst!Data_ID & rs!Data_ID & "|"
    rs.MoveNext
    Loop
    rst.Update
    rs.MoveNext
    Loop
    rst.Update
    End Function

    Thanks for any help,
    Scott
    Add the second update method call, shown above in bold blue.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    David,
    Thanks but it never gets to that point when its on the last record. Its still in the inside loop.
    Scott

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Does it get out of the inner loop?
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not when it is on the very last record.
    Scott

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Duh!

    You have the following.

    Do Until strUsername <> rs!User

    This provides no condition to exit the loop when rs reaches end of file. Add a sceonc condition, as follows.

    Do Until strUsername <> rs!User Or rs.EOF
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    david,
    I tried that too. Still same message.

    Scott

  8. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Message? What message?
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this version. It also avoids having a pipe character at the end of the strings.

    Code deleted because it was incorrect - see my next reply

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    Its almost there but something isn't looping correctly.
    Here is the output.


    Here is a sample database with your function if you can take a look.

    Thanks,
    Scott
    Attached Files Attached Files

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Add thes two lines to the code :
    strDealer = ""
    strDataID = ""


    Code:
    ...
      Do While Not rs.EOF
        strUsername = rs!User
        strDealer = ""
        strDataID = "" 
        Do Until strUsername <> rs!User
           strDealer = strDealer & "|" & rs!Dealer
    ...
    Francois

  12. #12
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry,
    Should have looked before I posted. I just had to add 2 lines (in bold)

    Thanks for the solution

    Function CreateDealerList()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim strUsername As String
    Dim strSQL As String
    Dim strDataID As String
    Dim strDealer As String

    Set db = CurrentDb
    strSQL = "SELECT DATA_ID, User, Dealer " & _
    "FROM tblDealers ORDER BY User, Dealer"

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tblDealerList;"
    DoCmd.SetWarnings True
    Set rs = db.OpenRecordset(strSQL)
    Set rst = db.OpenRecordset("tblDealerList")

    Do While Not rs.EOF
    strUsername = rs!User
    strDealer = ""
    strDataID = ""
    Do Until strUsername <> rs!User
    strDealer = strDealer & "|" & rs!Dealer
    strDataID = strDataID & "|" & rs!Data_ID
    rs.MoveNext
    If rs.EOF Then Exit Do
    Loop
    rst.AddNew
    rst!User = strUsername
    rst!Data_ID = Mid(strDataID, 2)
    rst!Dealer = Mid(strDealer, 2)
    rst.Update
    If rs.EOF Then Exit Do
    rs.MoveNext
    Loop

    rst.Close
    rs.Close
    Set rst = Nothing
    Set rs = Nothing
    Set db = Nothing
    End Function

  13. #13
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Francois,
    Scary how almost live this forum is. Or I need to be able to type faster.
    Thanks to all.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, I was too hasty. Here is a modified version.

    Code:
    Function CreateDealerList()
      Dim db As DAO.Database
      Dim rst1 As DAO.Recordset
      Dim rst2 As DAO.Recordset
      Dim rst As DAO.Recordset
      Dim strDataID As String
      Dim strDealer As String
    
      Set db = CurrentDb
    
      db.Execute "DELETE * FROM tblDealerList;"
      Set rst = db.OpenRecordset("tblDealerList")
      Set rst1 = db.OpenRecordset("SELECT User FROM tblDealers GROUP BY User")
    
      Do While Not rst1.EOF
        Set rst2 = db.OpenRecordset("SELECT Data_ID, Dealer, User " & _
          "FROM tblDealers WHERE User='" & rst1!User & "' ORDER BY Dealer")
          
        strDealer = ""
        strDataID = ""
        Do While Not rst2.EOF
          strDealer = strDealer & "|" & rst2!Dealer
          strDataID = strDataID & "|" & rst2!Data_ID
          rst2.MoveNext
        Loop
        
        rst.AddNew
        rst!User = rst1!User
        rst!Data_ID = Mid(strDataID, 2)
        rst!Dealer = Mid(strDealer, 2)
        rst.Update
        rst1.MoveNext
      Loop
    
      rst1.Close
      rst2.Close
      rst.Close
      Set rst1 = Nothing
      Set rst2 = Nothing
      Set rst = Nothing
      Set db = Nothing
    End Function

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is a version that (I think) does the job with just a query, using the concat function.

    [attachment=87038ealerQry.zip]
    Attached Files Attached Files
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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