Autokeys / setvalue macro problem

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • fieldling
    New Member
    • May 2007
    • 30

    #1

    Autokeys / setvalue macro problem

    Afternoon all. Am having some trouble I hope you can help with.

    I have twelve fields in a table (Labelled 1 - 12). I have created an autokey/setvalue macro so that when I press CTRL1, field 1 is populated with a "Y". So far so good.

    What I need to be able to do is press CTRL1 again and field 2 is populated with a "Y" (as long as field 1 has already been done) and on the next press field 3 etc.etc.

    Thought it would be easy but it's giving me a real headache. Anyone got any ideas? Thanks.
  • JConsulting
    Recognized Expert Contributor
    • Apr 2007
    • 603

    #2
    Originally posted by fieldling
    Afternoon all. Am having some trouble I hope you can help with.

    I have twelve fields in a table (Labelled 1 - 12). I have created an autokey/setvalue macro so that when I press CTRL1, field 1 is populated with a "Y". So far so good.

    What I need to be able to do is press CTRL1 again and field 2 is populated with a "Y" (as long as field 1 has already been done) and on the next press field 3 etc.etc.

    Thought it would be easy but it's giving me a real headache. Anyone got any ideas? Thanks.
    Sorry, can you possibly provide us a bit more info? What does the SetValue macro look like...and how are you vieweing your table to update it...through a form or directly in the table?

    Comment

    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834

      #3
      Originally posted by fieldling
      Afternoon all. Am having some trouble I hope you can help with.

      I have twelve fields in a table (Labelled 1 - 12). I have created an autokey/setvalue macro so that when I press CTRL1, field 1 is populated with a "Y". So far so good.

      What I need to be able to do is press CTRL1 again and field 2 is populated with a "Y" (as long as field 1 has already been done) and on the next press field 3 etc.etc.

      Thought it would be easy but it's giving me a real headache. Anyone got any ideas? Thanks.
      You can attempt to set multiple Conditions within your Macro, so that the value "Y" is written to the proper Field based on entries in prior Fields. The logic would be something similar to:
      [CODE=text]Condition Action Applicable Object Value
      Field1="Y" SetValue Field2 "Y"
      Field1="N" SetValue Field1 "Y"
      Field2="Y" SetValue Field3 "Y"
      Field2="N" SetValue Field2 "Y"
      Field3="Y" SetValue Field4 "Y"
      Field3="N" SetValue Field3 "Y"
      Field4="Y" SetValue Field5 "Y"
      Field4="N" SetValue Field4 "Y"
      etc.[/CODE]

      Comment

      • fieldling
        New Member
        • May 2007
        • 30

        #4
        Originally posted by JConsulting
        Sorry, can you possibly provide us a bit more info? What does the SetValue macro look like...and how are you vieweing your table to update it...through a form or directly in the table?
        I'm viewing the table through a form. The SetValue macro looks like this:
        Macro Name Action
        ^1 SetValue

        Item : [Forms]![Main Form]![M1} (M1 being field 1)
        Expression: "Y"

        This works fine. So what I need it to do is: the next time I press CTRL1 it checks whether field 1 on the selected record has a Y in it. If it does then it updates field 2 and so on.
        I tried putting another macro in the next row using the same name (^1) with a condition but it wouldn't allow it.

        I hope this makes sense.

        Comment

        • ADezii
          Recognized Expert Expert
          • Apr 2006
          • 8834

          #5
          Originally posted by fieldling
          I'm viewing the table through a form. The SetValue macro looks like this:
          Macro Name Action
          ^1 SetValue

          Item : [Forms]![Main Form]![M1} (M1 being field 1)
          Expression: "Y"

          This works fine. So what I need it to do is: the next time I press CTRL1 it checks whether field 1 on the selected record has a Y in it. If it does then it updates field 2 and so on.
          I tried putting another macro in the next row using the same name (^1) with a condition but it wouldn't allow it.

          I hope this makes sense.
          It does make sense and would be much easier to implement via code rather than multiple conditions within a Macro. You can also assign a Shortcut Key combination to launch the routine as you had previously requested. If you are interested, let me know and I'll work on iot.

          Comment

          • fieldling
            New Member
            • May 2007
            • 30

            #6
            Originally posted by ADezii
            It does make sense and would be much easier to implement via code rather than multiple conditions within a Macro. You can also assign a Shortcut Key combination to launch the routine as you had previously requested. If you are interested, let me know and I'll work on iot.
            If you could that would be great. I know a small amount about code but this has got me stumped. My other alternative was to assign different keys to each field but that could get too confusing when entering the data.

            Thanks for your help, much appreciated.

            Comment

            • ADezii
              Recognized Expert Expert
              • Apr 2006
              • 8834

              #7
              Originally posted by fieldling
              If you could that would be great. I know a small amount about code but this has got me stumped. My other alternative was to assign different keys to each field but that could get too confusing when entering the data.

              Thanks for your help, much appreciated.
              Maintain your AutoKeys Macro but with 2 small changes:
              1. In the Action Column enter RunCode.
              2. For the Function Name Argument, enter fModifyFieldSet tings().
              3. Copy and Paste the below listed Function to a Standard Code Module. It is only programmed for 5 Fields (Field1 to Field5), but can easily be expanded if you so desire.
              4. Open Main Form and consecutively press the CTRL+1 Key combination.
              5. Let me know how you make out.
                [CODE=vb]Public Function fModifyFieldSet tings()
                Dim fld1, fld2, fld3, fld4, fld5, fld6

                Set fld1 = [Forms]![Main Form]![Field1]
                Set fld2 = [Forms]![Main Form]![Field2]
                Set fld3 = [Forms]![Main Form]![Field3]
                Set fld4 = [Forms]![Main Form]![Field4]
                Set fld5 = [Forms]![Main Form]![Field5]

                If Nz(fld1.Value) <> "Y" Then
                fld1.Value = "Y"
                Exit Function
                End If

                If Nz(fld1.Value) = "Y" And Nz(fld2.Value) <> "Y" Then
                fld2.Value = "Y"
                Exit Function
                End If

                If Nz(fld1.Value) = "Y" And Nz(fld2.Value) = "Y" And _
                Nz(fld3.Value) <> "Y" Then
                fld3.Value = "Y"
                Exit Function
                End If

                If Nz(fld1.Value = "Y") And Nz(fld2.Value) = "Y" And _
                Nz(fld3.Value) = "Y" And Nz(fld4.Value) <> "Y" Then
                fld4.Value = "Y"
                Exit Function
                End If

                If Nz(fld1.Value) = "Y" And Nz(fld2.Value) = "Y" And _
                Nz(fld3.Value) = "Y" And Nz(fld4.Value) = "Y" And Nz(fld5.Value) <> "Y" Then
                fld5.Value = "Y"
                Exit Function
                End If
                End Function[/CODE]

              Comment

              • fieldling
                New Member
                • May 2007
                • 30

                #8
                ADezii, that is genius my friend. Worked first time!

                Thanks for your help with this. If I get stuck again I'll know who to call!

                Cheers.

                Comment

                • ADezii
                  Recognized Expert Expert
                  • Apr 2006
                  • 8834

                  #9
                  Originally posted by fieldling
                  ADezii, that is genius my friend. Worked first time!

                  Thanks for your help with this. If I get stuck again I'll know who to call!

                  Cheers.
                  Always glad to help.

                  Comment

                  Working...