Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Unanswered: Recordset Question

    Hello All,

    I have spent quite some time on this, and I haven't been able to get it working. Any help or suggestions would be great:

    I am trying to apply a location code to a record. The location code should only be used once per county in the table. If multiple records exist in the same county then the next number would be applied to record #2.

    I have that portion working, (not included in code below) but the code below updates every record within the same county with the same code. When it runs and updates the first record in the county, it should stop and not apply a "101" to another record in the same county.

    This is the part I cant seen to get past. Any advice would be appreciated.


    Dim rs1 As DAO.Recordset
    Set rs1 = CurrentDb.OpenRecordset("Select * from tbladimport")


    Do Until rs1.EOF And Not rs1.BOF





    Dim rs2 As DAO.Recordset
    Set rs2 = CurrentDb.OpenRecordset("Select USER_LOCATION_CODE, FACILITY_NAME,FACILITY_COUNTY from funeral_facilities where facility_name = " & "'" & rs1!companyname & "'" & " and facility_address = " & "'" & rs1!street & "'" & " AND FACILITY_COUNTY IN (SELECT FACILITY_COUNTY FROM FUNERAL_FACILITIES)")



    Do Until rs2.EOF

    If IsNull(rs2!user_location_code) = True Then
    Dim rs3 As Recordset
    Set rs3 = CurrentDb.OpenRecordset("Select county.ID as IDC, county.county,funeral_facilities.user_location_cod e from county,funeral_facilities where county.county = " & "'" & rs1!County & "'" & "and county.county = funeral_facilities.facility_county")
    If IsNull(rs3!user_location_code) = True Then
    Dim addten As String
    addten = rs3!idc + 10
    Dim cntyID As String
    cntyID = "F0" & addten & "101"
    Dim runsql1 As String
    runsql1 = "Update funeral_facilities set user_location_code = " & "'" & cntyID & "'" & " where facility_name = " & "'" & rs1!companyname & "'" & " and facility_address = " & "'" & rs1!street & "'" & " AND FACILITY_COUNTY = " & " '" & rs1!County & "'"
    CurrentDb.Execute runsql1
    rs3.Close
    End If
    End If

    rs2.MoveNext

    Loop
    rs1.MoveNext
    Loop



    rs2.Close
    Set rs2 = Nothing
    rs1.Close
    Set rs1 = Nothing
    Exit Sub
    Last edited by cmays637; 12-11-11 at 18:30.

  2. #2
    Join Date
    Oct 2006
    Posts
    110
    I just wanted to come back and post my fix to the thread I started, in case anyone else runs into a similar problem. I've basically started over from scratch and rewrote everything.

    Here is my new code that is working:


    Dim str2sql As String
    Dim rst1 As Recordset
    str2sql = "Select * from tbladimport order by companyname asc"
    Set rst1 = CurrentDb.OpenRecordset(str2sql, dbOpenSnapshot)

    Do While Not rst1.EOF

    Dim strSQL As String
    Dim rst As Recordset
    strSQL = "Select FACILITY_COUNTY,FACILITY_NAME,USER_LOCATION_CODE From funeral_facilities where facility_name = " & "'" & rst1!CompanyName & "'" & " AND USER_LOCATION_CODE IS NULL and facility_county not in (select facility_county from funeral_facilities where user_location_code is not null)"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)


    Do While Not rst.EOF


    If IsNull(rst!User_Location_Code) = True And rst!Facility_Name = rst1!CompanyName Then
    MsgBox rst!Facility_Name
    Dim rst2 As Recordset
    Dim rst2GO As String
    rst2GO = "Select id AS IDC,county from county where county = " & "'" & rst!Facility_County & "'"
    Set rst2 = CurrentDb.OpenRecordset(rst2GO, dbOpenSnapshot)
    Dim x As String
    x = Val(rst2!idc) + 10
    Dim Cid As String
    Cid = "F0" & x & "101"


    Dim STRUPDATE1 As String
    If IsNull(rst!User_Location_Code) = True And rst!Facility_Name = rst1!CompanyName Then
    STRUPDATE1 = "update funeral_facilities set user_location_code = " & "'" & Cid & "'" & " where facility_county = " & "'" & rst1!County & "'" & " and facility_name = " & "'" & rst1!CompanyName & "'" & " AND USER_LOCATION_CODE IS NULL"
    CurrentDb.Execute STRUPDATE1

    End If
    End If
    rst.Requery
    Loop
    rst.Close



    rst1.MoveNext
    Loop



    rst1.Close



    Set rst = Nothing
    Set rst1 = Nothing

Posting Permissions

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