Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Stretch2312 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    12

    Combobox based on a query, Query based on inputs from Form HELP!!


    I have created a form with 3 combo boxes on it. I am trying to get one of the combo boxes to display the results of a query. The query is based on 2 other combo boxes on the same form.

    I have 3 tables

    tblFLOC
    tblMachine
    tblLine

    Table FLOC has to look up fields for Line and Machine. The query I have created works fine until i try to get the inputs from the form.
    cboLine is based on a query of tblLine
    cboMachine is based on a query of tblMachine
    cboFLOC should be based on a query useing cboLine and cboMachine as the criteria.

    Every time I run it it comes up with a dialog box requesting the criteria. Can someone please help.

    Below is the SQL of the cboFLOC query


    SELECT FLOC.ID, FLOC.FLOC, LINE.LineID, LINE.[Line Name], MACHINE.MachineID, MACHINE.[Machine Name]
    FROM MACHINE INNER JOIN (LINE INNER JOIN FLOC ON LINE.LineID = FLOC.Line) ON MACHINE.MachineID = FLOC.Machine
    WHERE (((LINE.[Line Name])=[Forms]![Tags]![txtLine]) AND ((MACHINE.[Machine Name])=[Forms]![Tags]![cboMachine]));

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What is it asking you when it asks for criteria. Usually when it does that you're attempting to use a value for criteria that does not match what you've typed. For instance your cboMachine may be named cbo_Machine in which case you'll be prompted to put in a value for [cboMachine]. The very first thing to do is to check to see on your TAGS form if the two fields you're referencing (txtLine and cboMachine) are actually typed correctly on both the form AND in the query.

  3. #3
    Stretch2312 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    12
    Have checked and both are spelled the same on both Form and Query.

    The dialog box asks me to "Enter Parameter Value. Forms!Tags!cboLine"

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What does cboLine represent?
    I see
    WHERE (((LINE.[Line Name])=[Forms]![Tags]![txtLine]) AND ((MACHINE.[Machine Name])=[Forms]![Tags]![cboMachine]));
    cboMachine or txtLine in your SQL but no cboLine????

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Then the query you originally posted isn't the problem. Somewhere in the event of whatever you're doing you are referencing a field named cboline which does not exist on your form.

    If you can't find it try posting the entire section of code that is bombing

  6. #6
    Stretch2312 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    12
    TPM Tags DatabaseTEST.zipI have attached a stripped down version of my Database. Sorry I am kind of new to Access

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I can't view 2007 and higher, if you convert it to 2003 I'll be happy to take a look.

  8. #8
    Stretch2312 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    12
    TPM Tags DatabaseTEST.zip
    Thank you again for your help

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok there are a few problems here. One, you are limiting your cboLine to only show line items that have a match in your FLOC table but your cboLine is a combo box that is unbound which I assume means you're trying to use it to search for records.

    So really you want your record source to be ALL possible lines so you can pick one of any of them.

    You also have some weird lookup error going on because you have lookup fields on your tables, this is one of the main reasons I don't use them. If you get an error it's extremely hard to track down WHY it's occuring.

    Here's what I would do

    1. Delete both your cboLine and cboMachine
    2. Create a new combo box (cboLine) with the following query as it's record source
    Code:
    SELECT LINE.LineID, LINE.[Line Name] FROM LINE ORDER BY LINE.[Line Name];
    make the combo box have 2 columns, bound column is 1, column widths 0;2
    3. Create a new combo box (cboMachine) with the following query as it's record source
    Code:
    SELECT MACHINE.MachineID, MACHINE.[Machine Name] FROM MACHINE ORDER BY MACHINE.[Machine Name];
    make the combo box have 2 columns, bound column is 1, column widths 0;2
    4. in the ON EXIT property of each combo box have (or if you prefer in your afterupdate event)
    cboFloc.requery

    From there you shouldn't have any problem with your cboFloc combo box.

    I'm almost 100% sure you're encountering an artifact error because you're using a table that has a lookup value for a field (I've had this problem before using other people's tables). Compacting/Repairing won't fix the problem either you basically have to reformulate the parts that aren't working.

  10. #10
    Stretch2312 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    12
    Thankyou very much for your help.

    I have one more quick question. I cant seem to get the cboFLOC.Requery to work.
    I get the following message
    Click image for larger version. 

Name:	Capture.JPG 
Views:	7 
Size:	37.1 KB 
ID:	9416

  11. #11
    Stretch2312 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    12
    I modified the code in the "After Update" on both combo boxes, I no longer get an error, however I still dont get any information in cboFLOC

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Nothing will appear in that box if you haven't picked a combination of line and machine that exists in your FLOC table. So for instance in your example data if you choose line FC01 and machine CONVEYOR there are only 2 items in your FLOC table that have that line/machine combination. Again, with your test data if you were to choose line FC04 and ANY machine you would get no results because there is nothing on your FLOC table that has those two values in line/machine.

  13. #13
    Stretch2312 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    12
    I have chosen the right combination of Line and Machine and still dont see any results.

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You didn't do what I said then. Here's your sample database back

    TPM Tags DatabaseTEST-Modified.zip

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    INstead of trying to work with combo boxes at this point, I recommend you work on getting your tables and relationships designed to match what you are trying to do. As rpeare suggested, you should get rid of the lookups at the table level.
    see http://access.mvps.org/access/lookupfields.htm for rationale.

    You have told us HOW you are trying to do something. I think it's time to hear about WHAT you are trying to do in plain English. Forget the combo boxes etc and just tell us straight out.

    Good luck with your project.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query based on Combobox Value
    By Stretch2312 in forum Programming
    Replies: 3
    Last Post: 10-08-2012, 08:23 AM
  2. Replies: 2
    Last Post: 04-06-2012, 11:20 AM
  3. Run query based on value in combobox
    By tobinjames in forum Queries
    Replies: 2
    Last Post: 12-07-2011, 07:32 AM
  4. Query based on combobox
    By scotty562 in forum Queries
    Replies: 6
    Last Post: 11-11-2010, 08:49 AM
  5. query based on two combobox
    By datto in forum Queries
    Replies: 0
    Last Post: 04-18-2007, 11:10 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