Solved Detect the active field and its value

[zelarra821]

Hello.

I tell you my problem.

I want, when clicking on the form button that you see in the image I attached, to check if the active field is the dropdown also marked in said image and obtain the value.

What is the objective of this?

I want to add a code in that button to update the pricing table, without having to go to another form. In other forms without subforms, I add a Select Case so that it only works with the active field that I want. However, in this case, I think there may be two active fields (the one on the subform and then the one on the main form), and I don't know how to approach it.

I copy the code from another form so you can see what I want to achieve, but applied to a subform.

I also attach a database so you can try it.

Thanks a lot.

Private Sub CmdAñadirPrecio_Click()
    
On Error GoTo err_lbl

Dim FechaInicio As Date
Dim FechaFinal As Date
Dim DtoPactado As String
Dim CosteMaquila As String
Dim PrecioContenedor As String
Dim Precio As String

    Select Case Screen.PreviousControl.Name

        Case "IdTipoDeAceite"

            FechaInicio = InputBox("¿Qué fecha de inicio quieres añadir?", NombreBD, Format(Me.Fecha, "Medium date"))
            FechaFinal = InputBox("¿Qué fecha final quieres añadir?", NombreBD, Format(Me.Fecha, "Medium date"))
            Precio = InputBox("¿Cuál es el precio de este tipo de aceite?", NombreBD)

            If Not IsNumeric(Precio) Then Exit Sub

            CurrentDb.Execute "INSERT INTO TPreciosAceite (FechaInicio, FechaFinal, IdAlmazara, IdTipoDeAceite," _
            & " Precio) VALUES (#" & Format(FechaInicio, "mm/dd/yyyy") & "#, #" & Format(FechaFinal, "mm/dd/yyyy") & "#, " _
            & Me.IdAlmazara & ", " & Screen.PreviousControl & ", " & Replace(Precio, ",", ".") & ")"

        Case Else

            MsgBox "A este campo no se le puede añadir fecha y precio.", vbInformation, NombreBD
            Exit Sub

    End Select

    MsgBox "Precio añadido con éxito.", vbInformation, NombreBD

err_lbl_exit: Exit Sub

err_lbl:

    Select Case Err.Number

        Case 13 'Error al cancelar la fecha

            Exit Sub

    End Select

End Sub

 

[CJ_London]

Perhaps in the subform control after update event put the control name in a hidden text box and use your code to reference that rather than using previouscontrol

 

[zelarra821]

Thanks. And what's the I have to put on main form?

 

[CJ_London]

main form or subform, shouldn't really matter - you could even populate the tag property of the button

code in the control afterupdate event would be

Parent.CmdAñadirPrecio.tag=screen.activecontrol.name

and your button code would be

......
......
Select Case CmdAñadirPrecio.tag
.....
.....


One of the problems with screen.previouscontrol is you are strongly reliant on the user clicking in the right order. If they change a value in one of the controls, then click on another control before clicking on the button - you have the wrong control. Using the afterupdate event of the control means at least it will work on the last control changed, rather than just having (had) the focus.

 

[zelarra821]

you could even populate the tag property of the button

What do you mean with this?

 

[CJ_London]

see my updated post. Every control has a tag property. It is text and can store anything you like

 

[zelarra821]

Solved. Thanks so much.

 

[Pat Hartman]

I would not use the tag property for this purpose. Add a hidden, unbound control. That way there is only one "active" control EVER. Using the tag property won't work if you have code that needs to recognize more than a single control.

Also, don't lose sight of the fact that clicking a button makes the button the active control and there is no way to tell what the previously current control was. Therefore, this whole process is suspect.

A better technique, is to use the double click event of a control to take some action. That way, you never have to worry about trying to save some reference to a different control and hope it is not outdated. The double-click of the control ALWAYS works without any ambiguity.

Do NOT use your current method. It is flawed.

 

[CJ_London]

I would not use the tag property for this purpose. Add a hidden, unbound control.

I also suggested a hidden control but for this particular exercise I see no reason not to use the tag property of the button

please clarify what you consider to be flawed

 

[The_Doc_Man]

I want, when clicking on the form button that you see in the image I attached, to check if the active field is the dropdown also marked in said image and obtain the value.

The others have mentioned this but I will add to it. The exact moment that you click on the button, the "active control" will become the clicked button and its "click" event - which starts AFTER the manual click action - will detect itself. There is no "pre-click" event that triggers before you change focus due to a click. You cannot do this the way you are thinking.

To me, there is a very basic design flaw in the concept.

I want to add a code in that button to update the pricing table, without having to go to another form.

You have just made the button conditionally impure of purpose. You want to do this IF the button was clicked just after the dropdown was selected. (But not if it wasn't the previously selected control?) What do you mean for the button to do if the dropdown had NOT been selected? This is where I see the flaw in the concept. This ambiguity of purpose greatly confuses matters.

 

[Pat Hartman]

@CJ_London The issue is - once you click on the button, the button is the active control and there is no memory of what was the previously active control unless you make one. How you make one is problematic. Do you set the tag property when a control gets the focus? When the click event fires, when the afterUpdate event fires? What triggers the setting of the tag property? That is problem #1. Problem #2 is what unsets the tag property? Do you have to click on the button to unset the tag property AFTER you make use of it? What if you don't "unset" the tag property? How many controls might have their tag properties set at any one time? --- This was the point of the separate hidden control. Whatever you use to register the control as "current" for purposes of the button must at some point be unset. But if you use a separate unbound control which holds the name of the previously "current" control, there can only ever be ONE value since each "trigger" overlays the previous "current" value.

Obviously, the OP had in mind the potential for this code to be triggered by more than one control, but not more than one control at a time.

But, the bottom line is - using the double-click event to run the button code ALWAYS uses the correct "current" control because it is the control with the focus, not some random button, that is running the code.

 

[zelarra821]

Thanks.

 

[CJ_London]

Do you set the tag property when a control gets the focus? When the click event fires, when the afterUpdate event fires? What triggers the setting of the tag property? That is problem #1.

I clearly said and demonstrated the control's after update event to populate the button's tag property. (OP said 'button to update the pricing table,') I take this to mean that a value has been changed - you wouldn't update if there was no change to the value.

Problem #2 is what unsets the tag property? Do you have to click on the button to unset the tag property AFTER you make use of it? What if you don't "unset" the tag property? How many controls might have their tag properties set at any one time?

either clicking another control to replace what is in the button tag property (the default as demonstrated) or in the button click event reset the tag property to zls when all other actions completed. You could perhaps modify the control after update event to not populate the button tag if already populated - comes down to the OP's requirements. OP only requires one.

This was the point of the separate hidden control.

That was the question I was asking. How exactly would the above be different if a hidden control is used? At the end of the day, both are just storing some text and would be populated/depopulated in the same way. Since it is the control name being stored, you can't even use conditional formatting to highlight the currently 'selected' control once it is has lost the focus (by clicking on the button) - unless you use a vba function which can just as easily reference the button tag property as the value of a hidden control.

To be clear - I was answering the OP's question and did not propose alternatives as the purpose of the app is unclear (to me at least - looks like it might be logging changes to values). One could just as easily have said use the control or subform before update event to populate the table, might be the right thing to do, might not.

 

[Pat Hartman]

Chris, I'm not going to argue with out about how to implement this process. You asked for clarification on why I suggested a separate unbound control rather than the tag as you suggested. But, I actually said that BOTH are the wrong solution. The OP is a novice and gave you his "solution". I'm sure you can do exactly what the OP asked for. All I'm saying is that we have no way of knowing what the previous control is when you press a button so that is not a technique I would use. Pressing the button makes THAT control the current control and all knowledge of the previous control is gone. It is really important that the OP understand that concept. Why is a button needed at all if there is only one control that can trigger the other update? And when does the tag get reset?

That was the question I was asking. How exactly would the above be different if a hidden control is used?

It's like the concept of a combo or list box. There can never be more than one control that is "tagged" if you put its name into a separate control where it gets overlaid when a different control becomes "current". If this problem is only ever about a single control then why does the OP think he needs to know what the "previous" control was? Maybe he just needs to understand what events are available for each control. If this is all about always performing the update, then why use a button at all? Why not just always do the other update from the control's AfterUpdate event? That way you don't give the user a chance to make an error by not pressing the button when he should.

But then the question becomes WHY are two tables being updated with the same data? There is a flaw in this process and we should try to figure out what it is. When developers don't know how Access works or what their options are, they gravitate toward solutions like trying to figure out what the previous control is and then using a separate button rather than trying to find the right event to use which is related to the control itself.

Then we get to the final problem of if the update of the second table is done with the AfterUpdate event of a control or the click of a button before a record is saved we run the risk of updating table 2 but not updating table 1 because we can either cancel the update or the update could fail due to some other error. This is all part of the rabbit warren of problems caused by storing the same data in two places.

 

[Pat Hartman]

@zelarra821 You're welcome. While likes are always appreciated, they contribute nothing to the conversation. Did your problem get solved? Do you understand why you should not use this technique? Did you do it anyway? Do you understand that you should not be storing the same data in multiple tables and we should probably be having a conversation about that? Happy New Year

 

[zelarra821]

Hello. I have solved the problem using the technique that you do not recommend, because, even though I agree with everything you advocate in the last posts, it is something simple that does not present a major problem to solve what I had at hand in my case, which does not mean that which in other cases is contraindicated. He didn't write anything to me because he had nothing to argue. Perhaps, I admit, I would have forgotten to mention that I had solved it, which is why I apologize. Without anything else, please accept my most sincere thanks and the wish that you have the best possible year in 2024, which we will receive in a few hours.

 

[Pat Hartman]

You agree with everything I said but chose to go with the inferior method???? So using the double click event was too complex for you? I guess it really doesn't matter anyway since you are doing something far worse by storing the same data in multiple places.
Happy New Year

 

[zelarra821]

For me, no, but the user so cause he doesn't know to use Access so much.

 

[Pat Hartman]

For me, no, but the user so cause he doesn't know to use Access so much.

I have nothing more to contribute. Good luck.

 

[zelarra821]

Thanks, Pat