Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: stLinkCriteria

  1. #1
    Join Date
    Nov 2007
    Posts
    30

    Unanswered: stLinkCriteria

    I've setup a form that when a button is clicked the following portion of code is run:

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Prior to running the DoCmd, I've defined stLinkCriteria as:
    stLinkCriteria = "[BnBID]=" & Me.[bankorbrokerID]

    What this does is apply a filter to the new form I open.
    This works fine and the correct filter is applied, my problem reside with the new form not showing ALL the information, I'll explain.

    I have a relationship between two tables that I need to show:

    Table 1:................................Table 2:
    BnBID(Primary Key).------|.......AddressID(Primary Key)
    BankName.....................|---->BnBName
    ...........................................Address

    The form that opens shows the Name of the Bank Selected (from the previous form) and their Address (based off of the relationship)
    What's going wrong is:
    If the Bank selected DOES NOT have an address yet, both the Text Box (that holds the name of the Bank) and the Combo box(that holds the Address) are blank.
    When the Bank HAS an Address associated with it, everything works fine and the Text Box that holds the name, displays the Bank name and the Address is properly populated.


    I hope I made my issue clear, if anyone can help or needs more clarification please feel free to ask. Thank you for all the help!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's just you and me on the site for the moment, and i don't use filter-magic so i don't think i can help.

    BUT, do you have a query feeding the new form I open ?
    if yes - post the SQL of that query.
    if no - ho hum: sorry.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2007
    Posts
    30
    hey Izy,

    I do, I don't have it on this computer though so I'll supply you with that sample once I get home.

    Thanks!

  4. #4
    Join Date
    Nov 2007
    Posts
    30
    Got a snapshot of it for you Izy.

    See attached.
    Attached Thumbnails Attached Thumbnails SQL.JPG  

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Maybe try this:

    stLinkCriteria = "[BnBID]=" & Me.[bankorbrokerID] & " AND [BnBAddress1] IS NOT NULL"

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Nov 2007
    Posts
    30
    Quote Originally Posted by CyberLynx
    Maybe try this:

    stLinkCriteria = "[BnBID]=" & Me.[bankorbrokerID] & " AND [BnBAddress1] IS NOT NULL"

    .
    No dice,
    Same problem

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    are you still stuck here?
    after a good night's sleep and 4,962 e-mails, the answer seems obvious:

    open that query of yours in SQL-view
    notice that it includes something like:
    ...FROM BnBname INNER JOIN BnBAddress ON BnBname.BnBid = BnBaddresses.BnBname
    the INNER is only going to pull records where address exists.

    have a go with:
    ...FROM BnBname LEFT JOIN BnBAddress ON BnBname.BnBid = BnBaddresses.BnBname

    the LEFT will pull everything from the table BnBname with or without existing address

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2007
    Posts
    30
    Quote Originally Posted by izyrider
    are you still stuck here?
    after a good night's sleep and 4,962 e-mails, the answer seems obvious:

    open that query of yours in SQL-view
    notice that it includes something like:
    ...FROM BnBname INNER JOIN BnBAddress ON BnBname.BnBid = BnBaddresses.BnBname
    the INNER is only going to pull records where address exists.

    have a go with:
    ...FROM BnBname LEFT JOIN BnBAddress ON BnBname.BnBid = BnBaddresses.BnBname

    the LEFT will pull everything from the table BnBname with or without existing address

    izy
    Old SQL Code:
    SELECT BnBName.BnBID, BnBName.DTCNumber, BnBName.BnBName, BnBAddresses.BnBAddress1
    FROM BnBName INNER JOIN BnBAddresses ON BnBName.BnBID = BnBAddresses.BnBName;

    New SQL Code:
    SELECT BnBName.BnBID, BnBName.DTCNumber, BnBName.BnBName, BnBAddresses.BnBAddress1
    FROM BnBName LEFT JOIN BnBAddresses ON BnBName.BnBID = BnBAddresses.BnBName;

    Still experiencing the same problem. I'm gonna play around with it a bit more to see if it's something else I'm missing.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    lets go slowly and try to separate query problems from form problems from filter-magic problems.

    run the LEFT query all on it's own:
    do you see what you should see?

    just for fun, add a criteria (!! criterion) in query-builder to the LEFT query
    ...do you still see what you should see.

    so is the query OK ?????

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Nov 2007
    Posts
    30
    Quote Originally Posted by izyrider
    lets go slowly and try to separate query problems from form problems from filter-magic problems.

    run the LEFT query all on it's own:
    do you see what you should see?

    just for fun, add a criteria (!! criterion) in query-builder to the LEFT query
    ...do you still see what you should see.

    so is the query OK ?????

    izy
    The Query is working as intended with the LEFT command in, I need to head out for an hour, I'll test some criteria when I get back.

    EDIT: Tested in the Address criteria, "Is Null" and "Is Not Null" and we are getting the correct outputs. I'll test some more when I get back from lunch. brb! Thanks btw for all the help Izy.
    Last edited by Rakh; 12-07-07 at 14:12.

  11. #11
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Is the control source of BnBID in Table 1 or Table 2?

    You also appear to be linking an ID field with a name field (BnBname.BnBid = BnBaddresses.BnBname).
    Last edited by rogue; 12-07-07 at 14:23.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    enjoy lunch!
    for me - it's TV time and a bottle of red wine.

    but progress!
    if the query can fly on it's own - great.

    next step is to feed the query to a form.
    shouldn't be an issue EXCEPT most times edit forms based on JOINs either don't work at all (recordset is not updateable) or behave counter-intuitively.

    bon appetit!

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    rogue - yes:
    are we looking here at another manifestation of the dreaded table-lookup wizard?

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Nov 2007
    Posts
    30
    Quote Originally Posted by rogue
    Is the control source of BnBID in Table 1 or Table 2?

    You also appear to be linking an ID field with a name field (BnBname.BnBid = BnBaddresses.BnBname).

    BnBID is in Table 1
    BnBName in Table 2 is a Number fields.
    Also, reason why I added a field called BnBName in Table 2 is because one BnBName might have multiple addresses.

  15. #15
    Join Date
    Nov 2007
    Posts
    30
    Quote Originally Posted by izyrider
    rogue - yes:
    are we looking here at another manifestation of the dreaded table-lookup wizard?

    izy

    Haha, no sir, I learned my lesson with that already!!

Posting Permissions

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