Form fields not updating correctly

[CodeCurious]

Hello-
I’m having a problem with form fields not updating correctly. The form (frmEvaluation) is bound to a query (qryEvaluateApplicant). qryEvaluateApplicant is created in a VBA procedure. This procedure runs on the OnOpen event of frmEvaluation. On the OnLoad event of frmEvaluation, I requery the form.
The query is created correctly and retrieves current data, but when I open frmEvaluation, it opens with old data in the form fields. When I close and re-open frmEvaluation, then it updates correctly.
Some other information: the form (frmEvaluation) has a tab control that contains 5 pages. The record source for all controls in the tab control (textboxes and combo boxes) is qryEvaluateApplicant.
I’m hoping this is a ‘known issue’ or has a simple solution. I’d really appreciate help on this as I have a tight deadline.
[FONT=&quot]Thanks![/FONT]

 

[boblarson]

Post the SQL for qryEvaluateApplicant.

And post the VBA which creates it.

 

[CodeCurious]

Here's the SQL for qryEvaluateApplicant (after it has been created by the procedure):

SELECT tblFilmApplicant.app_student_id, tblApEvaluator.evaluator_ID, [tblFilmApplicant.app_first_name] & ' ' & [tblFilmApplicant.app_last_name] AS Name, tblFilmApplicant.term_value, tblFilmApplicant.submission_date, tblFilmApplicant.decision_plan_label, tblFilmApplicant.program_label, tblFilmApplicant.app_dob, tblFilmApplicant.app_address, tblFilmApplicant.app_city, tblFilmApplicant.app_state, tblFilmApplicant.app_zip, tblFilmApplicant.app_country, tblFilmApplicant.app_email, tblFilmApplicant.preferred_phone, tblFilmApplicant.app_home_phone, tblFilmApplicant.app_cell_phone, tblFilmApplicant.app_work_phone, tblFilmApplicant.acad_intent, tblFilmApplicant.emph_study, tblFilmApplicant.status, tblFilmApplicant.level_education, tblFilmApplicant.gpa, tblFilmApplicant.gpa_scale, tblFilmApplicant.gpa_weighted, tblFilmApplicant.inst_name, tblFilmApplicant.inst_city, tblFilmApplicant.inst_state, tblFilmApplicant.enrolled, tblFilmApplicant.grad_date, tblFilmApplicant.ged_complete, tblFilmApplicant.test_sat, tblFilmApplicant.act_math, tblFilmApplicant.act_read, tblFilmApplicant.act_english, tblFilmApplicant.test_act, tblFilmApplicant.coll_end_date_2, tblFilmApplicant.coll_begin_date_2, tblFilmApplicant.coll_state_2, tblFilmApplicant.coll_city_2, tblFilmApplicant.coll_name_2, tblFilmApplicant.coll_end_date_1, tblFilmApplicant.coll_begin_date_1, tblFilmApplicant.coll_state_1, tblFilmApplicant.coll_city_1, tblFilmApplicant.coll_name_1, tblFilmApplicant.coll_end_date_0, tblFilmApplicant.coll_begin_date_0, tblFilmApplicant.coll_state_0, tblFilmApplicant.coll_city_0, tblFilmApplicant.coll_name_0, tblFilmApplicant.advanced_no, tblFilmApplicant.advanced_unsure, tblFilmApplicant.advanced_ib, tblFilmApplicant.advanced_unsure, tblFilmApplicant.advanced_ap, tblFilmApplicant.film_work_none, tblFilmApplicant.employment_plans, tblFilmApplicant.current_employer_phone, tblFilmApplicant.current_employer_zip, tblFilmApplicant.current_employer_state, tblFilmApplicant.current_employer_city, tblFilmApplicant.current_employer_address, current_employer_name, tblFilmApplicant.employment_status, tblFilmApplicant.accu_math, tblFilmApplicant.accu_sentence, tblFilmApplicant.accu_reading, tblFilmApplicant.test_accuplacer, tblFilmApplicant.toefl_writing_type, tblFilmApplicant.toefl_writing, tblFilmApplicant.toefl_speaking_type, tblFilmApplicant.toefl_speaking, tblFilmApplicant.toefl_listening_type, tblFilmApplicant.toefl_listening, tblFilmApplicant.toefl_reading_type, tblFilmApplicant.toefl_reading, tblFilmApplicant.test_toefl, tblFilmApplicant.sat_math, sat_verbal, tblFilmApplicant.snapshot_6, tblFilmApplicant.snapshot_7, tblFilmApplicant.snapshot_5, tblFilmApplicant.snapshot_4, tblFilmApplicant.snapshot_3, tblFilmApplicant.snapshot_2, tblFilmApplicant.snapshot_1_3, tblFilmApplicant.snapshot_1_2, snapshot_1_1, tblFilmApplicant.film_work_2, tblFilmApplicant.film_duration_2, tblFilmApplicant.film_location_2, tblFilmApplicant.film_supervisor_2, tblFilmApplicant.film_company_2, tblFilmApplicant.film_work_1, tblFilmApplicant.film_duration_1, tblFilmApplicant.film_location_1, tblFilmApplicant.film_supervisor_1, tblFilmApplicant.film_company_1, tblFilmApplicant.film_work_0, tblFilmApplicant.film_duration_0, tblFilmApplicant.film_location_0, tblFilmApplicant.film_supervisor_0, tblFilmApplicant.film_company_0, tblFilmApplicant.applicant_signature_date, tblFilmApplicant.applicant_signature, tblFilmApplicant.essay, tblFilmApplicant.essay_choice, tblFilmApplicant.snapshot_8
FROM tblFilmApplicant INNER JOIN tblApEvaluator ON tblFilmApplicant.app_student_ID = tblApEvaluator.app_student_ID
WHERE tblApEvaluator.evaluator_ID = 'S01716626';

And here's the VBA that creates qryEvaluateApplicant:

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim qdfTemp As QueryDef

strSQL = "SELECT tblFilmApplicant.app_student_id, tblApEvaluator.evaluator_ID, [tblFilmApplicant.app_first_name] & ' ' & [tblFilmApplicant.app_last_name]AS Name, tblFilmApplicant.term_value, " _
& "tblFilmApplicant.submission_date, tblFilmApplicant.decision_plan_label, tblFilmApplicant.program_label, tblFilmApplicant.app_dob, tblFilmApplicant.app_address, " _
& "tblFilmApplicant.app_city, tblFilmApplicant.app_state, tblFilmApplicant.app_zip, tblFilmApplicant.app_country, tblFilmApplicant.app_email, tblFilmApplicant.preferred_phone, " _
& "tblFilmApplicant.app_home_phone, tblFilmApplicant.app_cell_phone, tblFilmApplicant.app_work_phone, tblFilmApplicant.acad_intent, " _
& "tblFilmApplicant.emph_study, tblFilmApplicant.status, tblFilmApplicant.level_education, tblFilmApplicant.gpa, tblFilmApplicant.gpa_scale, tblFilmApplicant.gpa_weighted, " _
& "tblFilmApplicant.inst_name, tblFilmApplicant.inst_city, tblFilmApplicant.inst_state, tblFilmApplicant.enrolled, tblFilmApplicant.grad_date, tblFilmApplicant.ged_complete, " _
& "tblFilmApplicant.test_sat, tblFilmApplicant.act_math,tblFilmApplicant.act_read,tblFilmApplicant.act_english,tblFilmApplicant.test_act,tblFilmApplicant.coll_end_date_2, " _
& "tblFilmApplicant.coll_begin_date_2,tblFilmApplicant.coll_state_2,tblFilmApplicant.coll_city_2,tblFilmApplicant.coll_name_2,tblFilmApplicant.coll_end_date_1, " _
& "tblFilmApplicant.coll_begin_date_1,tblFilmApplicant.coll_state_1,tblFilmApplicant.coll_city_1,tblFilmApplicant.coll_name_1, " _
& "tblFilmApplicant.coll_end_date_0,tblFilmApplicant.coll_begin_date_0,tblFilmApplicant.coll_state_0,tblFilmApplicant.coll_city_0,tblFilmApplicant.coll_name_0, " _
& "tblFilmApplicant.advanced_no,tblFilmApplicant.advanced_unsure,tblFilmApplicant.advanced_ib,tblFilmApplicant.advanced_unsure, tblFilmApplicant.advanced_ap, " _
& "tblFilmApplicant.film_work_none,tblFilmApplicant.employment_plans,tblFilmApplicant.current_employer_phone, " _
& "tblFilmApplicant.current_employer_zip,tblFilmApplicant.current_employer_state,tblFilmApplicant.current_employer_city,tblFilmApplicant.current_employer_address,current_employer_name, " _
& "tblFilmApplicant.employment_status,tblFilmApplicant.accu_math,tblFilmApplicant.accu_sentence,tblFilmApplicant.accu_reading,tblFilmApplicant.test_accuplacer, " _
& "tblFilmApplicant.toefl_writing_type,tblFilmApplicant.toefl_writing,tblFilmApplicant.toefl_speaking_type,tblFilmApplicant.toefl_speaking,tblFilmApplicant.toefl_listening_type,tblFilmApplicant.toefl_listening,tblFilmApplicant.toefl_reading_type, " _
& "tblFilmApplicant.toefl_reading,tblFilmApplicant.test_toefl,tblFilmApplicant.sat_math,sat_verbal, " _
& "tblFilmApplicant.snapshot_6,tblFilmApplicant.snapshot_7,tblFilmApplicant.snapshot_5,tblFilmApplicant.snapshot_4, " _
& "tblFilmApplicant.snapshot_3,tblFilmApplicant.snapshot_2,tblFilmApplicant.snapshot_1_3,tblFilmApplicant.snapshot_1_2,snapshot_1_1,tblFilmApplicant.film_work_2, tblFilmApplicant.film_duration_2, tblFilmApplicant.film_location_2, " _
& "tblFilmApplicant.film_supervisor_2, tblFilmApplicant.film_company_2, tblFilmApplicant.film_work_1,tblFilmApplicant.film_duration_1, " _
& "tblFilmApplicant.film_location_1,tblFilmApplicant.film_supervisor_1,tblFilmApplicant.film_company_1,tblFilmApplicant.film_work_0, " _
& "tblFilmApplicant.film_duration_0,tblFilmApplicant.film_location_0,tblFilmApplicant.film_supervisor_0,tblFilmApplicant.film_company_0, " _
& "tblFilmApplicant.applicant_signature_date,tblFilmApplicant.applicant_signature,tblFilmApplicant.essay,tblFilmApplicant.essay_choice,tblFilmApplicant.snapshot_8 " _
& "FROM tblFilmApplicant INNER JOIN tblApEvaluator ON tblFilmApplicant.app_student_ID = tblApEvaluator.app_student_ID " _
& "WHERE tblApEvaluator.evaluator_ID = '" & sLoginID & "';"

Set qdfTemp = CurrentDb.QueryDefs("qryEvaluateApplicant")
qdfTemp.SQL = strSQL
qdfTemp.Close
End Sub

 

[boblarson]

Looks like you dno't have an ORDER BY clause. That is important if you are looping through the recordset. Tables don't store data in any particular order (when you compact and repair it does try to order it by PK but it quickly gets out of order - think of it like a big bucket of fish and when one fish is taken out all the rest can slide around in a random order).

 

[BigHappyDaddy]

... but when I open frmEvaluation, it opens with old data in the form fields. When I close and re-open frmEvaluation, then it updates correctly.

Maybe missing a requery?

Private Sub Form_Open(Cancel As Integer)
. 
.
.
Set qdfTemp = CurrentDb.QueryDefs("qryEvaluateApplicant")
qdfTemp.SQL = strSQL
qdfTemp.Close
[COLOR=red]Me.Requery[/COLOR]
End Sub

 

[CodeCurious]

I added an ORDER BY clause, but that didn't seem to help.

It seems like there's a 'disconnect' between the query and the form, in that the form shows data that isn't returned by the query and does not exist in the result set. However, I believe that the data that shows in the form is from a prior execution of the query. Is there caching going on, and maybe some way to clear the cache?

Thanks for your help.

 

[boblarson]

I added an ORDER BY clause, but that didn't seem to help.

It seems like there's a 'disconnect' between the query and the form, in that the form shows data that isn't returned by the query and does not exist in the result set. However, I believe that the data that shows in the form is from a prior execution of the query. Is there caching going on, and maybe some way to clear the cache?

Thanks for your help.

BigHappyDaddy actually has the correct answer (SORT OF). I thought you were looping through a recordset. But if you are modifying the query with a QueryDef, then you can simply reassign the record source to the form.

Me.RecordSource = "qryEvaluateApplicant"

AFTER you have closed the QueryDef.

 

[CodeCurious]

I have a requery when the form loads (after the query executes, which is OnOpen), but also tried your suggestion, BigHappyDaddy, and added it to the procedure. Still not working ...

Thanks for the suggestion.

 

[boblarson]

I have a requery when the form loads (after the query executes, which is OnOpen), but also tried your suggestion, BigHappyDaddy, and added it to the procedure. Still not working ...

Thanks for the suggestion.

I had the same issue with a requery. It doesn't work when you are modifying the query via a QueryDef object. So you have to reassign the record source (see my post just before this one).

 

[CodeCurious]

That fixes it! Thanks very much!