Real time update of field in form (1 Viewer)

Kuleesha

Member
Local time
Today, 18:39
Joined
Jul 18, 2021
Messages
50
Hi,
I ve attached a small sample database below.
In the frmCalcium form I need the Range box to be updated real time as i type in the values in the calcium field. I ve set up the vba code to the after update event of the calcium text box, but then i have to click on another field for the range box to be updated.
How can i acheive what i want?
Thanks for the help
 

Attachments

  • Test.accdb
    500 KB · Views: 339

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,357
Hi. Haven't looked at your sample db yet, but have you tried using the Change event?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2002
Messages
42,970
The Change event is not used for this purpose. Change is used ONLY when you want to capture each character as it is typed into a control. Change runs multiple times. Once for each character typed. The control's BeforeUpdate or AfterUpdate events are more appropriate, depending on exactly what needs to happen. In this case, you would use the AfterUpdate event. HOWEVER, doing so violates normal forms since you are storing duplicate data which can conflict if not kept properly in sync.

The way to handle this is to create a range table that has Range as it's Primary key and two data fields - Low and High or Begin and End or whatever makes sense to you. Then your query will join to the range table using a left join.

Select tblCalcium.*, tblRange.Range
From tblCalcium Left Join tblRange on tblCalcium.Calcium >= tblRange.Low AND tblCalcium.Calcium <= tblRange.High

This join CANNOT be created in QBE view. You will need to switch to SQL View to create it because it is a non equi-join

Remove the Range field from tblCalcium.



And finally, I'm pretty sure you are headed down the wrong path. You are using data values as your table name and column name. That isn't how we create relational databases. The table name should probably be something like tblSubstance. And instead of Calcium, the column name should be SubstanceValue and you need a third column to identify the Substance. It's value would be "Calcium" The way you are going, you would need a separate table for everything you want to measure.

If you have more elements than just Calcium, you don't need to create a separate Range table for each. You can use the same table by adding a fourth column to specify the Substance name. So, the three rows for Calcium, would each have Calcium as the Substance. This would also change the PK to be compound - Substance + Range.
 
Last edited:

Kuleesha

Member
Local time
Today, 18:39
Joined
Jul 18, 2021
Messages
50
Hi. Haven't looked at your sample db yet, but have you tried using the Change event?
I ve tried that but that gives even more delay in the update - i have to first click outside the text box and then click back inside for the change to occur in this case.
The Change event is not used for this purpose. Change is used ONLY when you want to capture each character as it is typed into a control. Change runs multiple times. Once for each character typed. The control's BeforeUpdate or AfterUpdate events are more appropriate, depending on exactly what needs to happen. In this case, you would use the AfterUpdate event. HOWEVER, doing so violates normal forms since you are storing duplicate data which can conflict if not kept properly in sync.

The way to handle this is to create a range table that has Range as it's Primary key and two data fields - Low and High or Begin and End or whatever makes sense to you. Then your query will join to the range table using a left join.

Select tblCalcium.*, tblRange.Range
From tblCalcium Left Join tblRange on tblCalcium.Calcium >= tblRange.Low AND tblCalcium.Calcium <= tblRange.High

This join CANNOT be created in QBE view. You will need to switch to SQL View to create it because it is a non equi-join

Remove the Range field from tblCalcium.



And finally, I'm pretty sure you are headed down the wrong path. You are using data values as your table name and column name. That isn't how we create relational databases. The table name should probably be something like tblSubstance. And instead of Calcium, the column name should be SubstanceValue and you need a third column to identify the Substance. It's value would be "Calcium" The way you are going, you would need a separate table for everything you want to measure.

If you have more elements than just Calcium, you don't need to create a separate Range table for each. You can use the same table by adding a fourth column to specify the Substance name. So, the three rows for Calcium, would each have Calcium as the Substance. This would also change the PK to be compound - Substance + Range.
Thank you.
Sorry but i'm very new to this and cannot understand how to go about setting up the SQL.
If it is not too much trouble for you could you edit it on the database i have attached and upload it so that i can observe it.
Ever greatful.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2002
Messages
42,970
I will do it later tonight when I get home from the concert.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:09
Joined
Feb 28, 2001
Messages
26,996
My concern is more because of the "real-time" nature of whatever you are doing. My screen name is The_Doc_Man because I am a doctor of chemistry - actually, a PhD in Analytical Chemistry (Univ. of New Orleans, 1975).

Tell me what you are doing. I will understand the description of your experiment and its intent. What are you attempting to capture? Further, an isolated reading by itself is useless. It has to be made in a context, so what is the context? Time? Some other reading? Explain what you are doing.

Here is a thought for you that might get you started.

On this text box, create a LostFocus event. If you hit either the TAB key or the ENTER key, you trigger the LostFocus event. Inside the lost focus event routine, save your information and before you leave the routine, do a textbox.SetFocus as the last thing in the routine. Note that this way, you can get reasonably close to real-time given that human-finger inputs are involved. But when you are done taking observations, you will need some other way, perhaps a command button - or merely close the form - to stop this process. Note that closing the form with the upper-right [X} will store one more data because that click potentially ALSO triggers a LostFocus on the textbox, so the last reading might be suspect.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:09
Joined
May 7, 2009
Messages
19,169
see change event.
 

Attachments

  • Test (7).accdb
    500 KB · Views: 318

Kuleesha

Member
Local time
Today, 18:39
Joined
Jul 18, 2021
Messages
50
Thanks. This is just what i wanted. Much appreciated.
Could the same be applied to realtime display of the result in a calculated field ?
e.g. txtA = txtB*10 Is it possible to show the result in textbox A right after (or during) you type in the value in textbox B rather than have to click outside the textbox B to display the result?
 

Users who are viewing this thread

Top Bottom