Results 1 to 7 of 7
  1. #1
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    record selection method- better method than TempVar?

    Hi

    like to run a design concept past to see if I'm approaching it the correct way.

    I have a two table database tbl_Parish and tbl_church, each parish can hold several churches so its a one to many relationship.

    I want a way that on the opening form the user can select a parish and church and limit record selection to that range.

    My though was set up two combo boxes one for church and one for parish and when the opening form is closed move the two values from those boxes into temporary variables.



    Then when I create my queries use those temporary variables as criteria.

    Am I over thinking this? or is this as good a method as any?

    thanks

    Ian

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no, many do it that way.
    when user selects the parish, then the afterupdate event will filter the church combo to that parish
    you need 2 queries for the church combo, 1 to show all , and 1 to filter by whats in the cboParish
    Code:
    sub cboParish_afterupdate
    if isnull(cboParish) then
       cboChurch.rowsource = "qsChurchesAll"
    else
       cboChurch.rowsource = "qsChurchs1Parish"
    endif
    qsChurchs1Parish query would be
    select [church] from table where [parish] = forms!frmName!cboParish

  3. #3
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    many thanks for the clear succinct explanation

    Ian

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the other option is instead of a query to have a rowsource for your cboChurch as something like

    SELECT Church FROM tblChurches WHERE Parish=nz([cboParish],Parish)

    then in the afterupdate event of cbpParish put

    cboChurch.requery

  5. #5
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    thanks for that, never fails to amaze me how may ways there are to do the same thing :-)

    I always try to understand the code ( often fail)

    IN your code

    SELECT Church FROM tblChurches WHERE Parish=nz([cboParish],Parish)

    Is this the nz functions as below if so you have lost me

    cheers

    Ian

    "You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression."

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    yes it is the same nz function

  7. #7
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Off to read up on nz function

    cheers

    Ian

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-29-2015, 10:57 AM
  2. Replies: 1
    Last Post: 12-10-2013, 02:56 PM
  3. What can be the best method?
    By cap.zadi in forum Database Design
    Replies: 2
    Last Post: 03-04-2013, 10:26 PM
  4. Replies: 5
    Last Post: 08-07-2012, 11:31 AM
  5. Which method is better?
    By undrcvr in forum Database Design
    Replies: 3
    Last Post: 05-24-2010, 12:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums