Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Control Query Results from Form (2000)

    The attached zip of two pictures taken might help explain a bit more.

    I have a form with a tab control. In the tab control I have two fields at present, which will have values to them. The first being a maximum price, the second being minimum no. of bedrooms. To the right hand side of these I have a sub form, which is based upon a query (second screen shot). I want to display a list of the matching items in the table depending on the values in the fields for the parent form.

    How can I do this?
    Attached Files Attached Files
    Best Regards,

    Luke

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    The query will not recognize the parent property of the fields. You'll have to use the full reference of the fields.
    In the query use as criteria something like :
    <= Forms!ParentsFormName!MaxPrice
    and
    >=Forms!ParentsFormName!MinBedrooms
    replace ParentsFormName with the appropriate name of your form.

    In the after update events of the two fields, requery the subform.
    Me.SubformControlName.Form.Requery
    Replace SubformControlName with the name of the Subform control. This is not necessary the same name as the subform.
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    thanks, that seems to work well.

    The next part of my narrowing down of the query is:

    I have the 'Area' field which is linked to a separate table called tblPropertiesArea with the table structure as follows:

    Area AreaMailing
    Clarendon Park WantCP
    Evington WantEvin

    The purchasers form/table has yes/no fields for each area, these are labelled to correspond to the 'AreaMailing' field value for each area but are present in the tblPurchsers table as that name. How can I do this in the query to look at a yes/no option.

    For example, based on the above design it needs to look at the area the property is, look up its AreaMailing field value. This is then the same name as the yes/no field in tblpurchsers, so needs to see if there is a tick or not in the box.

    Any ideas?
    Best Regards,

    Luke

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    I don't understand what you mean and don't know the structure of the db.
    Is it possible to attach a stripped version of your mdb so we can see the structure and the relationships ? See <post#=401925>post 401925</post#> for instructions.
    Francois

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Attaced is a stripped down version.

    You need to open frmPurchaser1, you will find myself in there. There is four test properties.

    I have got the min bedrooms and max price to work.

    Now need to get areas to work. The idea to have yes/no boxes, make it easier for the end user to select, etc, so I would like to stick with this method if possible? Then the same principle can work for the property type.
    Attached Files Attached Files
    Best Regards,

    Luke

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Are you going to put 35 checkboxes on the form, one for each area ? Do I understand that correctly ?
    Francois

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    yes about right.
    Best Regards,

    Luke

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    I would not do this. Use a Listbox with multiple selection. See Hans' code in <post#=382908>post 382908</post#> for how to build a where string and modify the code to change the recordsource of the subform.
    Francois

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Luke,

    Here you have you're mdb, reworked like I meaned in my previous answer.
    Select one or more area's in the listbox and click on the command button Filter on area.
    Attached Files Attached Files
    Francois

  10. #10
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Francois,

    Thank you for the improved functions. However we need to be able to have the ability to store each users area and type preference so that when a new property comes ont the market we can match it to them. In the example attached it does not store the values?
    Best Regards,

    Luke

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Luke,

    You are right when you say that in the previous sample, you can't store the areas.
    Here another sample where you can store them.
    A little explanation :
    Name all your checkboxes Want1, Want2, Want3,.... until Want35
    Put the name of the area in the Tag property of the corresponding checkbox
    In each afterupdate event of the 35 checkboxes put FilterOnArea
    Copy the sub FilterOnArea to your form.
    See attached sample with two checkboxes
    Attached Files Attached Files
    Francois

  12. #12
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Francois,

    that is fantastic, thank you.

    Upon implementing it, after selecting the Want1 box (so there's a tick in the field), it renames the 'House' field in the subform to the name of the area. Is there a way to not get it to do this. Trying to work my way through the code and no been successful yet....
    Best Regards,

    Luke

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Sorry for that mistake.
    In the sub FilterOnArea replace the line :
    strSQL = "SELECT [tblProperty]![area] AS House, tblProperty.PRICE, " _
    by
    strSQL = "SELECT [propnumr] & ' ' & [streetname] & ', ' & [tblProperty]![area] AS House, tblProperty.PRICE, " _
    Attached Files Attached Files
    Francois

  14. #14
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    Francois,

    Thanks for the revised code, works a treat.

    It is much appreciated for your help.
    Best Regards,

    Luke

  15. #15
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Query Results from Form (2000)

    I was trying & " " &, but obviously a & ' & works on this part then? - Any particuarly reason why it is is a ' and not "?

    Second, is there a way to organise the results of the requery by price order?
    Best Regards,

    Luke

Page 1 of 2 12 LastLast

Posting Permissions

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