Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If...then statements to change field content (Access2k)

    Opening a query based on two linked tables. Want to change content of 3 different fields in second table (fieldweb1-3) to value in (field7), based on content of one field (field6)in first table. I do not have the IF syntax correct in example below. Code cycles through each record but does not change fieldweb content

    Dim rs As DAO.Recordset, stDocName As String
    stDocName = "web-query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Set rs = CurrentDb.OpenRecordset("web-query")
    DoCmd.GoToRecord , , acFirst
    Do While Not rs.EOF
    DoCmd.GoToControl "field6"
    If field6 = "ssfty018" Then fieldweb1 = field7
    ElseIf field6 = "ssfty033" Then fieldweb2 = field7
    ElseIf field6 = "ssfty052" Then fieldweb3 = field7
    DoCmd.GoToRecord , , acNext
    Loop
    rs.Close
    Set rs = Nothing

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If...then statements to change field content (Access2k)

    If you're trying to change values in fields in the recordset, then you have to refer to fields in the recordset. You're mixing form navigation commands with recordset stuff, and it doesn't work.

    If you want to base a recordset on a query, there are a couple of ways to do it. but DoCmd.OpenQuery is used to execute an action query, not to open a recordset.

    The simplest way to open the recordset is like this:

    <pre>Dim rst As DAO.Recordset
    Dim dbs As DAO.Database

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("web-query",dbOpenDynaset)</pre>


    DoCmd.GoToRecord is used for navigating within forms. What you need is rst.MoveFirst, which tells Access you want to move to the first record in the recordset. Likewise, DoCmd.GoToControl is for moving to a control on a form, not for addressing a field in a recordset, which is actually much simpler.

    <pre>Do While Not rst.EOF
    rst.Edit
    If rst!field6 = "ssfty018" Then
    rst!fieldweb1 = rst!field7
    .... etc.

    End If
    rst.Update
    rst.MoveNext
    Loop</pre>

    Charlotte

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: If...then statements to change field content (Access2k)

    Hi,
    In addition to Charlotte's points, you might want to use a Select Case statement rather than If..ElseIf..ElseIf, since you're referring to the same field each time.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If...then statements to change field content (Access2k)

    This code was successful. Though I haven't gone back yet to try it, it appears that the response from jhermiz would
    have worked as well with the addition of the rs EDIT/UPDATE lines. Thanks for the help.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If...then statements to change field content (Access2k)

    Yes, it would have. My intention was to show you where you had gone wrong rather than to write code for you. The old principle of "teach a man to fish ...."
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If...then statements to change field content (Access2k)

    >>"teach a man to fish ...."

    And he will spend all day in a boat drinking beer <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If...then statements to change field content (Access2k)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> You may be right ... but at least he'll know what to do when he gets a bite! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

Posting Permissions

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