event not firing

[david123]

I have an event that won't fire. It's in an unbound text box in a subform that's supposed to compare the recordset in that subform with the recordset in another subform. However, when I run the form the textox is blank.

*Also, I tried running breakpoints but still nothing happens

Dim RS As DAO.Recordset
Set RS = [Forms![Staff Training]![CoursesCompletedforPosition Query subform].Form!Courses].Recordset
Dim found As Boolean
Do Until RS.EOF
If RS("Courses") = Courses.Value Then found = True
Loop
If found Then
Text4.Value = "Yes"
Else: Text4.Value = "No"
End If

 

[Dennisk]

Events do not fire if the control is updated via VBA, only when a users types something in. So explicitly call the code required to run.

 

[dkinley]

If "Text4.Value" is the unbound text box then you can try to use "Text4.Text".

If I recall correctly - this is based on two premises - the control is unbound - therefore it doesn't have a value and some event will not work for unbound controls (after update, etc).

-dK

 

[david123]

Events do not fire if the control is updated via VBA, only when a users types something in. So explicitly call the code required to run.

Sorry - how do I do that? The code is stored in the text box's afterupdate event

 

[dkinley]

Right ... per Access Help -

AfterUpdate macros and event procedures run only if you change the data 
in a control. This event does not occur when a value changes in 
a calculated control.

I am taking this as the after update event will not work in your situation.

-dk

 

[david123]

Right ... per Access Help -

AfterUpdate macros and event procedures run only if you change the data 
in a control. This event does not occur when a value changes in 
a calculated control.

I am taking this as the after update event will not work in your situation.

-dk

Thanks for letting me know
Correct. Is there anything you can suggest in this situation to get the event to fire? I've tried in the BeforeUpdate as well but still no results

 

[dkinley]

If you are just comparing recordsets ... why are you trying to do it a control?

I am trying to understand what the trigger for the comparison is ...

If its some calculation - why do it in the control? Just do it in VBA and post the results in a control or something. I am not sure of your premise.

-dk

 

[david123]

If you are just comparing recordsets ... why are you trying to do it a control?

I am trying to understand what the trigger for the comparison is ...

If its some calculation - why do it in the control? Just do it in VBA and post the results in a control or something. I am not sure of your premise.

-dk

I'm not sure of another way to do it. I was hoping the trigger would be when the main form's record was updated.
It's not a calculation, just checking two recordsets to see if they match or not and outputting "Yes" or "No".

Before I entered this into the textbox's control:
=IIf(Forms![MainForm]![Subform].Form!Field=Me.Field,"Yes","No")

It would work but only for the first record.

Would you have any suggestions as to how I can just do it vba and post the results in a control like you mentioned?

Thank you!

 

[Dennisk]

Move the trigger code to a standard procedure. Then when you update the text box, run the procedure

e.g.
Text4.text = Something
Call YourProcedure()

 

[dkinley]

If all you are doing is something simple is that then you might try it on the OnCurrent event of the record.

Other than that doubtful approach, Dennisk has the best solution and call it each time you do the other stuff so it is updated accurately and timely.

-dK

 

[david123]

If all you are doing is something simple is that then you might try it on the OnCurrent event of the record.

Other than that doubtful approach, Dennisk has the best solution and call it each time you do the other stuff so it is updated accurately and timely.

-dK

I tried it in the OnCurrent event in the subform, but i get a 2450 error on this line:

Set RS = Forms!Staff_Training!CoursesCompletedforPosition_Query_subform.Form!Courses.Recordset

It can't find the 'Staff_Training' main form, so I realized my syntax must be way off. So I googled around and came up with this: Set RS = db.OpenRecordset("Select Courses" & "From Forms!Staff_Training!CourseCompletedforPosition_Query_Subform")

but now I get a 424 error.

Any advice you can give would be great, thanks

 

[david123]

I tried it in the OnCurrent event in the subform, but i get a 2450 error on this line:

Set RS = Forms!Staff_Training!CoursesCompletedforPosition_Query_subform.Form!Courses.Recordset

It can't find the 'Staff_Training' main form, so I realized my syntax must be way off. So I googled around and came up with this: Set RS = db.OpenRecordset("Select Courses" & "From Forms!Staff_Training!CourseCompletedforPosition_Query_Subform")

but now I get a 424 error.

Any advice you can give would be great, thanks

Just a note...

In the procedure i have:Select Courses" & "From Forms!Staff_Training!CourseCompletedforPosition_Query_Subform

but when I posted in here it came up:Select Courses" & "From Forms!Staff_Training!CourseCompletedforPosition_Qu ery_Subform

sorry!

 

[missinglinq]

As with others here, I'm somewhat at sea as to exactly what you're trying to do here, but if you're trying to check the value entered in this field to see if it exists in another table, then you can easily use DLookup() function for this.

Sorry, dk, but your memory is faulty. Unbound controls do have a Value Property! The .Text Property seldom has a place in Access VBA code, as it requires the control to currently have Focus.

 

[david123]

As with others here, I'm somewhat at sea as to exactly what you're trying to do here, but if you're trying to check the value entered in this field to see if it exists in another table, then you can easily use DLookup() function for this.

Sorry, dk, but your memory is faulty. Unbound controls do have a Value Property! The .Text Property seldom has a place in Access VBA code, as it requires the control to currently have Focus.

I'm just trying to compare the values of a field on two different subforms (see below). Basically, all i want to do is let the user know which courses they have left to complete. The two subforms are running off of seperate queries.

In the database I have a positions table, a positioncourses table (which lists positions and the relevant courses to each one), a courses table, an employee training table (listing the employee's name, the courses they've completed, and the date it's been completed) and a staff list table (listing each employee with their position).

I've made a query to get the courses completed by employee which are relevant to their positions, but now I'm just trying to show courses they have left to complete