Real time update of field in form

[Kuleesha]

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

 

[theDBguy]

Hi. Haven't looked at your sample db yet, but have you tried using the Change event?

 

[Pat Hartman]

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.

 

[Kuleesha]

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]

I will do it later tonight when I get home from the concert.

 

[The_Doc_Man]

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]

see change event.

 

[Kuleesha]

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?