Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Question Unanswered: New SQL Question

    I need an SQL statement or Query to do the following.. can someone help me out with the code? I'm still Green behind the ears when it comes to SQL.


    SELECTS the number of checkboxes that are checked in a table
    FROM a table that is chosen in a combo box on the FORM1



    The following code works exactly how I want it to.. except I am limited to one table ... (Table_1)

    SELECT count([Table_1].[Checkboxes])
    FROM Table_1
    WHERE ((([Table_1].[Checkboxes])=True));

    so.. if I have 3 records in Table_1 and 2 have checked checkboxes... it returns the value 2.

    My question is how to take out "Table_1" and put in the the value of what the user selects in my combobox.

    My combobox lists all my tables... Table_1, Table_2, Table_3.. etc.

    Thanks for any help.
    -----

    "How much deeper would the ocean be without sponges?"

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: New SQL Question

    Try:

    "SELECT count(1) FROM " & Forms!<your form name>!<your combobox name> & " WHERE [Checkboxes]=True;"

  3. #3
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Talking That works... but...

    Thank You very much. It works.. but

    Now I need that value inserted into a textbox on a form...

    any suggestions?
    -----

    "How much deeper would the ocean be without sponges?"

  4. #4
    Join Date
    Nov 2002
    Posts
    150

    Re: That works... but...

    Originally posted by stratego16
    Thank You very much. It works.. but

    Now I need that value inserted into a textbox on a form...

    any suggestions?
    Set the control source of the textbox to: ="SELECT count(1) FROM " & Forms!<your form name>!<your combobox name> & " WHERE [Checkboxes]=True;"

  5. #5
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Lightbulb what it gives me

    It gives me this in my textbox when I do that...

    #Name?
    -----

    "How much deeper would the ocean be without sponges?"

  6. #6
    Join Date
    Nov 2002
    Posts
    150

    Re: what it gives me

    Originally posted by stratego16
    It gives me this in my textbox when I do that...

    #Name?
    If the form with the combobox still open? Also, did you include the = in the control source?

  7. #7
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Question Thanks for the Help

    Yes and Yes

    My combobox and Textbox are on the same form... and.. I did include the equals...

    Here is my statement in the Combobox Afterupdate event:

    Me.Text14.ControlSource = "SELECT count(1) FROM " & Forms!Generate_Test!Combo21 & " WHERE [Include]=True;"


    I think it has something to do with the Control Source. Can it be a string?
    -----

    "How much deeper would the ocean be without sponges?"

  8. #8
    Join Date
    Nov 2002
    Posts
    150

    Re: Thanks for the Help

    Originally posted by stratego16
    Yes and Yes

    My combobox and Textbox are on the same form... and.. I did include the equals...

    Here is my statement in the Combobox Afterupdate event:

    Me.Text14.ControlSource = "SELECT count(1) FROM " & Forms!Generate_Test!Combo21 & " WHERE [Include]=True;"


    I think it has something to do with the Control Source. Can it be a string?
    First Try: Me.Text14.ControlSource = "SELECT count(1) FROM " & Me.Combo21 & " WHERE [Include]=True;"

    Then Try: Me.Text14.ControlSource = "=SELECT count(1) FROM " & Me.Combo21 & " WHERE [Include]=True;"

  9. #9
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Post Still Not it

    Me.Text14.ControlSource = "SELECT count(1) FROM " & Me.Combo21 & " WHERE [Include]=True;"

    The above syntax still gives me #Name?

    Me.Text14.ControlSource = "=SELECT count(1) FROM " & Me.Combo21 & " WHERE [Include]=True;"

    The above gives me the following error:
    The syntax of the subquery in this expression is incorrect.



    Thanks for all the help..
    -----

    "How much deeper would the ocean be without sponges?"

  10. #10
    Join Date
    Nov 2002
    Posts
    150

    Re: Still Not it

    Originally posted by stratego16
    Me.Text14.ControlSource = "SELECT count(1) FROM " & Me.Combo21 & " WHERE [Include]=True;"

    The above syntax still gives me #Name?

    Me.Text14.ControlSource = "=SELECT count(1) FROM " & Me.Combo21 & " WHERE [Include]=True;"

    The above gives me the following error:
    The syntax of the subquery in this expression is incorrect.



    Thanks for all the help..
    Alright, change of pace. Get rid of the code we tried and replace it with:

    Me.Text14 = "DCOUNT("*", Me.Combo21, "[Include]=True")

  11. #11
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Talking Thanks

    FirstandGoal4...

    Thank you for your help. I was able to solve the problem.. by.. deleting my Textbox.. and changing it to a List box. I used the sql code above that you helped me develop to make it work with the listbox.

    Geez.. what a Long Long day!

    Brian in NC
    -----

    "How much deeper would the ocean be without sponges?"

Posting Permissions

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