Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update field according to another field (2000)

    This database is a nightmare. I have the following code (that should look familiar) that updates a table called tblSF.
    I have a (supposedly I thought ) manually entered field called "Status" that has a dropdown list with the following options: Declined, Pending, Prospect, Enrolled
    I have another field called "eplatform_Indicator" that is either Yes or No. What I want to do is if "eplatform_indicator" is "Yes" than the field "Status" (which is part of the tblSF) should be "Enrolled". If "ePlatform_indicator" is "No" than "Status" should indicate "Declined" otherwise "Status" should be Null or blank. Where in the code below can I do this? I am forever in your debt. Thank you for your help.

    Private Sub cmdUpdateSF_Click()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset

    On Error GoTo Err_Handler

    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset("Total_qry3", dbOpenDynaset)
    Set rstOut = dbs.OpenRecordset("tblSF", dbOpenDynaset)

    ' Loop throug records of query
    Do While Not rstIn.EOF
    ' NEW CODE STARTS HERE
    ' Try to find corresponding record
    rstOut.FindFirst "SFClient_Number = " & Chr(34) & rstIn!Client_Number & Chr(34)
    If rstOut.NoMatch Then
    ' Not found, try to match on client name
    rstOut.FindFirst "SFClient_Name = " & Chr(34) & rstIn!Client_Name & Chr(34)
    If rstOut.NoMatch Then
    ' Not found either, so add a new record
    rstOut.AddNew
    Else
    ' Found, so edit existing record
    rstOut.Edit
    End If
    ' And set client number
    rstOut!SFClient_Number = rstIn!Client_Number
    Else
    ' Found, so edit existing record
    rstOut.Edit
    End If
    'Old code that didn't include Client Name
    'rstOut.FindFirst "SFClient_Number = " & Chr(34) & _
    'rstIn!Client_Number & Chr(34)
    'If rstOut.NoMatch Then
    ' Not found, so create new record
    'rstOut.AddNew
    ' And set client number
    'rstOut!SFClient_Number = rstIn!Client_Number
    'Else
    ' Found, so edit existing record
    'rstOut.Edit
    'End If
    rstOut!SFClient_Name = rstIn!Client_Name
    rstOut!SFRenewal_Date = rstIn!LastOfRenewal_Date
    rstOut!SFECS_Member_Cnt = rstIn!SumOfECS_MEMBER_CNT
    rstOut!SFGroup_Broker_Name = rstIn!Broker
    rstOut!SFRep_Last = rstIn!LastOfRep_Last
    rstOut!SFCSG_Indicator = rstIn!LastOfCSG_INDICATOR
    rstOut!SFDistrict = rstIn!LastOfDISTRICT
    rstOut!SFePlatform_Indicator = rstIn!LastOfEPlatform_Indicator
    rstOut!SFBEC_Indicator = rstIn!LastOfBEC_Indicator
    rstOut!SFGroup_SIC_Code = rstIn!LastOfGROUP_SIC_CODE
    rstOut!SF_Group_SIC_Name = rstIn!LastOfGroup_SIC_Name
    rstOut!SFASM_Last = rstIn!LastOfAsm_Last
    rstOut!SFAffiliated_Name = rstIn!LastOfAFFILIATED_NAME
    rstOut!SFHDHP = rstIn!LastOfHDHP
    rstOut!SFHRA = rstIn!LastOfHRA
    rstOut!SFHSA = rstIn!LastOfHSA
    rstOut!SFeBill = rstIn!LastOfeBill
    rstOut!SFMember_Self_Serve = rstIn!LastOfMember_Self_Serve
    rstOut!SFEmployer_Portal = rstIn!LastOfEmployer_Portal
    rstOut!SFUniform_Benefit_Health_Plan = rstIn!UHBP
    rstOut!SFIFF_834 = rstIn!LastOfIFF_834
    rstOut.Update
    ' And move on
    rstIn.MoveNext
    Loop

    Exit_Handler:
    'Clean Up
    On Error Resume Next
    rstOut.Close
    Set rstOut = Nothing
    rstIn.Close
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

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

    Re: Update field according to another field (2000)

    > What I want to do is if "eplatform_indicator" is "Yes" than the field "Status" (which is part of the tblSF) should be "Enrolled". If "ePlatform_indicator" is "No" than "Status" should indicate "Declined" otherwise "Status" should be Null or blank.

    If, as you state, eplatform_indicator is a Yes/no field,, Status will become either "Enrolled" or "Declined", the "otherwise" part will never be effected. After the line

    rstOut!SFePlatform_Indicator = rstIn!LastOfEPlatform_Indicator

    you can insert this code:

    If rstOut!SFePlatform_Indicator = True Then
    rstOut!Status = "Enrolled"
    Else
    rstOut!Status = "Declined"
    End If

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field according to another field (2000)

    How would I adjust the code you sent to do the following.

    If rstOut!SFePlatform_Indicator = True Then
    rstOut!Status = "Enrolled"
    Else
    rstOut!Status = "Pending"
    End If

    The next time I run the code, if there is something in the field Status - do not overwrite. Only if the record is added, put something in Status since it will be blank.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field according to another field (2000)

    Is it this what you want ?
    <pre>If IsNull(rstOut!Status) Then
    If rstOut!SFePlatform_Indicator = True Then
    rstOut!Status = "Enrolled"
    Else
    rstOut!Status = "Pending"
    End If
    End If</pre>

    Francois

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field according to another field (2000)

    Yes - where should I put it in reference to the code? Should I put it - after "rstOut!SFIFF_834 = rstIn!LastOfIFF_834" and before "rstOut.Update" in the code?

    Thank you

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field according to another field (2000)

    Yes. This should be ok.
    Francois

Posting Permissions

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