Autokeys / setvalue macro problem

[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.

[JConsulting]

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?

[ADezii]

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:

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.

[fieldling]

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.

[ADezii]

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.

[fieldling]

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.

[ADezii]

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 fModifyFieldSettings().
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.
   

   Public Function fModifyFieldSettings()
   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

[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.

[ADezii]

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.