Yet Another Access Question

[archcommus]

In Microsoft Access, what sort of event fires when a control is changed programmatically? Apparently the Change and Dirty events only fire when text is directly input into a control. I need an event that fires when a control is changed as a result of being dependent on other controls.

Thanks.

 

[MyK Von DyK]

Don't you first have to set focus to the visual control that changes text? I've tried what I thought you were talking about but can't find any problems - OnChange fires. Example with two text boxes and one command button:

Option Compare Database

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Text0.SetFocus
Text0.Text = Text0.Text + " blah"

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

Private Sub Text0_Change()

Text3.SetFocus
Text3.Text = "OnChange"

End Sub

Could you possibly elaborate what exactly are you trying to do?

 

[archcommus]

Thanks for the code, unfortunately I don't quite understand it. Here is what I'm trying to do. I have a continuous form with a control called Hours. I have a control in the header that is to set to =Sum([Hours]). Whenever Hours is updated, the Sum is updated after a one or two second delay. Once that Sum control has been updated, I want it to set that value in another control in a subform so it can be written to a table. I already have that part working with a simple .Value setting in my VB code. However I need the right event (that fires when the Sum is updated) to trigger that happening.

Thanks so much!

 

[MyK Von DyK]

You write the sum is updated with some delay which confuses me even further. If you're using table for your values in continuous form, than what you need is to call a custom function in either (possibly both) "Default Value" or "Validation Rule" properties of that field. Remember that "Validation Rule" needs boolean return value and the function called would obviously have to be public. If you use objects that are not linked to the table, then you need to write your update code on "OnUpdate" event of the "Hours" control, disable automatic linking with your "Sum" control and trigger calculation manually. Once that's done, you update your database. Besides, you could always calculate sum manualy with a simple "for each" loop. It wouldn't perform slower than with automatic linking anyway.

 

[archcommus]

Oy I'm getting confused. But thanks for staying with me on this.

The continuous form IS linked to a table. Every time someone puts text into Hours it makes a new blank record underneath that to enter more hours figures. The control in the header of the form is just set to equal the sum. It just delays because it takes a second for it realize you updated Hours and then to resum the column. Most calculated fields behave this way. Beyond that, I don't understand what you're saying about the validation rule and such, my apologies. I just know that once Sum is updated, I need to run a chunk of code.

 

[MyK Von DyK]

Calculate the sum manually on the "OnChange" event of the "Hours" text box with "for each" loop and assign it to your "Sum" text box/label. After you've done that you can use this same value to update your database. I don't see any reason why this wouldn't work😕

PS: if you whish you can send me PM with your code (link to sample project even better) and I'll fill in the rest

 

[archcommus]

Originally posted by: MyK Von DyK
Calculate the sum manually on the "OnChange" event of the "Hours" text box with "for each" loop and assign it to your "Sum" text box/label. After you've done that you can use this same value to update your database. I don't see any reason why this wouldn't work😕

That should work just fine. The only reason I was using the calculated control for the sum was because I didn't know how to sum the Hours control in VB code. You can't use the Sum function and I think the DSum function is only for tables and queries. Do you know what kind of code I need to do this?

EDIT: The database is too gigantic to go through and pick out what I'd like to send you, thanks, though. All I need to know is how to sum a control on a continuous form with VB code and I should be all set! Thanks!

 

[MyK Von DyK]

PM me your email adress and I'll send you a sample project then...

 

[MyK Von DyK]

You need a code that would look something like this:



Private Sub CalcSum()

Dim i As Variant
Dim rcs As Recordset

i = 0
days = 0
Me.txtSum.Value = ""

Set rcs = Me.RecordsetClone

If rcs.RecordCount > 0 Then

rcs.MoveFirst

While Not rcs.EOF

i = i + rcs.Fields("Hours").Value
rcs.MoveNext

Wend

End If

Me.txtSum.Value = i

End Sub

Then call this sub on "OnOpen" and "AfterUpdate" events of the form.

Best, MyK

PS: I've sent you a sample project to your email address, also.

 

[archcommus]

Thank you so much for your effort and help with this, MyK! I checked our your database, did some rummaging with your code and it seems to be working almost perfectly now. The only problem I still have (and I have this problem with your sample database, as well) is that when I delete a record the sum is not reflected properly until I reload the form. Any ideas?

Thanks again very much.

 

[KLin]

Originally posted by: archcommus
Thank you so much for your effort and help with this, MyK! I checked our your database, did some rummaging with your code and it seems to be working almost perfectly now. The only problem I still have (and I have this problem with your sample database, as well) is that when I delete a record the sum is not reflected properly until I reload the form. Any ideas?

Thanks again very much.

You can use the Requery action to update the data in a specified control on the active object by requerying the source of the control. If no control is specified, this action requeries the source of the object itself. Use this action to ensure that the active object or one of its controls displays the most current data.

You'll want to put in some code on the form for the "AfterDelConfirm" event, to fire requerying of the control. You'd put in a line something like txtHours.requery,

 

[MyK Von DyK]

Originally posted by: KLin
You'll want to put in some code on the form for the "AfterDelConfirm" event, to fire requerying of the control.

Yup, that should work just fine. Just add same sub call to "After Del Confirm" event procedure as with "After Update". Like:

Private Sub Form_AfterDelConfirm(Status As Integer)
CalcSum
End Sub

Best, MyK

PS: You have new mail🙂

 

[archcommus]

Thank you both very much, especially for the sample databases, helped a ton. I will implement that new code tomorrow morning and let you know how it goes!

 

[archcommus]

Thank you to both of you very much! I have it all implemented and it works perfectly. The sum gets updated just how it should. I even added another control in the main form that sums the other Sum control by adding a Form.Requery and a control requery command at the end of your function. This runs a query in that control that sums the other Sum figures from the table they get written to. Couldn't have asked for more.

Thanks!