By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,586 Members | 623 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,586 IT Pros & Developers. It's quick & easy.

Search code...how to filter using code/SQL

stonward
100+
P: 145
Hi again People.

This Forum has been a serious help for me...I'm sure you know how it is...you get pretty good at handling Access when wham! you come across something you just can't figure - and if you're in a hurry at all....!

I have a Main Form, based upon my main table (products). This main form has a subform (based upon a query that is based primarily upon the Products table, plus a few others to grab hold of related data (customer names rather than ID etc)).

One of my fields of the products table is called HANDLE and is, essentially, a made up field to enable simpler searching - it's an amalgam of several fields of the table.

When I click SEARCH, i want the subform to show the results based on the HANDLE the user taps into an (unbound) textbox. This works okay using a macro to run a filter, but I need something, um, firmer and more detailed and controllable.

I know there is a way using SQL entered via a variable in code...is there some 'generic' code someone can show me as to how this may be done.

Sorry for the complexity, but i want to give you as much info as possible.

Thanks.

Stonward the Troubled.
Sep 3 '07 #1
Share this Question
Share on Google+
27 Replies


ADezii
Expert 5K+
P: 8,627
Hi again People.

This Forum has been a serious help for me...I'm sure you know how it is...you get pretty good at handling Access when wham! you come across something you just can't figure - and if you're in a hurry at all....!

I have a Main Form, based upon my main table (products). This main form has a subform (based upon a query that is based primarily upon the Products table, plus a few others to grab hold of related data (customer names rather than ID etc)).

One of my fields of the products table is called HANDLE and is, essentially, a made up field to enable simpler searching - it's an amalgam of several fields of the table.

When I click SEARCH, i want the subform to show the results based on the HANDLE the user taps into an (unbound) textbox. This works okay using a macro to run a filter, but I need something, um, firmer and more detailed and controllable.

I know there is a way using SQL entered via a variable in code...is there some 'generic' code someone can show me as to how this may be done.

Sorry for the complexity, but i want to give you as much info as possible.

Thanks.

Stonward the Troubled.
I don't have that much to go, on so first, a couple of assumptions:
  1. [HANDLE] Field - Field in SubForm cosisting of an amalgam of various values.
  2. Main Form Name = frmMain.
  3. SubForm Control Name = subfSubForm.
  4. Search Button Name = cmdSearch.
  5. Text Box Name = txtSearchCriteria.
  6. RecordSource for the SubForm = qryRecordSource.
  7. Primary Key Field on Main Form = [PrimaryKey].
  8. Foreign Key Field on SubForm = [ForeignKey].
  9. One Method to use is to modify the RecordSource of the SubForm to reflect the proper Foreign Key Fields, as well as a Partial Match for Values in the [HANDLE] Field, as indicated by entries in txtSearchCriteria.
  10. Hope this makes sense.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdSearch_Click()
    2. If Not IsNull(Me![txtSearchCriteria]) Then
    3.   Dim MySQL As String
    4.   MySQL = "Select * From qryRecordSource Where [ForeignKey] = " & Me![PrimaryKey] & " And " & _
    5.           "[HANDLE] Like '*" & Me![txtSearchCriteria] & "*'"
    6.   Forms!MainForm!subfSubForm.Form.RecordSource = MySQL
    7. End If
    End Sub
Sep 3 '07 #2

stonward
100+
P: 145
That's what I'm talking about! Thanks ADezii!
Of course! Change the recordsource - I'd never have discovered how to get that syntax right!

Gonna try it now.

Thanx again.

Stonward the Impressed.
Sep 4 '07 #3

stonward
100+
P: 145
Hi. Trying out (above) code and getting teething troubles. The Foreign Key is the foreign of the recordsource of the subform? is that right? The code doesn't appear to 'see' the query that the subform is based on...
Sep 4 '07 #4

ADezii
Expert 5K+
P: 8,627
Hi. Trying out (above) code and getting teething troubles. The Foreign Key is the foreign of the recordsource of the subform? is that right? The code doesn't appear to 'see' the query that the subform is based on...
The Foreign Key is the Field that relates back to the Primary Key in the Main Form and is on the MANY side of the Relationship. If the code is not 'seeing' the Query, make sure:
  1. You have the Query Name spelled correctly.
  2. The Query in question, is in fact the RecordSource for the SubForm.
  3. The Foreign Key Field exists in the Query.
  4. Ledt me know how you make out.
Sep 4 '07 #5

stonward
100+
P: 145
No, sorry Adezii - i can't seem to get it running - and I've tried MANY permutations!

My Main form (FrmMain) is based upon my Products table. That table has a Primary key (of course). My subform is based upon a query - that query is also based upon the Products table. The subform is linked to the main via the Handle field. I think i may have a fundamental error relating to key use?

My products are tyres...the handle has a format like 195 55 15. I added the Handle field because i didn't know a way of searching using two separate fields, i.e., size (195x15) and series 15.

Please let me know what othe info i can give you to help. This is a major sticking point for both my system and my learning!
Sep 5 '07 #6

stonward
100+
P: 145
It has occurred to me that subforms are supposed to have a one:many relationship with their main form? Is that correct? if so, how can i base the forms upon what i need to base them on whilst obeying those rules?!

Stonward the Perplexed
Sep 5 '07 #7

ADezii
Expert 5K+
P: 8,627
It has occurred to me that subforms are supposed to have a one:many relationship with their main form? Is that correct? if so, how can i base the forms upon what i need to base them on whilst obeying those rules?!

Stonward the Perplexed
Please Post all relevant information such as: Table(s), Fields, Data types of the Fields, sample data, brief description of exactly what you are trying to accomplish, etc.
Sep 5 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Stoneward, I'm a little confused about what you are doing. Can you give the metadata of the table products and the full SQL of the query behind the subform.

Here is an example of how to post table MetaData :
Table Name=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Sep 5 '07 #9

stonward
100+
P: 145
Thanks Guys, I really appreciate this:

(The product is tyres)
Products Table (tblProducts)
ProductID; AutoNum; PK
Handle; Text
CategoryID; Number; FK (Categories Table PK)
Series; Text; FK (Series Table PK)
Size; Text; FK (Size Table PK)
Rating; Text; FK (etc)
Manufacturer; Text; FK
Notes; Text
UnitCost; Currency
Trade; Currency
Retail; Currency
Diameters; Number
Widths; Number
Stocklevel; Number

The CATEGORIES table tells me what type of tyre it is, Car, Van, 4X4 etc...
it's included in the (following) query(ies) so we get a tyre type and not just an ID number...

Subform Query ("Subform2", subform for frmMain)

SELECT Products.ProductID, Products.Handle, Categories.CategoryName, [Widths] & " " & [Series] & " " & [Rating] & " " & [Diameters] & " - " & [Manufacturer] & " " & [Notes] AS Tyre, Products.UnitCost, Products.Trade, Products.Retail, Products.StockLevel
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
ORDER BY Products.Rating DESC , Products.Manufacturer;


I need to have the User type in what they recognise, so I have an input mask set to allow input in this format; 195 55 15, or 185 65 14.

IF POSSIBLE, i need to add RATING to this to get 195 55 HR 15....

So, when a user taps in the handle, I need the subform to show all tyres that match that size/series (and, hopefully) rating.

Linking the subform to the main via the HANDLE field works, but the user must tap in every character- there is no leeway at present, and it doesn't always work correctly. For example, if one presses Return after entering handle everything is okay, but if he clicks the SEARCH button, the subform disappears!!

Help?

Thanks again.

StonwardR

PS: If you need anything else, please don't hesitate to ask...I've gotta get this right!
Sep 6 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Why haven't you created a link based on ProductID?
Sep 6 '07 #11

stonward
100+
P: 145
Yes, I see what you mean - and usually subforms have a 'many' relationship with the main form I think. But I could see no way of searching the fields i need to search whilst involving productID...
Sep 7 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes, I see what you mean - and usually subforms have a 'many' relationship with the main form I think. But I could see no way of searching the fields i need to search whilst involving productID...
Now I'm totally confused. Your main form is based on the products table and the subform query contains productID. Maybe you need to start again and explain your problem because at this stage I'm totally lost. Prehaps if you gave the filter code you are currently using this would make more sense.
Sep 7 '07 #13

stonward
100+
P: 145
Sorry, Mmcarthy! Okay, I have frmMain based upon the Products table. The subform (unfortunately known as Subform2) - where I want to show my search results - is based on a query - that query is itself based on the Products table, but with the addition of the Categories table so the user can 'see' the tyre type (not just an ID number).

My search code just opens a macro which applies a filter:

qry_Tyres, [Forms]![frmMain]![Handle], subform2

I can see I have a fundamental problem related to the table/keys, but can see no way out!

Thanks for your help. Bear with me and all will become clear!

StonwardR
Sep 7 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
OK :)

Lets for get the code for a moment and go to plain logic. You are filtering the subform to display all records in the query where the handle is the same as the one on the main form. If that is the case your filter should be working.

The only other way to do this is to set the master child relationship between the main form and the subform to be based on Handle. Have you tried this?

BTW, if I've got the logic wrong please let me know.

Mary
Sep 7 '07 #15

stonward
100+
P: 145
Mary, that's spot on!

I've already got the two forms linked via Handle, and this was working just fine...until the User tried it!

After update of the textbox where the Handle is entered, the focus moves to the 'search' button. If one presses Return, everything is fine...but if he clicks the Search button, the subform disappears!

Also, with this method, every character must be entered into the textbox...there is no scope for, say, tapping in just the first few numbers, or using wildcards, so I just thought that there is probably a better way of searching records using code?

I'm relieved that you can see what I'm trying to do...I was beginning to think i was completely adrift! :-)

Roy.
Sep 7 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary, that's spot on!

I've already got the two forms linked via Handle, and this was working just fine...until the User tried it!

After update of the textbox where the Handle is entered, the focus moves to the 'search' button. If one presses Return, everything is fine...but if he clicks the Search button, the subform disappears!

Also, with this method, every character must be entered into the textbox...there is no scope for, say, tapping in just the first few numbers, or using wildcards, so I just thought that there is probably a better way of searching records using code?

I'm relieved that you can see what I'm trying to do...I was beginning to think i was completely adrift! :-)

Roy.
OK Roy,

This is why the primary key is normally used to link the forms as it is a static value that doesn't change.

So you actually want the subform to return a list of records based on the search?

If so then the macro filter will cause problems.

What you need to do is apply a filter using VBA code instead. Based on the click event of the search button - Something like ...

Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3.     ' This assumes Handle is a text field
  4.     strFilter = "[Handle] LIKE '" & Me.Handle & "*'"
  5.     Me.Subform2.Form.Filter = strFilter
  6.     Me.Subform2.Form.FilterOn = True
  7.  
  8.  
Sep 7 '07 #17

stonward
100+
P: 145
Wow, Mary!!

That worked straight off the bat...copy and paste!!

Thanks a million - that's a real life-safer. Will work out how it works now.

Thanks again.

(more to follow) :)

Later,

Roy the Impressed.
Sep 7 '07 #18

stonward
100+
P: 145
MMCCarthy...

Just one thing: if i wanted to add further parameters to the search, how (and can I) add them to the strFilter string?

So, if I only wanted to show those products with a stocklevel greater than "0", what would be the syntax for adding that to the string?

StonwardRoy
Sep 7 '07 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
MMCCarthy...

Just one thing: if i wanted to add further parameters to the search, how (and can I) add them to the strFilter string?

So, if I only wanted to show those products with a stocklevel greater than "0", what would be the syntax for adding that to the string?

StonwardRoy
strFilter = "[Handle] LIKE '" & Me.Handle & "*' AND [stocklevel]>0"
Sep 7 '07 #20

stonward
100+
P: 145
Mary, that is just SO cool!

Thank You!

Hope to speak with you again.

And thanks to all others who helped!

TTFN

StonwardR
Sep 7 '07 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
You're welcome Roy. If you have any questions about what I've done just let me know.

Mary
Sep 7 '07 #22

stonward
100+
P: 145
Thanks, Mary.

I do have one question regarding the syntax....what is the relevance of the single quote in the string?

strFilter = "[Handle] LIKE '" & Me.Handle & "*' AND [stocklevel]>0"

StonwardR.
Sep 10 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks, Mary.

I do have one question regarding the syntax....what is the relevance of the single quote in the string?

strFilter = "[Handle] LIKE '" & Me.Handle & "*' AND [stocklevel]>0"

StonwardR.
If the value is a date you surround it with #
If the value is text you surround it with quotes, single quotes are used when part of a larger quoted statement.
Sep 10 '07 #24

stonward
100+
P: 145
I see,

The code includes the wildcard character....but wildcards don't work in the input...It's not really important, but can you see a reason why that might be?

Stonward
Sep 11 '07 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
I see,

The code includes the wildcard character....but wildcards don't work in the input...It's not really important, but can you see a reason why that might be?

Stonward
Have a look at this tutorial and see if it helps.

Ansi standards in string comparisons
Sep 11 '07 #26

stonward
100+
P: 145
Thanx, Mary.

I'm determined to crack this stuff. I've been working with Access for some years now, but appear to have reached a bit of a brick wall as far as further advancement goes...the books no longer help!

You've been a huge help. Thankyou again.


Roy.
Sep 11 '07 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanx, Mary.

I'm determined to crack this stuff. I've been working with Access for some years now, but appear to have reached a bit of a brick wall as far as further advancement goes...the books no longer help!

You've been a huge help. Thankyou again.


Roy.
I remember that stage Roy. The only way forward from here is to tackle each problem as it comes up and search and explore possible solutions. That part never really stops. The more you learn, the more you realise Access is capable of, the further you push the envelope. It's fun :)

We are always around to help.
Sep 11 '07 #28

Post your reply

Sign in to post your reply or Sign up for a free account.