Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Post Unanswered: Combo box in a query

    Hi there

    I'm new to this game so I hope this isn't to basic

    I am triing to put a combo box from a form [contact] into my query so the user can choose options.

    SELECT Contacts.Title, Contacts.FirstName, Contacts.LastName, Contacts.Category, Contacts.Home_PhoneNumber, Contacts.Home_CellPhoneNumber, Contacts.Business_PhoneNumber, Contacts.[Business_Cell phone]
    FROM Contacts
    WHERE ((Contacts.Category) Like [forms]![Contact]![Category]);

    I know I am almost there from rearch in other areas but the combo is not happening.

    Can any body help me.

    Mikeg950
    Mikeg950

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the LIKE is expecting something along the lines of
    LIKE "*subStringInQuotes*"

    try:
    Like '*" & [forms]![Contact]![Category] & "*'");

    izy

  3. #3
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25
    Originally posted by izyrider
    the LIKE is expecting something along the lines of
    LIKE "*subStringInQuotes*"

    try:
    Like '*" & [forms]![Contact]![Category] & "*'");

    izy


    Thanks so much I will give that a go.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my reading skills are improving all the time!

    now i notice you are using a combo.

    LIKE and combo are an unusual pair... the combo is almost certainly returning an exact match to the field you are looking at so you need = rather than LIKE (if for no other reason than it's faaaaaster).

    if the field & bound column of the combo is a string:
    = '" & [forms]![Contact]![Category] & "');

    and if numeric
    = " & [forms]![Contact]![Category] & ");

  5. #5
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Combo box in a query

    Hi there izyrider,

    Thanks so much for your interest so far. Its still not working properly yet and I'm not sure if we are able to make it work in SQL. I will explain what the user will be doing with this personal contact directory.

    In my main menu the if the user wants to view phone numbers as a report of a certain category such as "friends" for example then they click "Preview Phone No" button. At this point it will open the query where instead of entering the certeria by hand as the spelling will have to be exact like:
    WHERE (((Contacts.Category)=[Please enter in the category]));
    They will have the pop up box with the combo box of critera to choose from.

    I already have a form called contact where there is a combo box in it where it has (Friends,family etc in it),so I am wanting to get this combo box and place it in the query where when the query is promted it is promted with the combo box field from the contact field.

    WHERE (((Contacts.Category) ='" & [forms]![Contact]![Category] & "'));

    This isn't promting the user with the pop up box with the combo box with the selection to choose from. I have tried inserting [] in that query so that the pop up appears but it doent like it.

    I hope this gives you a better explaination

    Thanks

    Mikeg950

  6. #6
    Join Date
    Sep 2003
    Location
    India
    Posts
    11

    Combo box

    I used it so many times . U send me database with some sample data i will do it and send back to u.
    Pankaj Garg

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Mikeg950,

    i don't think you are going to succeed with getting SQL to call an access form! access can handle the parameter query with it's own built-in input box, but you can't substitute your own popup as far as i know (well probably you can by messing with the .dlls etc but that's not fun)

    the workaround is for you to call the popup before you build the SQL. you will need a public var on the calling form for the popup to feed

    izy

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    another thought: maybe you want to use this same popup in different places. i was struggling with this a while back and couldn't find a good solution. my nasty workaround is
    http://dbforums.com/showthread.php?threadid=909567

  9. #9
    Join Date
    Sep 2003
    Location
    India
    Posts
    11

    your database

    In your database you open the form pankaj.
    I did it in two ways one by the query named bypankaj and another you can check by opening the form pankaj in design view and then select the button "Open Form" then go to properties event tab then check the codes by clicking on "On click"

    Another database i am attaching developed by me. You can alter that. Open the database while pressing shift key only then you would be able to see the database window.

    Good Luck
    Pankaj Garg
    Attached Files Attached Files
    Pankaj Garg

  10. #10
    Join Date
    Sep 2003
    Posts
    1

    Lightbulb

    Mikeg950,

    From the mdb file I saw, It looks like you want to filter the report using a combo box to enter your filter expression.

    From what I can see from the dbcontact.mdb file, you are trying to pull information from a form that is not open (or probably not open) yet. Since the value you are trying to assign to your SQL statement does not exist yet, it can't see it to use it.

    What you need to do it to create a small form with only a combo box and a button on it. Setup the combo box to show the values of the "Category" field. Then use the button to create an event that will open the report.

    (now here is where my inexperience shows through.) You can open the report from the button and use the where SQL statement and it to the small OPEN form: Forms![FORM NAME].[COMBO BOX NAME] in the where statement.

    I was not (quickly) able to get that way to work, so I added the "WHERE (((Contacts.Category)=[FORMS]![PickCategory].[PickCAT])); to the record source parameter of the report.


    I presume the dbcontact.mdb file is yours. I've attached the modified mdb file, If it does what you want, great.

    kneekeep
    Attached Files Attached Files

  11. #11
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25
    Hi there every body

    I have reliesed that you are unable to make a query in SQL with a built in combo box. So from all of your ideas I have made a form with the combo box and the query compares the data in the Contacts.category with the data in the new form "Choose Category".

    WHERE (([Contacts].[Category]) Like [forms]![Choose Category]![Choose Category]);

    To get the query to run you click the "Preview phone no" button which opens the "Choose Category" form where you select from the combo box the category that you want the report to create. I have made a button on this "Choose Category" form where it then opens the Form "Phone numbers by category". This is now running great.

    I will now be able to use this concept with the Birthday Report.

    Thank you to you all for your input.

    I hope you have leant some thing from this too like me.

    Mikeg950
    Mikeg950

  12. #12
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Talking

    Sorry small typo

    To get the query to run you click the "Preview phone no" button which opens the "Choose Category" form where you select from the combo box the category that you want the report to create. I have made a button on this "Choose Category" form where it then opens the Report "Phone numbers by category". This is now running great.

    Problem solved.
    Mikeg950

Posting Permissions

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