Combo Box On Subform not working

[FredaH302]

Hi,

I have a combo box that I want to use as a filter. The combo box works fine until I embed the form into another (as a subform). I am sure it has to do with how a control is referenced on subforms.

Here is the code as it exists now:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Available_list"

stLinkCriteria = "[category]=" & "'" & Me![Cat_Search] & "'"
DoCmd.ApplyFilter stDocName, stLinkCriteria


Cat_Search = The combo box containing the above code.
Category = The field being filtered.

What is the proper way to reference the stlinkcritera ?

 

[Eupher]

Hi, FredaH302,

Here's a link with good info about referencing subforms:

http://www.mvps.org/access/forms/frm0031.htm

Here's another:

http://support.microsoft.com/kb/113352

HTH,

Ken S.

 

[FredaH302]

I really appreciate the info. The problem is that I have tried the "correct" syntax; as mentioned in the articles you referenced, but cannot get it to work.

The subform exists on a tab control. Does that matter?

 

[IGPCS]

no its dose not matter

can you paste the sql the combo is based on?

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[FredaH302]

The combo box (searchbox-named Cat_Search) is tied simply to a value list that provides three values:

Common
Security
Other

The subform has a [Category] field that stores one of these values for each of its records.

When a user selects one of the values (above), the subform should filter to display only those matching records. The [Cat_Search] box exists on the subform which it is filtering. The subform is in a tab control

The only code I have is on [Cat_Search]:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Available_list"

stLinkCriteria = "[category]=" & "'" & Me![Cat_Search] & "'"
DoCmd.ApplyFilter stDocName, stLinkCriteria

As I mentioned, the code works fine if I open the subform by itself, but when it is placed within another form it doesn't.

 

[IGPCS]

now you have to eliminate the combo box to show you only values for records that have the same likedtableID (i.e. if i have a customers (main form) and address (SubFOrm) and i use the customers id and now i want to filter and see only the address locations that this specific customer has so i will base the combo box values on a query that give me only this customers location so if he has 2 locations only like home and work this is what i will see in the combo box

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[FredaH302]

I am sorry, but I don't understand what you are saying.

To be clear, I do not want to filter the main form. I want to filter the records on the subform. The subform records are the related records for the active main form record.

 

[IGPCS]

i underestood that you want to filter the subform

are the 2 form linked subform vs MainForm if yes what field links them

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[FredaH302]

Each form has a [Facility ID] field in their underlying table. The subform is properly linked to the main form on this field.

I honestly do not think this is the problem. I believe it is in the "stlinkcriteria" statement.

 

[Eupher]

And have you tried:

stDocName = "Forms!Mainform!Subform1.Form"

...where "Mainform" is the name of the form on which your subform resides, and "Subform1" is the name of the subform control on your main form (not necessarily the same as the name of the form that lives in the subform control).

HTH,

Ken S.

 

[FredaH302]

Alright, so let's try this.

Here are my form particulars:

Main form: frm_housing
subform: frm_housing_Features
tabcontrol: tbctl35

[Category] subform field to be searched
[Cat_Search] Combo box that filters subform records

So, to construct your statement, I would type...

stDocName = "Forms!frm_housing!tbctl35.Form" ??

 

[IGPCS]

Forms!Frm_housing!frm_housing_Features.Form"

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[FredaH302]

Guys, I really appreciate your help. Unfortunately, it is still not working.

Here is my code now:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Forms!frm_housing!frm_housing_features.Form"

stLinkCriteria = "[category]=" & "'" & Me![Cat_Search] & "'"

DoCmd.ApplyFilter stDocName, stLinkCriteria


I keep thinking the problem is with: stLinkCriteria

 

[IGPCS]

instead of for apply filter try this

frm_housing_features.Filter = [Category] = Cat_Search

not sure but try it



I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[FredaH302]

Sorry, IGPCS, it didn't appear to work.

I also tried modifying the stlinkcriteria (as I have many times) to read:

stLinkCriteria = "Forms!frm_housing_questionnaire!frm_housing_feature_list.Form![category]=" & "'" & Forms!frm_housing_questionnaire!frm_Housing_Feature_List.Form![Cat_Search] & "'"

Here's what happened. If I choose one value from the searchbox, it either shows nothing (in both the primary and subform) or it filters using a value I didn't choose. Either way, the info displayed is incorrect.

 

[IGPCS]

why dont you try stlinkcriteria = Form_frm_Housing_Feature without ""

i dont see any problem with your first code

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[Eupher]

Hi, again,

I poked around in the help files (I rarely use filters), and it seems the ApplyFilter method may not be right for this scenario. From the help files:

The ApplyFilter method carries out the ApplyFilter action in Visual Basic.

expression.ApplyFilter(FilterName, WhereCondition)
expression Required. An expression that returns one of the objects in the Applies To list.

FilterName Optional Variant. A string expression that's the valid name of a filter or query in the current database.

Try this instead:

Forms!frm_housing![blue]frm_housing_features[/blue].Form.Filter = stLinkCriteria
Forms!frm_housing![blue]frm_housing_features[/blue].Form.FilterOn = True

But I must reiterate - the text in blue above must be the NAME of the SUBFORM CONTROL on you main form - NOT the name of the form that resides in the subform control. They can be the same name, but often are not!

HTH,

Ken S.

 

[FredaH302]

Yes, finally. I was about to call Microsoft on this. Thank you very much for you help.

I do have one question though. Your statement:

"the text in blue above must be the NAME of the SUBFORM CONTROL on you main form - NOT the name of the form that resides in the subform control"

I am not following this. The Subform control would be my subform - correct? What, then, do you mean by the form residing on the subform control?

 

[Eupher]

Hi, FredaH302,

The Subform control would be my subform - correct?

NO! - at least, not necessarily. When you place a subform on your form, you are placing a subform control. The subform control is, in essence, a wrapper for another form. The subform control has a .Name property, which is how it is referenced in code. It also has a .SourceObject property, which is the name of the form that resides in it. The Name of the control and its SourceObject can be the same - but often are not. If they are not the same and you try to reference the subform using the SourceObject's name - the code will fail!

HTH,

Ken S.