another combo box query question

[harleyken]

i will apologize in advance for asking again...

database set up:
table: classes available
fields: class
class size
ID (primary key)
table: Classes scheduled
fields: class type (from classes available)
start date
number of students

form: class scheduling
unbound combo box [combo2] to select class type
(based on query of classes
available, returns [ID] and [class]
need to create:
subform showing scheduled [start date] and [number of students] for type of
class shown in combo box.

code tried in subform: SELECT [sheduled classes].[class start
date],[scheduled classes].[number of students]; FROM [scheduled classes];
WHERE combo2 = [forms]![class scheduling]![combo2]

returns error: characters found after end of SQL statement

ver:2007

 

[Dale Fye]

Remove the semi-colons before the FROM and WHERE clauses. Should read:

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE combo2 = [forms]![class scheduling]![combo2]

HTH
Dale

 

[Beetle]

The "combo2" at the beginnning of the where clause doesn't look right either

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE [scheduled classes].[class type] = [forms]![class scheduling]![combo2]


--
_________

Sean Bailey

Remove the semi-colons before the FROM and WHERE clauses. Should read:

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE combo2 = [forms]![class scheduling]![combo2]

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

i will apologize in advance for asking again...

database set up:
table: classes available
fields: class
class size
ID (primary key)
table: Classes scheduled
fields: class type (from classes available)
start date
number of students

form: class scheduling
unbound combo box [combo2] to select class type
(based on query of classes
available, returns [ID] and [class]
need to create:
subform showing scheduled [start date] and [number of students] for type of
class shown in combo box.

code tried in subform: SELECT [sheduled classes].[class start
date],[scheduled classes].[number of students]; FROM [scheduled classes];
WHERE combo2 = [forms]![class scheduling]![combo2]

returns error: characters found after end of SQL statement

ver:2007

 

[harleyken]

Dale;
No more error msg (thanks!) but.. "start date" in sub form shows all
records..is this due to access storing a number instead of text in the table?

Remove the semi-colons before the FROM and WHERE clauses. Should read:

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE combo2 = [forms]![class scheduling]![combo2]

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

i will apologize in advance for asking again...

database set up:
table: classes available
fields: class
class size
ID (primary key)
table: Classes scheduled
fields: class type (from classes available)
start date
number of students

form: class scheduling
unbound combo box [combo2] to select class type
(based on query of classes
available, returns [ID] and [class]
need to create:
subform showing scheduled [start date] and [number of students] for type of
class shown in combo box.

code tried in subform: SELECT [sheduled classes].[class start
date],[scheduled classes].[number of students]; FROM [scheduled classes];
WHERE combo2 = [forms]![class scheduling]![combo2]

returns error: characters found after end of SQL statement

ver:2007

 

[harleyken]

thanks Beetle - once I put in [sheduled classes].[class type] it works!
Thanks for helping this motorhead (as in blockhead)
Ken

The "combo2" at the beginnning of the where clause doesn't look right either

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE [scheduled classes].[class type] = [forms]![class scheduling]![combo2]


--
_________

Sean Bailey

Remove the semi-colons before the FROM and WHERE clauses. Should read:

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE combo2 = [forms]![class scheduling]![combo2]

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

i will apologize in advance for asking again...

database set up:
table: classes available
fields: class
class size
ID (primary key)
table: Classes scheduled
fields: class type (from classes available)
start date
number of students

form: class scheduling
unbound combo box [combo2] to select class type
(based on query of classes
available, returns [ID] and [class]
need to create:
subform showing scheduled [start date] and [number of students] for type of
class shown in combo box.

code tried in subform: SELECT [sheduled classes].[class start
date],[scheduled classes].[number of students]; FROM [scheduled classes];
WHERE combo2 = [forms]![class scheduling]![combo2]

returns error: characters found after end of SQL statement

ver:2007

 

[Beetle]

You're welcome. I've been referred to as a blockhead myself on more than one
occasion : )
--
_________

Sean Bailey

thanks Beetle - once I put in [sheduled classes].[class type] it works!
Thanks for helping this motorhead (as in blockhead)
Ken

The "combo2" at the beginnning of the where clause doesn't look right either

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE [scheduled classes].[class type] = [forms]![class scheduling]![combo2]


--
_________

Sean Bailey

Remove the semi-colons before the FROM and WHERE clauses. Should read:

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE combo2 = [forms]![class scheduling]![combo2]

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

i will apologize in advance for asking again...

database set up:
table: classes available
fields: class
class size
ID (primary key)
table: Classes scheduled
fields: class type (from classes available)
start date
number of students

form: class scheduling
unbound combo box [combo2] to select class type
(based on query of classes
available, returns [ID] and [class]
need to create:
subform showing scheduled [start date] and [number of students] for type of
class shown in combo box.

code tried in subform: SELECT [sheduled classes].[class start
date],[scheduled classes].[number of students]; FROM [scheduled classes];
WHERE combo2 = [forms]![class scheduling]![combo2]

returns error: characters found after end of SQL statement

ver:2007

 

[Ken Sheridan]

Assuming the [class type] column in [scheduled classes] references the
primary key [ID] column of [classes available], and that the BoundColumn
property of the combo box is the [ID] column (i.e. 1 if the [ID] column is
the first column returned by the RowSource query), then you don't need to
reference that column in the subform's underlying query's WHERE clause. Just
use:

SELECT [class type], [class start date], [number of students] FROM
[scheduled classes]
ORDER BY [class start date];

Set the LinkMasterFields property of the subform control to:

[combo2]

and set its LinkChidFields property to:

[class type]

You don't need to show the [class type] column in the subform, but it needs
to be returned by the underlying query so that it can be referenced as the
linkChildFields property. When a class type is selected in the combo box on
the parent form the subform will return only those rows where the class type
matches the value of the combo box.

The inclusion of an ORDER BY clause in the query will order the rows in the
subform by date. If you want them in reverse date order, i.e. latest first,
then just change it to:

ORDER BY [class start date] DESC;

Ken Sheridan
Stafford, England

Dale;
No more error msg (thanks!) but.. "start date" in sub form shows all
records..is this due to access storing a number instead of text in the table?

Remove the semi-colons before the FROM and WHERE clauses. Should read:

SELECT [sheduled classes].[class start date],
[scheduled classes].[number of students]
FROM [scheduled classes]
WHERE combo2 = [forms]![class scheduling]![combo2]

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

i will apologize in advance for asking again...

database set up:
table: classes available
fields: class
class size
ID (primary key)
table: Classes scheduled
fields: class type (from classes available)
start date
number of students

form: class scheduling
unbound combo box [combo2] to select class type
(based on query of classes
available, returns [ID] and [class]
need to create:
subform showing scheduled [start date] and [number of students] for type of
class shown in combo box.

code tried in subform: SELECT [sheduled classes].[class start
date],[scheduled classes].[number of students]; FROM [scheduled classes];
WHERE combo2 = [forms]![class scheduling]![combo2]

returns error: characters found after end of SQL statement

ver:2007