Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Recordset Advice (2000)

    I have a database tracking addresses. Each person may have multiple addresses, one of which is their primary address. Temporary addresses have expiration dates. I'm trying to write a procedure that will cycle through the records and update the primary field. I have the easy part done - setting the primary address for people with only one address. Now I need to cycle through those with more than one address. If there is a temporary address, that should be the primary address unless it is expired, in which case the permanent address should be set as the primary address (with the primary field for the temporary field unchecked). Any suggestions for the next step would be greatly appreciate. Thanks.

    Dim rstAddress As ADODB.Recordset
    Dim cnxn As ADODB.Connection
    Dim strSQL As String
    Dim strVar As String
    Dim strCount As Integer

    Set rstAddress = New ADODB.Recordset
    Set cnxn = CurrentProject.Connection

    strSQL = "SELECT fldAddressID, fldPersonID, fldAddressType, fldAddressExpires, fldPrimaryAddress FROM tblAddressPractice"

    rstAddress.Open strSQL, cnxn, adOpenForwardOnly, adLockOptimistic
    rstAddress.MoveFirst

    Do While Not rstAddress.EOF
    strVar = rstAddress!fldPersonID
    strCount = DCount("[fldAddressID]", "tblAddressPractice", "[fldPersonID] = " & strVar)
    If strCount < 2 Then
    strSQL = "UPDATE tblAddressPractice SET fldPrimaryAddress = 1 WHERE fldPersonID = " & strVar
    DoCmd.RunSQL strSQL
    Else
    ' What goes here? I thought about a series of If Then statements, but it seems like there has to be a better way
    End If

    rstAddress.MoveNext
    Loop

    rstAddress.Close
    cnxn.Close

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Need Recordset Advice (2000)

    Using the approach you've taken, a set of IF statements is probably the easies way to complete the process, but I would probably have looked at doing it all in queries rather than using VBA. That of course presumes it is a one-time problem. If that's not the case, then I would probably do all the processing using either ADO or DAO, and simply step through the recordset comparing the sorted recordset. So what happens if you have two addresses and neither is temporary? We solved a similar problem by giving a person a preferred address, and then tools to administer it. Our back-end was in SQL Server, so we used a trigger to reset the Preferred flag for other addresses when the preferred address changed. Unfortunately you don't have that luxury in Access. If it's an ongoing thing, you would probably want something form based to reset things, but you are likely to need an audit to assure that you don't have two primary addresses.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Recordset Advice (2000)

    Would it work for you to set the expiration date of the permanent address to be a really large number, then just do a query for the address with the lowest expiry date? You could query for addresses where the expiry date > NOW(), which would eliminate all addresses which have expired as of today.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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