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 = txtSearchCriter ia.
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 txtSearchCriter ia.
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=tblBooking s

 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.Produc tID, Products.Handle , Categories.Cate goryName, [Widths] & " " & [Series] & " " & [Rating] & " " & [Diameters] & " - " & [Manufacturer] & " " & [Notes] AS Tyre, Products.UnitCo st, Products.Trade, Products.Retail , Products.StockL evel
FROM Categories INNER JOIN Products ON Categories.Cate goryID = Products.Catego ryID
ORDER BY Products.Rating DESC , Products.Manufa cturer;


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!