Queries behind forms

[Rhids]

Hi All,

I'm hoping this is something basic, but I've racked my brains and don't seem to be able to solve this.

In the attached database there is a form called "Events- QA", on the form there is a combo field called "Event types" Behind this field in the "Row source" property there is an SQL statement;

SELECT [Event types look up].[Event types] AS Expr1, [Events  types look up].[Event descriptions] AS Expr2
FROM [Event types look up]
WHERE ((([Event types look up].[Event types])="QA"));

Which I think works, it limits the combo box to just the option that I want appearing in that field on that form. Might not be elegent...

What doesn't seem to work is on the form propertise I goto the "On Open" property and use this SQL statement;

SELECT [Events].[Serial number], [Events].[Events type], [Event].[date], [Events].[inputters name], [Events].[QA final check date], [Events].[qa final check pass], [Events].[comments]
FROM [Events]
WHERE ((([Events].[Events type])="QA"));

When I save, close and reopen this form, nothing happens, worse I goto check the "On Open" property and the SQL is no longer there ...

Thats what I'm trying to work out at the sec. The goal is to have the form open and only events with QA in the event types field of QA are shown.
The code behind the source row on the combo box, I think works fine and only mention it in case it's causeing a conflict somewhere

 

[Jon K]

Put the SQL statement inside a pair of double-quotes and surround QA with single-quotes e.g.

Me.Combo17.RowSource = "SELECT [Event types look up].[Event types] AS Expr1, [Event types look up].[Event descriptions] AS Expr2 FROM [Event types look up] WHERE ((([Event types look up].[Event types])='QA'));"


You can also use two consecutive double-quotes to replace the single quotes e.g.
................ [Event types])=""QA""));"


You must put it in one line. If you break the string into more than one line, you will need to use the & concatenation character and the line continuation character _ to join the lines. There are many code examples in Access's help and in the VBA forum.

Note
I used the [Event types look up] table as illustration as the fields in the [Events] table are different.
.

 

[Rhids]

Thanks JonK.

It worked a treat