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.

 

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

 

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

 

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

 

[zelarra821]

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

 

[zelarra821]

Thanks, Pat

 

[zelarra821]

The issue is that I only want the button so that when it is in the oil type field the user can, through a series of inputboxes, add a price and a date, which will be saved in the table. I don't know if I can convey what I want to do. The subform table is not synchronized at all. Where it is saved is in a third table that has nothing to do with a main form and the subform. Do you get my idea?

Thanks to CJ and everyone who, at some point throughout the history of this thread, has been able to participate. Really thank you very much.

 

[zelarra821]

You're going to excuse me, but in the end the button wasn't necessary. I was recording a video to show you how it works when I realized the error.

Thank you very much for your clarifications.

Anyway, I'll explain what I've done with another example that does apply:

I have a form where the hours of work on each property of a worker, vehicle and tools are collected.

There are no subforms in that form.

There is a button to add labor, vehicle and implement prices.

I have four tables: one with the records of the hours, and another with the prices of labor, vehicles and tools.

With a Select Case, I detect the active field and if it corresponds to one of those that I can set a price, I insert the date and price that the user has added in the inputboxes. That way, it's faster than having to go to another table to add it.

That said, thank you very much.

 

[zelarra821]

Thanks to all.