Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Lookup Not Working Correctly? (a2k (9.0.3821) SR-1 Jet 4.0)

    Combo Lookup Not Working Correctly?
    a2k (9.0.3821) SR-1 Jet 4.0

    I have the following in a Form Combo Box Row Source:

    SELECT tbl_RPS_Owner.strLocStreetName as [Street Name], tbl_RPS_Owner.strLocStreetNo as [Number], tbl_RPS_Owner.strLocOwnerName as [Owner Name], tbl_RPS_Owner.strPrint_Key as [SBL Number], tbl_RPS_Owner.strPropClass as [PC], tbl_RPS_Owner.strMailStreetAddress AS [Mailing Address], tbl_RPS_Owner.strMailCityStateZip AS [Mailing City, St, Zip], tbl_RPS_Owner.dtmCCDateTime AS [Create], tbl_RPS_Owner.strLocStreetNameNo AS [Loaction Address], tbl_RPS_Owner.ysnWaterFront AS [WP], tbl_RPS_Owner.ysnHistorical AS [HP] FROM tbl_RPS_Owner ORDER BY strLocStreetNameNo; UNION select tbl_RPS_Owner_Non_RPS.strLocStreetName, tbl_RPS_Owner_Non_RPS.strLocStreetNo, tbl_RPS_Owner_Non_RPS.strLocOwnerName, tbl_RPS_Owner_Non_RPS.strPrint_Key, tbl_RPS_Owner_Non_RPS.strPropClass, tbl_RPS_Owner_Non_RPS.strMailStreetAddress AS [Mailing Address], tbl_RPS_Owner_Non_RPS.strMailCityStateZip AS [Mailing City, St, Zip], tbl_RPS_Owner_Non_RPS.dtmCCDateTime AS [Create], tbl_RPS_Owner_Non_RPS.strLocStreetNameNo AS [Loaction Address], tbl_RPS_Owner_Non_RPS.ysnWaterFront AS [WP], tbl_RPS_Owner_Non_RPS.ysnHistorical AS [HP] FROM tbl_RPS_Owner_Non_RPS;

    Other Combo Box Properties are:

    Coulmn Count: 11
    Column Heads: Yes
    Column Widths: 2";0.9";2";1.5";0.4";2";2";0.8";1.5";0.5";0.5"
    Bound Column: 1
    List Rows: 30
    List Width: 14.1

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combo Lookup Not Working Correctly? (a2k (9.0.3821) SR-1 Jet 4.0)

    John,

    When you do a Union query, you must have the same number of fields selected (and of the same type) for each section of the query. So, if you only selected 9 fields from the first table, then did a UNION with another table selecting 11 fields, I believe only the first 9 fields (of the 11) will be taken. Just add 2 expressions to the 1st part of the query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Not Working Correctly? (a2k (9.0.3821) SR-1 Jet 4.0)

    Hi Mark

    I formatted the original sql statement and it does have 11 fields in the first part oof the query and all fields have matching data types and the sql does work in query builder.

    What am iI missing?

    Any other suggestions?

    Thanks, John


    SELECT

    tbl_RPS_Owner.strLocStreetName as [Street Name],
    tbl_RPS_Owner.strLocStreetNo as [Number],
    tbl_RPS_Owner.strLocOwnerName as [Owner Name],
    tbl_RPS_Owner.strPrint_Key as [SBL Number],
    tbl_RPS_Owner.strPropClass as [PC],
    tbl_RPS_Owner.strMailStreetAddress AS [Mailing Address],
    tbl_RPS_Owner.strMailCityStateZip AS [Mailing City, St, Zip],
    tbl_RPS_Owner.dtmCCDateTime AS [Create],
    tbl_RPS_Owner.strLocStreetNameNo AS [Loaction Address],
    tbl_RPS_Owner.ysnWaterFront AS [WP],
    tbl_RPS_Owner.ysnHistorical AS [HP]

    FROM tbl_RPS_Owner ORDER BY strLocStreetNameNo;
    UNION
    select

    tbl_RPS_Owner_Non_RPS.strLocStreetName,
    tbl_RPS_Owner_Non_RPS.strLocStreetNo,
    tbl_RPS_Owner_Non_RPS.strLocOwnerName,
    tbl_RPS_Owner_Non_RPS.strPrint_Key,
    tbl_RPS_Owner_Non_RPS.strPropClass,
    tbl_RPS_Owner_Non_RPS.strMailStreetAddress AS [Mailing Address], tbl_RPS_Owner_Non_RPS.strMailCityStateZip AS [Mailing City, St, Zip], tbl_RPS_Owner_Non_RPS.dtmCCDateTime AS [Create], tbl_RPS_Owner_Non_RPS.strLocStreetNameNo AS [Loaction Address], tbl_RPS_Owner_Non_RPS.ysnWaterFront AS [WP],
    tbl_RPS_Owner_Non_RPS.ysnHistorical AS [HP]

    FROM tbl_RPS_Owner_Non_RPS;

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Not Working Correctly? (a2k (9.0.3821) SR-1 Jet 4.0)

    Hi Mark

    After further review I see I am building the row source in code.

    Moral of the storie, if building row source in code, may want to clear row source property as a clue with future maintenance of the app.

    Sorry for my confusion

    John

Posting Permissions

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