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

[stonward]

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.

[ADezii]

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.
    

    Private Sub cmdSearch_Click()
    If Not IsNull(Me![txtSearchCriteria]) Then
      Dim MySQL As String
      MySQL = "Select * From qryRecordSource Where [ForeignKey] = " & Me![PrimaryKey] & " And " & _
              "[HANDLE] Like '*" & Me![txtSearchCriteria] & "*'"
      Forms!MainForm!subfSubForm.Form.RecordSource = MySQL
    End If

    End Sub

[stonward]

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.

[stonward]

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...

[ADezii]

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.

[stonward]

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!

[stonward]

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

[ADezii]

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.

[MMcCarthy]

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

 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[stonward]

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!

[MMcCarthy]

Why haven't you created a link based on ProductID?

[stonward]

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...

[MMcCarthy]

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.

[stonward]

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

[MMcCarthy]

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

[stonward]

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.

[MMcCarthy]

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 ...

Dim strFilter As String
 
    ' This assumes Handle is a text field
    strFilter = "[Handle] LIKE '" & Me.Handle & "*'"
    Me.Subform2.Form.Filter = strFilter
    Me.Subform2.Form.FilterOn = True
 
 

[stonward]

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.

[stonward]

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

[MMcCarthy]

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"

[stonward]

Mary, that is just SO cool!

Thank You!

Hope to speak with you again.

And thanks to all others who helped!

TTFN

StonwardR

[MMcCarthy]

You're welcome Roy. If you have any questions about what I've done just let me know.

Mary

[stonward]

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.

[MMcCarthy]

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.

[stonward]

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

[MMcCarthy]

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

[stonward]

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.

[MMcCarthy]

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.