Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2004
    Posts
    30

    Unanswered: Best way to search a form?

    What's the best way to search a form? I have about 300 records that each have specific owners. Currently I'm using an SQL statement, one for each owner, that asks them for the client name and it returns records with that name. But you have to be REAL specific. If you are off any at all it returns no records.

    I also have the filter by forms in place but it doesn't seem user friendly because after you click filter by forms, enter your criteria, you have to right click and then click "apply filter".

    It would be nice to have a drop down for the owner (8 owners total) and another drop box returns the clients that belong to that owner ONLY.

    Is this hard to do?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    LATER: below is the answer to a different question. you need two combos - it's about the same process as below...
    queryOne feeds comboOne.
    queryTwo has criteria ComboOne.
    queryTwo feeds comboTwo.
    queryThree has criteria comboTwo
    form has recordsource queryThree
    comboOne_afterupdate() does comboTwo.requery
    comboTwo_afterupdate() does me.requery to the form

    ...sorry about my reading skills! izy



    you don't describe your tables so i'm going to assume you have tables like:

    tblOwners
    IDowner, autonumber
    nameOwner, string

    tblStuff
    .....blah blah
    IDowner, foreign key
    .....blah blah


    if your form is currently looking directly at tblStuff - fix it. make a query that reproduces the fields you want on your form from tblStuff and change the forms recordsource to that query. lets call is qryStuff.
    make that work first, so you are back where you started.

    now make another query qryOwners: the SQL goes something like
    "SELECT IDowner, nameOwner FROM tblOwners ORDER BY nameOwner;"
    ...but you can use the design grid to build this if you like

    now drop a combo on your form (use the wizard if you like).
    rowsource is qryOwners
    boundcolumn = 0 (the first one, the foreignkey in tblStuff)
    columncount = 2
    columnwidth = 0cm, 3cm (so you see the name, not the autonumber)

    now you should be able to select an owner on your form, but nothing much else happens.

    almost done - now to connect the combo to the query:
    design view qryStuff, click in the criteria box under the IDowner column, click the magic wand thingie in the toolbar: navigate forms, allforms, whateveryourformiscalled, and doubleclick on whateverYourComboIsCalled.

    last step - requery the form when the combo changes:
    private sub whateverYourComboIsCalled_AfterUpdate()
    if isnull(whateverYourComboIsCalled) then exit sub
    me.requery
    end sub


    izy
    Last edited by izyrider; 07-16-04 at 14:02.
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2004
    Posts
    30
    I understand alot of that.

    For example I have tblfeedsoutbound, that consists of the feed, and the owner information (each have a seperate ID, but the owners are duped and do not have their own ID).

    So first thing to do would be to create tblfeedowner and use only values from the owners tbl?

    Then I create the qry in SQL view using what you gave me below?

  4. #4
    Join Date
    Jul 2004
    Posts
    30
    Where does this stuff go?

    private sub whateverYourComboIsCalled_AfterUpdate()
    if isnull(whateverYourComboIsCalled) then exit sub
    me.requery
    end sub

    Almost done

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I assume you have OwnerID as a autonumber and a PK in tblfeedsoutbound...you are done for...I say delete that field. make your TblOwner like:

    TblOwner
    OwnerID (autonum, PK)
    name
    addy
    blah blah
    ....

    Then in tblfeedoutbound add the OwnerID field
    Ryan
    My Blog

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by joshua310
    Where does this stuff go?

    private sub whateverYourComboIsCalled_AfterUpdate()
    if isnull(whateverYourComboIsCalled) then exit sub
    me.requery
    end sub

    Almost done
    You need to enter that in your VBA editor. Not sure where Sorry ( i dont know vba)
    Ryan
    My Blog

  7. #7
    Join Date
    Jul 2004
    Posts
    30
    Here is my SQL Code

    Code:
    SELECT tblFeedOutbound.FeedOutboundID, tblFeedOutbound.Client, tblFeedOutbound.[Control Number], tblFeedOutbound.Product, tblFeedOutbound.Schedule, tblFeedOutbound.[Day Due Out], tblFeedOutbound.Carrier, tblFeedOutbound.Owner, tblFeedOutbound.[File Location]
    FROM tblFeedOutbound
    WHERE (((tblFeedOutbound.Owner)=Forms!frmFeedOutbound!Combo61));
    It's working if I query and type in the exact name (kind of like what I had going before, but when I choose a name from the combo box it's not moving to a record with that name?

    Weird.

  8. #8
    Join Date
    Jul 2004
    Posts
    30
    Yah you are right I have Owner as a field in TblFeeds, there are tons of dup owners.

    I'll make a seperate Owners table and try it.

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Yes, think of categorizing stuff. So if you have a field with feedType add a table called whatever, then just have one field (probably no Key) like FeedType so you have bird feed, pig feed, etc. Then add that field to the table.

    Look at the NorthWind sample.
    Ryan
    My Blog

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    LATER: i see i took too long posting this... anyhow, it's reasonably valid still.


    yes, no, ummmmm.

    the yes part: if your owners repeat, i.e. one owner may have several entries in tblfeedsoutbound, then owners should be in their own table and tblfeedsoutbound should have a foreign key pointing to that table.
    (as it happens this is a sensitive subject: i'm just fighting my way through an inherited mess where five different spellings of the same customer is not unusual - ughhhhh! and that's the risk you take when you are not sufficiently normalised)

    the no part: but you can live with what you've got just to experiment with the combo-feeds-combo-feeds-form idea.

    you can certainly use the design grid to get there (and for your first go at setting criteria, it's fun to try) but it's easier for me to type SQL here to illlustrate what it's supposed to look like. you can switch between grid/SQL views anytime.

    qryOne:
    SELECT DISTINCT owner FROM tblfeedsoutbound;
    feeds comboOne

    qryTwo:
    SELECT customer FROM tblfeedsoutbound WHERE owner ...
    ...if owner is text: WHERE owner = '" & comboOne & "';"
    ...if owner is number: WHERE owner = " & comboOne & ";"
    feeds comboTwo

    qryThree:
    SELECT * FROM tblfeedsoutbound WHERE customer...
    ...if customer is text: WHERE customer= '" & comboTwo & "';"
    ...if customer is number: WHERE customer= " & comboTwo & ";"
    feeds the form

    BE WARNED - your form will not have a decent recordsource until both combos have a selection. there are a zillion ways around this - here is one:
    make a sub to hide footer/ show detail or show footer / hide detail to suit your combo status (if isnull(comboTwo) then ....etc). call this sub in the _afterupdate() of both combos.

    izy
    Last edited by izyrider; 07-16-04 at 14:47.
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    where does the code go?

    design view your form.
    click the yellow outline square thingie on the tool bar (ALT-F11 will do it too)

    on the topleft combo select the object (the combo or whatever)
    on the topright combo select the event (AfterUpdate or whatever)
    ...that's where it goes.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and maybe you also need

    qryTwo:
    SELECT DISTINCT customer


    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Jul 2004
    Posts
    30
    Thanks Izy,

    Here is what I have now.

    TblFeedOutbound (Feed, Client, Owner {from tblowners})

    Then I have another table tblOwners.

    I'll try again.

  14. #14
    Join Date
    Jul 2004
    Posts
    30
    Doesn't look like I have it working correctly.

    The combo box just drops down values from the owner field in the tbl owners and when I click on them it doesn't update the form with the record (or records) that has that owner

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    do you have the combo as criteria in the query feeding the form?
    do you requery the form?

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •