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

    VBA Code question (2000)

    I have the following VBA code:
    If IsNull(rstOut!Status) Then
    If rstOut!SFePlatform_Indicator = "Yes" Then
    rstOut!Status = "Enrolled"
    Else
    rstOut!Status = "Undefined"
    End If
    End If

    I want to add the following code after the above code. Can I do this or do I have to put it in the above code?

    If IsNull(rstOut!eplatformDt) Then
    Out!eplatformDt = rstIn!LastOfRenewal_Date
    End If

    Thanks for your help.

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code question (2000)

    Linda

    It depends what you want your code to do.
    You can nest any number of "If Then" statements or create "Select Case" statements if dealing with the same issue.

    If you can be a little more descriptive, we may find an answer.

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

    Re: VBA Code question (2000)

    This is the whole code:
    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
    'Old Code - use this just change Prospect to whatever (changed to

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code question (2000)

    Linda

    There's no reason why you can't put the code after the two If statements.
    It depends on how you want your conditions met.
    You are probably better testing on a copy of your database and check the results.
    A little tip if you're going to nest them, tab each "If Then" and "End If" so you can see how you're building your code.

    <pre>'If .................... Then
    ' If ............................ Then
    ' Do This
    ' Else
    ' Do This Instead
    ' End If
    'End If</pre>


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

    Re: VBA Code question (2000)

    The code you want to add seems to be independent of the code you already have, so I agree with Dave that you can put the new lines below the existing code (but before rstOut.Update, of course).

    I assume that you mean rstOut!eplatformDt = rstIn!LastOfRenewal_Date

Posting Permissions

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