Searching in one collumn but filtering by another

[Tiktok51]

Hello everyone,



i'm quite new to access and i have adifficulty finding out how could i search the table by entering data from lets say Collumn1 but filtering the table and showing only those values that match from the Collumn2.

I will give an example.

ID	Code	Name	Group
1	J5	ball1	1
2	G7	ball2	2
3	I3	ball3	2
4	P12	ball4	3
5	J1	ball5	1

I would enter in a text box G7 press a button to filter my selection and it would give out G7 and I3 lines because they are in the same group, same if i entered J5 it would give Ball1 and Ball5 lines. I need for Access to search in what group the item is by justknowing the code.

Should this filtering be done through a Query or a Macro on a button or how, can any one with more experience give out any suggestions?

[orange]

Search by Group. I don't use macros, so I vote for query. But it would be helpful if you told us a little more about what these fields mean and what you are trying to achieve.

Also, Access has some reserved words that could play havoc with your efforts.

Good luck

[Micron]

Don't know how you intend to pass the Code value to your query, but this should work if you can tweak it to accommodate whatever that method is

SELECT tblGroupCode.ID, tblGroupCode.Code, tblGroupCode.Nme, tblGroupCode.Grp
FROM tblGroupCode WHERE (((tblGroupCode.Grp)=(SELECT [Grp] FROM [tblGroupCode] where {code} = [input])));

This one was a 1st for me. Having [ ] around the word code messes up the post, so I substituted braces { }. You need to swap those for square brackets.

Those are not your real table field names, I hope.

[Tiktok51]

Well this table that i posted is justanexample the long term goal would be to have a table with items that have they'r individual barcode and each item would be in a group so if i enter a barcode of an item it would show me all the equivalent items that i have in the table. So lets say i will have 5 different types of teniss balls from different manufacturers i would enter the barcode of one teniss ball and i would have a filtered list by the group those balls are in. I will also in the future try to make that if i search an item by that barcode i would be able to add new items to the table with the same group that my search was in.

I hope i explained clear what my long end goals are with this.

[June7]

I avoid dynamic parameterized queries, especially using an input popup. Problem with input popups is can't validate the input. Don't search table or query, apply filter to form or report.

Instead of a textbox, have a multi-column combobox on a form that lists the records from that table. Apply filter criteria to form or report by referencing the Group column of whatever Code record is selected. Reference combobox columns by index. Index begins with 0 so if the Group is in column 2, its index is 1.

Combobox RowSource: SELECT Code, Group FROM tablename;

VBA code to open filtered form (or use OpenReport for report):

DoCmd.OpenForm "form name", , , "Group='" & Me.comboxboxName.Column(1) & "'"

Or set the form Filter and FilterOn properties:

Me.Filter = "Group='" & Me.comboboxName.Column(1) & "'"
Me.FilterOn = True

If Group is actually a number type field, remove the apostrophe delimiters.

If you really must have a dynamic parameterized query and avoid VBA (or macro), there is a way to accomplish. Dynamic parameter in query can reference textbox: WHERE code=Forms!formname!textboxname. To assure users enter a value code, I still recommend a combobox. However, query object will not recognize the Column() property. So have textbox (can be not visible) with an expression that references the combobox column: =[comboboxName].Column(1).

@Micron, maybe exclude the [] for purpose of forum post so it doesn't expect a CODE tag. The [] aren't really needed in the SQL.

[Micron]

The [] aren't really needed in the SQL.

They are required to invoke parameter prompts, your favorite type of query.
My thought was that once OP got the prompt and put in the value, the results would replicate the requirement of the post. Once it was determined whether or not the results were valid, he/she had to determine where that value was coming from because it wasn't stated.

[June7]

I meant they aren't needed around the field name code, but yes are needed for the input popup.

OP did mention a textbox for input of code.

[Micron]

Gotcha.
On this we will simply agree to disagree Don't search table or query, apply filter to form or report.
Where I last worked for many years, I saw no sense in loading an entire table with huge numbers of records only to subsequently filter it in a form. If something has changed in the way Access works now, or if I had it wrong all these years, then so be it. Now it's a matter of habit for me to only load the records required. At one time, there were plenty of bugs in filtering reports and forms, which have no doubt been fixed, so that's probably another reason for my habit. Those bugs were documented on AB's website by the way.
Perhaps you know a reason why your way is better when it comes to performance or efficiency so I'm all eyes (can't say ears) if you want to share it. If it's just a matter of convenience (which would include not having to validate user inputs) then don't worry about it.
Thanks.

[June7]

Since I've never had to deal with a dataset that was so large as to impact performance, my viewpoint is probably skewed. And I find it annoying as developer when I want to open a query to examine it's output and the object referenced in parameter is not open. I stick by my advice not to design input popups for users.

[Micron]

I understand your objections. When I had to get new data most of the underlying network (ODBC) tables had one or two million or so records and that's no exaggeration. Sure I've been caught trying to test run a query without the form being open, but I put that down to me being forgetful and would just deal with it by opening the form and taking care of the missing data. Probably the difference in speeds from many years ago was more of a problem as compared to now. After all, I'm going back to Windows 3.1 when drawing on my experiences. But if there is a next time, I will probably try it your way on local data at least. It's just that for the sake of consistency I tried to follow the same habits regardless of where the data was coming from, believing it was faster to not pull down thousands (or more) of records I didn't need only to start applying filters to a data set.
Thanks.

[Tiktok51]

I avoid dynamic parameterized queries, especially using an input popup. Problem with input popups is can't validate the input. Don't search table or query, apply filter to form or report.

Instead of a textbox, have a multi-column combobox on a form that lists the records from that table. Apply filter criteria to form or report by referencing the Group column of whatever Code record is selected. Reference combobox columns by index. Index begins with 0 so if the Group is in column 2, its index is 1.

Combobox RowSource: SELECT Code, Group FROM tablename;

VBA code to open filtered form (or use OpenReport for report):

DoCmd.OpenForm "form name", , , "Group='" & Me.comboxboxName.Column(1) & "'"

Or set the form Filter and FilterOn properties:

Me.Filter = "Group='" & Me.comboboxName.Column(1) & "'"
Me.FilterOn = True

If Group is actually a number type field, remove the apostrophe delimiters.

If you really must have a dynamic parameterized query and avoid VBA (or macro), there is a way to accomplish. Dynamic parameter in query can reference textbox: WHERE code=Forms!formname!textboxname. To assure users enter a value code, I still recommend a combobox. However, query object will not recognize the Column() property. So have textbox (can be not visible) with an expression that references the combobox column: =[comboboxName].Column(1).

@Micron, maybe exclude the [] for purpose of forum post so it doesn't expect a CODE tag. The [] aren't really needed in the SQL.

Thankyou for the help unfortunately i can't use a Combobox it has to be a text box because there will be quite a lot of records in the table. i'm thinking maybe there is a way to store the value in memory and then filter by that value. So i would get the value of the group, store it somewhere and then filter by it?

[June7]

I have comboboxes that list 'quite a lot of records' with no issue. Combobox has 'match and filter as you type functionality' with its AutoExpand property.

However, review http://allenbrowne.com/ser-32.html

Yes, can do a DLookup() to pull the group that matches the given code. DLookup can be expression in textbox or in query or in VBA.