Filter continuous subform from combo box selection MS Access 2007

[BigMikeT76]

I am trying to get (2) pages of the tab control to filter subforms based on a combo box entry. I can't get the Filter property to work or an embedded macro run a query.

The combo box is from tblWorkFlowTitles and stores the value as dwfTitleNo

dwfTitleNo tblWorkFlowTitles 1=Draft Evaluations
dwfTitleNo tblWorkFlowTitles 2=Signatures

On the tab control:

pgEvaluations has a continuous subformDraftEvals and I would like it to only show the records where dwfTitleNo=1 (when Draft Evaluations has been selected from the combo box Name cboEval)

Similarly, on the tab control:

pgSignatures has a continuous subformSignatures that I would like it to only show records where dwfTitle=2 (when Signatures has been selected from the combo box Name cboSig)

Further, is there any way to only give the user the option of selecting the corresponding Draft Evaluations when doing data entry on the continuous subformDraftEvals and Signatures when doing data entry on the continuous subformSignatures from the combo box?

In summary, I only want subform located on pgEvaluations to show the records for the Work Flow Title: Draft Evaluations, and for the user to only be able to enter a Draft Evaluations Work Flow Title when adding a record into that subform. Likewise for Signatures and the subformSignatures located on pgSignatures to show the records for the Work Flow Title: Signatures.

I'm attaching the MS Access 2007 database on 4shared.com below. You can look at my issue by going into design view of frmMasterTabForm and selecting the tabs Evaluations and Signatures.

 

[BigMikeT76]

I had received some advice as follows:

"From your mainform, you can set a Filter on a Subform like this:

Me.NameOfYourSubformCONTROL.Form.Filter = " dwfTitleNo=1"
Me.NameOfYourSubformCONTROL.Form.FilterOn = True

Note the NameOfYourSubformCONTROL; this may or may not be the same as the form you're using as a SourceObject, so be careful with that.

This would be done in the AfterUpdate event of your combo."

However, when I used this code with my "NameOfYourSubformCONTROL" = cboSig and cboEvals for each corresponding subform, it did not recognize the code after Me.cboSig. Then I put in the name of the form: frmMasterTabForm just in case they meant .Form to be the Form Name, but no dice.....

any ideas???

 

[dhookom]

What is the actual code you tried? Do you have spaces in your control names? What are your field names? What are your field data types?

Duane
Hook'D on Access
MS Access MVP

 

[BigMikeT76]

I tried
Me.cboEval.Form.Filter = "dwfTitleNo=1"
Me.cboEval.Form.FilterOn = True
In the AfterUpdate for the combo box Name cboEval

Me.cboSig.Form.Filter = "dwfTitleNo=2"
Me.cboSig.Form.FilterOn = True
In the AfterUpdate for the combo box Name cboSig

My form is set up like this:

Form: frmMasterTabForm

Tab Control:
Page: pgSignatures
Combo Box Name: cboSig
Combo Box Control Source: dwfTitleNo

tblWorkFlowTitles
wrfTitleID dbLong PrimaryKey Indexed
wrfTitleName dbText

wrfTitleID 1 , wrfTitleName Draft Evaluations
wrfTitleID 2 , wrfTitleName Signatures

The combo box lists the wrfTitleName (Draft Evalautions and Signatures) and stores the corresponding Primary Key wrfTitleID in dwfTitleNo (foreign key of another table).

tblDocWorkFlow
dwfDWFID dbLong PrimaryKey Indexed
dwfDocID dbLong ForiegnKey Indexed
dwfTitleNo dbLong ForiegnKey Indexed
dwfEmployeeNo dbLong ForiegnKey Indexed
dwfRoleID dbLong ForiegnKey Indexed
dwfDateStart dbDate
dwfDateComplete dbDate
dwfNotes dbText
dwfDraftNo dbLong

 

[dhookom]

If you only want to show records with one value or the other, you should be able to set up the Link Master/Child properties to the control on the main form and the field on the child/subform.

There is an added benefit that any records added to the subform will automatically use the value in the control from the main form as the default value of the field on the subform.

Duane
Hook'D on Access
MS Access MVP

 

[BigMikeT76]

Thank you Duane. I will try it out and post the results. Looks like this will solve both of my questions! Thanks again.

 

[MajP]

BigMike,
Are you picking up Colebean's work or did you just change your handle?

 

[BigMikeT76]

I took over this project for a previous colleague, not sure if she was Colebean. Nicole? I saw this forum in her notes. I think I'm pretty close to finishing this up. Kind of hard coming in the middle of a design!!

 

[BigMikeT76]

Majp: I just noticed your initials in the FilterSortListBox module. So I think that confirms this is the same project...

 

[MajP]

Just curious. If you have questions, I wrote all the code in the database to include developing the search form. There some pretty involved processes. With the different numbering schemes.

 

[MajP]

Here is the original work Thread705-1587913.

 

[BigMikeT76]

Duane, I looked into your suggestion, however the main form is already sorting the subform for docID (tblDocInfo PK relationship one to many with tblDocWorkFlow dwfDocID fk). I am posting the tblDocumentInfo below. In addition to filtering for docID on the main form, I need to filter (2) subforms on the tab control for dwfTitleNo from the tblDocWorkFlow depending on which subform they are entering data into.

tblDocInfo
docID dbLong PrimaryKey Indexed
docType dbInteger ForiegnKey Indexed Required
docSeries dbDecimal Indexed Required
docVersion dbDecimal Indexed
docTitle dbText Indexed
docDescription dbText Indexed
docChangeCategory dbLong ForiegnKey Indexed
docChangeReason dbText
blnDocArchived dbBoolean
dtmDateTimeStamp dbDate
PurchaseOrderNo dbText
PurchaseOrderApprovalDate dbDate
EstimatedDeliveryDate dbDate
ActualDeliveryDate dbDate
DatePostedSharepoint dbDate
DateSubmittedEPA dbDate
DateEpaApproval dbDate
DateRetired dbDate
EmpRetiredNo dbLong ForiegnKey Indexed
ActiveSOPLink dbMemo
RetiredSOPLink dbMemo
EpaApprovalStoredLink dbMemo

Does this make sense?

 

[dhookom]

I'm not going to step on MajP's extensive work on this. It seems my original advice to normalize the table structure was ignored.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I do not think what you are asking makes any sense, or how the subforms are set up makes any sense.

I would think everything on the draft eval subform should represent a draft evaluation. Everything on the signature tab should represent signature.

If it was me I just get rid of the combo. Or can you explain what it is supposed to do? (I do not think an eval changes into a signature, Instead a new record is created for the signature.) Instead I would have a hidden textbox with the default value set to 1 and bound to the dwfTitleNo.
Then the rowsource query of the draft eval subform should include include a criteria:
WHERE (tblDocWorkFlow.dwfTitleNo)=1)
Now this form only shows dwfTitleNo's = 1 and enters only 1 for new records.

Same idea for signatures. See if this makes sense.

http://www.4shared.com/file/ygCUmu7-/FINAL_SOP_DCS_V27.html

Duane,
The OP did incorporate some normalization in the tblDocWorkFlow. They never fully got it. That is actually why there is a 1 for draft and a 2 for signature, kind of using your suggestions.

 

[BigMikeT76]

Wow. I just went over the previous thread. The work flows look like a great idea. I went ahead and changed the work flow table to incorporate the dates and employee info. From what I understood, I modified the Work Flow Titles table to handle each step: manager signature, QAQC signature, SOP Lead Signature, etc. I am going to revise the forms to reflect it. Thank you for connecting me to the original post. I attached the updated tables for the database just in case you were curious, looks like you put a lot of time into this project.

Thanks,
Mike