Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Combo box help...

    Hi!

    I have a form with a combo field inside. In my Row Source of the Combo box I have..

    SELECT TblSiteCodeDisposers.DisposalLocation FROM TblSiteCodeDisposers WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode])) AND ORDER BY TblSiteCodeDisposers.PriorityCode;

    I want to add this piece of the code into the above code..

    tblsitecodeDisposers.DisposerCheck = False

    In other words I want the combo box not to list any records with a check in my field DisposerCheck.


    I tried doing this...

    SELECT TblSiteCodeDisposers.DisposalLocation FROM TblSiteCodeDisposers WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode])) AND (tblSiteCodeDisposer.DisposerCheck = False) ORDER BY TblSiteCodeDisposers.PriorityCode;

    But that didnt work...

    any ideas ?

    thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Combo box help...

    Originally posted by TonyT
    Hi!

    I have a form with a combo field inside. In my Row Source of the Combo box I have..

    SELECT TblSiteCodeDisposers.DisposalLocation FROM TblSiteCodeDisposers WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode])) AND ORDER BY TblSiteCodeDisposers.PriorityCode;

    I want to add this piece of the code into the above code..

    tblsitecodeDisposers.DisposerCheck = False

    In other words I want the combo box not to list any records with a check in my field DisposerCheck.


    I tried doing this...

    SELECT TblSiteCodeDisposers.DisposalLocation FROM TblSiteCodeDisposers WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode])) AND (tblSiteCodeDisposer.DisposerCheck = False) ORDER BY TblSiteCodeDisposers.PriorityCode;

    But that didnt work...

    any ideas ?

    thanks
    Check your parens ...

    WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode]) AND (tblSiteCodeDisposer.DisposerCheck = False))

  3. #3
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Combo box help...

    Originally posted by TonyT
    Hi!

    I have a form with a combo field inside. In my Row Source of the Combo box I have..

    SELECT TblSiteCodeDisposers.DisposalLocation FROM TblSiteCodeDisposers WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode])) AND ORDER BY TblSiteCodeDisposers.PriorityCode;

    I want to add this piece of the code into the above code..

    tblsitecodeDisposers.DisposerCheck = False

    In other words I want the combo box not to list any records with a check in my field DisposerCheck.


    I tried doing this...

    SELECT TblSiteCodeDisposers.DisposalLocation FROM TblSiteCodeDisposers WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode])) AND (tblSiteCodeDisposer.DisposerCheck = False) ORDER BY TblSiteCodeDisposers.PriorityCode;

    But that didnt work...

    any ideas ?

    thanks
    Is your DisposerCheck field in TblSiteCodeDisposers?... and if so, is it a boolean field (yes/no type)?... or is it a control on your form? ... a checkbox control?

    and ... when you say that it didn't work... can you clarify?... Did you get an error of some kind? ...and if so, what did it say?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Combo box help...

    Originally posted by Trudi
    Is your DisposerCheck field in TblSiteCodeDisposers?... and if so, is it a boolean field (yes/no type)?... or is it a control on your form? ... a checkbox control?

    and ... when you say that it didn't work... can you clarify?... Did you get an error of some kind? ...and if so, what did it say?
    Trudi,

    Look at his WHERE clause and where he placed the additional filter ... I think my mod should fix it.

    That is of course assuming that the filter is valid ...

  5. #5
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Combo box help...

    Originally posted by M Owen
    Trudi,

    Look at his WHERE clause and where he placed the additional filter ... I think my mod should fix it.

    That is of course assuming that the filter is valid ...
    Let's hope...

    The way he had it didn't have a syntax error that i can see... If there'd been an OR in there it could have just changed the order in which the different expressions had priority... Since both logical tests are AND's doesn't that mean it wouldn't make a difference in this case?

    I mean... if I want to filter the recordset on three True values overall...

    (True and True) and True is the same as
    (True and True and True), don't ya think?

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    M Owen,

    I tried the code that you suggested.. and I'm coming up with errors..

    The error that I came up with was.. The microsoft Jet engine cannot find the input table or query..... (the code).. Make sure it exists and the name is spelled correctly..

    I double checked the names and tables and they were there... There was missing a 's' ... AND (TblSiteCodeDisposers.DisposerCheck = False)) but I fixed it and I am still getting the same error...

    WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers. SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode
    ]) AND (TblSiteCodeDisposers.DisposerCheck = False)) ORDER BY TblSiteCodeDisposers.PriorityCode;

    Trudi,

    Yes is there is a Disposercheck field in tblsitecodedisposers.. and it is a (yes/no type)..

    In the original post with the original code.. when i ran the code on the form.. nothing happened.. my combo boxed displayed all info. check or unchecked..

    Thanks to the both of you

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Combo box help...

    Originally posted by Trudi
    Let's hope...

    The way he had it didn't have a syntax error that i can see... If there'd been an OR in there it could have just changed the order in which the different expressions had priority... Since both logical tests are AND's doesn't that mean it wouldn't make a difference in this case?

    I mean... if I want to filter the recordset on three True values overall...

    (True and True) and True is the same as
    (True and True and True), don't ya think?
    Actully there was ... Precedence rules ... If you have ((X) AND (Y)) AND (Z) How does it evaluate using a WHERE clause??? The last I knew the filtering needed to be enclosed within parens for the WHERE clause ...

    And yes, generically the asociativity property applies ... Except I thought there was a syntax error here ...

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by TonyT
    M Owen,

    I tried the code that you suggested.. and I'm coming up with errors..

    The error that I came up with was.. The microsoft Jet engine cannot find the input table or query..... (the code).. Make sure it exists and the name is spelled correctly..

    I double checked the names and tables and they were there... There was missing a 's' ... AND (TblSiteCodeDisposers.DisposerCheck = False)) but I fixed it and I am still getting the same error...

    WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers. SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode
    ]) AND (TblSiteCodeDisposers.DisposerCheck = False)) ORDER BY TblSiteCodeDisposers.PriorityCode;

    Trudi,

    Yes is there is a Disposercheck field in tblsitecodedisposers.. and it is a (yes/no type)..

    In the original post with the original code.. when i ran the code on the form.. nothing happened.. my combo boxed displayed all info. check or unchecked..

    Thanks to the both of you
    Have you run your query thru the QBE to check the result set? Try that 1st ... See if you get the wrong results there also ... If so, you need to rethink your query.

  9. #9
    Join Date
    Jul 2003
    Posts
    292
    I'm getting confused...

    Ok I was given this database to correct from a pervious owner..

    Let me start from the beginning..

    The user has many tables and fields link together throughout the database..

    THe database basically is a way for the user to update, verify and schedule waste from different sites..

    Over the years sites move or change.. so user do not need to see these sites.. ( in the combo boxes ) anymore when they add or update a waste.

    So it needs to be removed off the combo box so that the user does not pick sites that no longer exists..

    When the user opens up the form.. he has a button.. called.. Sites... When he presses on the button it list all Sites.. I've added a checkbox to the form .. so that when the site is no longer needed to put a check in the box and the site will be removed..

    On the scheduling form.. there is a field called sites.. sitecode... disposal location...

    Site, sitecode, and disposal are all combos..

    disposal is linked to site and sitecode..

    ie.. This site with this sitecode has to match this disposal..

    What I want is.. once the user selects the site to be removed.. then the user will not have the option of picking that site again..


    Kinda hard to understand.. if you need to see the .mdb let me know.. i'll change data so that you'll see what i mean..

    thanks so much

  10. #10
    Join Date
    Jul 2003
    Posts
    292
    I believe that the way M. Owen had the code was correct.. but.. since the record is not there.. i'm getting this error...

    The microsoft Jet database engine can't find a record in the table 'TblSiteCodeDisposers' with the key matching field(s) 'Site, SiteCode,DisposalLocation'.

    any ideas ?

    thanks

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by TonyT
    I'm getting confused...

    Ok I was given this database to correct from a pervious owner..

    Let me start from the beginning..

    The user has many tables and fields link together throughout the database..

    THe database basically is a way for the user to update, verify and schedule waste from different sites..

    Over the years sites move or change.. so user do not need to see these sites.. ( in the combo boxes ) anymore when they add or update a waste.

    So it needs to be removed off the combo box so that the user does not pick sites that no longer exists..

    When the user opens up the form.. he has a button.. called.. Sites... When he presses on the button it list all Sites.. I've added a checkbox to the form .. so that when the site is no longer needed to put a check in the box and the site will be removed..

    On the scheduling form.. there is a field called sites.. sitecode... disposal location...

    Site, sitecode, and disposal are all combos..

    disposal is linked to site and sitecode..

    ie.. This site with this sitecode has to match this disposal..

    What I want is.. once the user selects the site to be removed.. then the user will not have the option of picking that site again..


    Kinda hard to understand.. if you need to see the .mdb let me know.. i'll change data so that you'll see what i mean..

    thanks so much
    Tony...

    If you're still having problems with this Where clause, can you post the db in either Access 97 or 2000 format?... I want to see whether this checkbox on the Sites form is updating the DisposerCheck field in the recordsource... I'm sure we can fix this quickly if we saw the database itself...


    Thanks!

  12. #12
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49

    Re: Combo box help...

    Originally posted by M Owen
    Check your parens ...

    WHERE (((TblSiteCodeDisposers.Site)=[Forms]![frmSchedulingData]![cboSite]) AND ((TblSiteCodeDisposers.SiteCode)=[Forms]![frmSchedulingData]![cboSiteCode]) AND (tblSiteCodeDisposer.DisposerCheck = False))
    What's the error?

    Try

    (tblSiteCodeDisposer.DisposerCheck = 0)
    Cathy

  13. #13
    Join Date
    Jul 2003
    Posts
    292
    I think I may have figured out what I was doing wrong..

    I do have a question.. On my form the recordset is based on my query.. and in that query I have my field SiteCheck which has yes/no type..
    I need for it to show up on my Site form .. The Site form is where all sites are listed.. If the user wants to see the sites in the combo boxes then
    the user would leave the box uncheck.. if he didnt then.. simply check the box..

    The way i have my query is.. my Sitecheck field is set to false.. soo in my form it does not display the SItes that have checks on them

    Is there a way to make just that form display all checked and uncheck.. that way the user has the option to add/remove site from combo?

    thanks

  14. #14
    Join Date
    Jul 2003
    Posts
    292
    Is there any other way . besides making two exact queries? making one false and not the other..?

    thanks

  15. #15
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by TonyT
    Is there any other way . besides making two exact queries? making one false and not the other..?

    thanks
    Yes... Just take the False criteria out of the original query... (then the form will show all records... ) and add a filter to the form's recordset as you need it...

    Sample code:

    Me.Filter = "DisposerCheck = False"
    Me.FilterOn = True

    or

    Me.FilterOn = False '(when you want to see all records)

Posting Permissions

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