Solved Subform combo RowSource to Change At Diff. Combo Operations from Mainform. (1 Viewer)

[Ashfaque]

Hi,

I have mainform with a subform. Mainform is unbound but subform is bound with table.

On mainform I have a combo CboAddOT wherein I select dept and when I enter in subform to enter the relevant record of that dept, all the employee I can see in subform combo “CboOTEmp” using following code when I enter in subform and dropdown CboOTEmp.

This works perfectly while adding newrec, save, update etc thru diff code. NO issue till here.

But when I am trying to bring old record on mainform for which I am using another combo called CboSearchOT, all the record data is appearing on mainform and subform EXCEPT the combo CboOTEmp does not display relevant employees of selected dept.

[Forms]![F_EmpOTHeader]![SF_EmpOTFooter]!CboOTEmp.RowSource = [Forms]![F_EmpOTHeader]![SF_EmpOTFooter]!CboOTEmp.RowSource
[Forms]![F_EmpOTHeader]![SF_EmpOTFooter]!CboOTEmp.Requery

This I need to add some more employees of selected dept.

Can someone please help me?

 

[Gasman]

Doesn't that just say Rowsource=same Rowsource?

 

[bastanu]

The row source of CboOTEmp probably references CboAddOT, in the AfterUpdate event of the CboSearchOT reset the row source to another query\SQL string that references the CboSearchOT instead.

Cheers,

 

[Ashfaque]

Basically CboOTEmp combo is placed on subform with following Rowsource code of this combo is:

SELECT DISTINCT T_JobOffer.CName, T_JobOffer.Cno, T_JobOffer.CDept FROM T_JobOffer WHERE (((T_JobOffer.CDept)=[forms]![F_EmpOTHeader]![CboAddOT] Or (T_JobOffer.CDept)=[forms]![F_EmpOTHeader]![CboSearchOT])) ORDER BY T_JobOffer.CName;

So when at the new entry form main form when I select department in Combo CboAddOT, all the relevant employees of selected departments are appearing in subform combo.

I want to replace this when I use another combo from mainform called CboSearchOT. This combo is to search old records.

My old record is also appearing fully BUT the rowsource in subform combo is not generating relevant dept employees this time.

So I want to replace each time Rowsource of subform combo as per 2 diff combos from the mainform. I have attached 2 pics to make it more clear.

After update of combo CboAddOT at main form following line are used to requery the rowsource

[Forms]![F_EmpOTHeader]![SF_EmpOTFooter]!CboOTEmp.RowSource = [Forms]![F_EmpOTHeader]![SF_EmpOTFooter]!CboOTEmp.RowSource
[Forms]![F_EmpOTHeader]![SF_EmpOTFooter]!CboOTEmp.Requery

But I am confused if this is working then what is the maining of actual Rowsource of subform combo? which is (Select Dinstinct......)

Kindly extend your help... Thanks,

 

[Ashfaque]

I removed rowsource from property of subform combo CboOTEmp and placed this code in after event of combo CboSearchOT

Me.SF_EmpOTFooter!CboOTEmp.RowSource = "SELECT DISTINCT T_JobOffer.CName, T_JobOffer.Cno, T_JobOffer.CDept " & _
" FROM T_JobOffer WHERE ((((T_JobOffer.CDept)=[forms]![F_EmpOTHeader]![CboSearchOT])) ORDER BY T_JobOffer.CName;"
Me.SF_EmpOTFooter!CboOTEmp.Requery

The code has no error but not displaying relevant dept employees.

 

[bastanu]

Copy the SQL statement to the query designer (or save it as a query from the combo itself) and run it after you enter something in the main form search combo, it probably doesn't return any records. Could be that the combo displays the department name but is actually bound to department ID, can't really say much without seeing your form. In any case setting to row source to itself doesn't make much sense as Gasman pointed out.
What is the code in the AfterUpdate event of the search combo?
Cheers,

 

[Ashfaque]

Bastanu,

I have reduced records and other unnecessary objects from the attached db.

I have added one record only of Administration staffs. You may make another new entry for calibration dept. My Concern is only why the subform combo is not generating the employee list as per the selection at both combos of mainform.

I hope it will work smoothly at your end. Please check

 

[bastanu]

Please have a look at the updated file. Not quite sure what are you trying to do as the cboAddOT changes the header info so you could add a Calibration footer record to an Administration header. You will have to explain a bit better your intentions with the form. I have left some comments in the code for reference.

Cheers,
Vlad

 

[Ashfaque]

The main intention of search combo in main form is to search old record and end user might have forgotten to enter some employees of a particular dept so later on he can add. So once he search the old record entry from main form he can add few more additional employees of that dept

Sorry to say that in the updated db Subform combo is working for search only but not with new record. I need subform combos rowsource should be changed according to the main form combo....mean when use search combo, the subform combo should generate new rowsource as per selected dept and when I add new record at main form then the subfrm comb should generate the employees list as CboAddOT.

I think I have made it very clear to make you understand.

Thanks for your support.

 

[bastanu]

But if you want to add to "it" (meaning department via the OPID) you shouldn't be allowed to choose another department...I understood your original issue "very clearly" but it does not make sense so that is why I suggested you should review your logic. On an unbound form changing unbound textboxes based on combo selections without moving records is going to be very confusing to users.

Cheers,

 

[Ashfaque]

But if you want to add to "it" (meaning department via the OPID) you shouldn't be allowed to choose another department...I understood your original issue "very clearly" but it does not make sense so that is why I suggested you should review your logic. On an unbound form changing unbound textboxes based on combo selections without moving records is going to be very confusing to users.

Cheers,

User will add anther dept only at new record Sir. My only requirement is while user enter new entry at main form (by clicking New Entry btn) he will select new department and based on selected dept in combo CboAddOT, the rowsource should be changed of subform combo and display relevant emp list.

But when user just want add few more employees in the previous entry, he will select old record in combo CboSearchOT at main form and after update of CboSearchOT, the subform should change its rowsource and display relevent dept employees in it.

This is what I need because of flow of work.

Any other idea?

 

[bastanu]

This one should do what you want, please let me know if it works for you.
Cheers,

 

[Ashfaque]

This one should do what you want, please let me know if it works for you.
Cheers,

Thanks a lot.

It is working as expected. The only thing is it is fluctuating screen when changes rowsource. That need to make it smooth.
But that I can manage.

Thank you very much again.

 

[bastanu]

You're very welcome. Add a Docmd.Echo False before and Docmd.Echo True after the row source setting.

Cheers,