Filtering split form with search box & combo boxes

[nelo]

I'm trying to figure out how I can filter the datasheet on my split form by use of a search box and 3 combo boxes. I'd like each record in the combo box to appear just once, even though they're not unique to any one firm, maybe by making a table for each?

Lastly under the "More Info" column i'd like there to be a link on each row which opens a "Details Form" I have already created for the respective firm.

Can anyone give me a hand with this? I've searched around but haven't found anything helpful yet.

...I've attached a picture of the form for reference as well as of the "Details Form"

 

[jzwp22]

Regarding the combo boxes, if an item (classification, industry etc.) can apply to many firms then it would probably be best to have a table of classifications, a table of industries etc. And then in your firm table just reference the key field of each of the applicable tables. This will allow you to easily correct spelling errors because you would just have 1 record to do the correction rather than potentially every record in your firm table. You would then base your combo boxes on those respective tables. If you don't want to use the separate tables, you can query your firm table using the DISTINCT predicate. (SELECT DISTINCT classification FROM firmtable). I would recommend the additional table approach since it would be better in the long run.

As to your second issue, I don't know if you can use a hyperlink in an expression to open the detail for to the specific record being chosen. Alternatively, you could use a continuous form rather than a datasheet form since you can add a button to a continous form. You would then add code to the on click event of the button to open the detail form to the specific firm selected.

 

[nelo]

Regarding the combo boxes, if an item (classification, industry etc.) can apply to many firms then it would probably be best to have a table of classifications, a table of industries etc. And then in your firm table just reference the key field of each of the applicable tables. This will allow you to easily correct spelling errors because you would just have 1 record to do the correction rather than potentially every record in your firm table. You would then base your combo boxes on those respective tables. If you don't want to use the separate tables, you can query your firm table using the DISTINCT predicate. (SELECT DISTINCT classification FROM firmtable). I would recommend the additional table approach since it would be better in the long run.

As to your second issue, I don't know if you can use a hyperlink in an expression to open the detail for to the specific record being chosen. Alternatively, you could use a continuous form rather than a datasheet form since you can add a button to a continous form. You would then add code to the on click event of the button to open the detail form to the specific firm selected.

You're right, I went ahead and created tables for all three of the combos (Classification, Industry, and Geographical) along with relationships and re-insert them as a combo box and two list boxes (since one firm can cover more than one industry or region) appropriately across my forms.

I was able to figure out the link issue, I stole the code from the template I'm basing my project off and it worked.

My main goal now is to get the search box and combo boxes to filter the data in the datasheet.
For example: If a user wants to search for a firm they could select a classification from the combo and one ore more industries and regions from the two list boxes, along with the search box for any further filtering.

Edit: The link was a macro and I added the following to link's properties
Control Source: =IIf(IsNull([ID]),"(New)","Open")
and attached is an image of the Macro which was embedded under "On Click"

 

[jzwp22]

I'm glad you were able to figure out the link issue. It might be beneficial for others who might come across this thread to see the actual code you used. Can you include that?


Since a firm can cover multiple regions and/or industries, did you set up appropriate junction tables since that would describe a many-to-many relationship? Similar to this:

tblCompanyIndustries
-pkCoIndID primary key, autonumber
-fkCompanyID foreign key to company table
-fkIndustryID foreign key to industry list table

Are you going to allow multiple selections in the list boxes or are you going to limit the user to just one choice in each? If you are going to use multiselect list boxes then I think the best way to handle the filtering is to dynamically create the query on which the datasheet is based in code and then apply that query to the datasheet's record source. If you are not going to use multiselect list boxes, you might find this link on query by forms useful.

 

[nelo]

I'm glad you were able to figure out the link issue. It might be beneficial for others who might come across this thread to see the actual code you used. Can you include that?


Since a firm can cover multiple regions and/or industries, did you set up appropriate junction tables since that would describe a many-to-many relationship? Similar to this:

tblCompanyIndustries
-pkCoIndID primary key, autonumber
-fkCompanyID foreign key to company table
-fkIndustryID foreign key to industry list table

Are you going to allow multiple selections in the list boxes or are you going to limit the user to just one choice in each? If you are going to use multiselect list boxes then I think the best way to handle the filtering is to dynamically create the query on which the datasheet is based in code and then apply that query to the datasheet's record source. If you are not going to use multiselect list boxes, you might find this link on query by forms useful.

I edited my previous post regarding the link fix.

I've been working on it all morning and came to the realizations you made. The 3 tables were lookup tables (Classification, Industry Coverage, and Geographical Focus) and used the lookup wizard which created the relationships automatically.

Each firm does fall into one Classification but can cover more than one industry or region, so i went ahead and changed Industry Coverage and Geographical Focus to combo boxes with multiple select radio buttons (don't know what they're called). Classification remained as a regular combo box since it is unique to a firm.

All I have to figure out now is how to have the combo boxes and search box filter the data in my data sheet.

 

[jzwp22]

Regarding the combo boxes with radio buttons; I'm not familiar with that type of control. Is that something new in Access 2007 or 2010?

Also, regarding the lookup. Are you saying that you have lookup fields at the table level? If so, that is generally not recommended. See this link for more details on the topic.

 

[nelo]

Regarding the combo boxes with radio buttons; I'm not familiar with that type of control. Is that something new in Access 2007 or 2010?

Also, regarding the lookup. Are you saying that you have lookup fields at the table level? If so, that is generally not recommended. See this link for more details on the topic.

It looks like this cmbo.jpg

and yes i have a lookup field at the table level, it seemed to provide what I needed at the time.

I just need a way of having my search textbox and combo boxes filter the datasheet.

 

[jzwp22]

OK, I'm not familiar with that type of combo box, so I can't say how it would be handled in code in order to dynamically create a query. What version of Access are you using?

 

[vbaInet]

Those are multi-valued fields. I don't use them but you may find that you need to use FieldName.Value In () in the sql string. You're better off without lookup fields.

 

[jzwp22]

Thank vbaINet!

I have not used the multivalue fields (intentionally avoiding them), so I have not done anything in code with them. Perhaps someone who is more familiar with them and how to use them in code could help out.

 

[vbaInet]

They're useful but to me they're not real relational fields. They are a nightmare with DAO recordsets that's what the Recordset2 is for. And I don't think they're compatible with sql server or any of the other sql constructs.

You may find this thread useful:

http://www.access-programmers.co.uk/forums/showthread.php?t=194233&page=2