Coding not right?

[Crackers]

I have a Form that currently contains a checkbox that has the following OnClick event...
SELECT tblPlayerRegister.Surname,tblPlayerRegister.[Club],tblPlayerRegister.Age
FROM tblPlayerRegister
WHERE (((tblPlayerRegister.Age) < 11) And ((tblPlayerRegister.Club) = "Beaconsfield"))
ORDER BY tblPlayerRegister.Surname;

The aim of this is to select the Surname, Club & Age from the Table PlayerRegister where the Age is less than 11 and the Club is 'Beaconsfield' and then Sort in Ascending Order by Surname.
I get an error when I write this code and I don't know what I am doing wrong.
I am not very knowledgable with Coding but I have given it a go.
There will eventually be many checkboxes with different criteria and I don't want to do a query for every one.
Could somebody please advise what I have done wrong?
Thank-you for any assistance given.

 

[JimmyK]

What does the error say?

 

[Crackers]

Reply to question from JimmyK

Compile error:
Expected: Case

Also the SELECT line and the ORDER line are in red text

 

[CEH]

You might want to rethink how your setting this up... Cascading combos might work better for you. Heres a link from another post...
http://www.fontstuff.com/access/acctut10.htm

One thing to think about..... I always try to stay away from having fixed criteria in code...... For example your age "< 11" and your "Beaconsfield"
What happens if you want different criteria? You have to add or change code. What if you have split your DB and made the front end a ".mde" file.... You can NOT change your code.
Looks like the cascading combo might work for you here. One query, sort, filtered by the choices in the combos.

 

[mschwent]

The On_Click event is for VBA code, and you are using a SQL query, they are 2 different languages, they go in different places.

SQL is only for queries. If the values of some listbox or combobox depend on the checkbox, then you need to set this as a property of the combobox, not in the code of the checkbox.

So something like this:

(i think rowsource is the property you need, but I'm not sure...)

private sub <checkbox name>_after_update()

<combobox name>.rowsource = "SELECT (e.[firstname] & " " & e.[lastname]) AS employee, (s.firstname & " " & s.lastname) AS supervisor ROM AAtblJob AS e, aatbljob AS s HERE e.supervisorid = s.empid ORDER BY e.[empID];"

<combobox name>.requery

end sub

The text in blue is your SQL code, the rest is VBA code.

The first line (well, lines) changes the property of a combo box to get the data you want it to have. The second line tells the combobox to refresh its data.

If this doesn't work, you might want to repost the question in the VBA forums, they'll be able to assist you better than I.

(The reason you get that error message by the way: VBA also uses the word SELECT, but it uses it in a compeltely different way, and it's called a SELECT CASE statement, hence the expected: Case)

 

[mdow]

The issue at hand is that the code you posted is SQL. The OnClick event would run VBA code. If you want to run SQL with VBA you need to use the DoCmd.RunSQL and pass your SQL statement as a string.

The next issue is that you generally don't run this type of SQL statements from VBA code because there is no container to return the data. You can run action queries using this method. An example of an action query would be a query that will update records.

What you need to do is write a query that takes parameter values from your form. One query will be all you need since the fields you want to look at are always the same. You can use DoCmd.OpenQuery for this.