Combobox based on a query 2

[severin]

I have combobox where the source is a query and the elements in the box is from a table. This setup is working other places in the database when the source is a table. So the only difference is the fact that the source is based on a query.

I can't see what's wrong.
Help..
From Denmark.

 

[LittleSmudge]

By SOURCE - do you mean

A) the field that the control is bound to

B) the combo box control's RowSource property


And WHAT do you mean by - 'NOT WORKING' ?


does the query produce the expected results when it is run as a query in its own right ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[severin]

Sorry.. I have to translate everything so it's a little confusing.

The answer is A, meaning that the field that the control of the combo box is bound to is a field from the query, which is the control of the form!!!

So when I open my form and use the combo box, I get the option yes or no and then I pic one. The response is that the recordset cannot be updated.

Thanks for responding.

 

[LittleSmudge]

RIGHT

so it is a problem with the recordset rather than a problem with the combo box.

The combo box is working fine.

The FORM is bound to the QUERY
Ie The Form's RecordSource property = YourQuery

Where YourQuery is either the name of the query that you have stored in the Query pane of the database window
OR
The SQL string in full for the Query that you want to use.


Your problem is that YourQuery is a "Not-Updatable" query.

Go back and correct the JOINS to make it Updatable


If you struggle with that then post the SQL string of YourQuery .



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[severin]

Help

SQL string of YourQuery...

SELECT Stikprøver.[Bedømt uge], Stikprøver.Bedømmelsesrunde, Stikprøver.[Ny rækkefølge], Stikprøver.Id, Stikprøver.Nr, Stikprøver.Dato, Stikprøver.Kode, Stikprøver.[Kar nr], Stikprøver.[Sort/type], Stikprøver.[Vægt i gram], Stikprøver.Godkendt
FROM Stikprøver
GROUP BY Stikprøver.[Bedømt uge], Stikprøver.Bedømmelsesrunde, Stikprøver.[Ny rækkefølge], Stikprøver.Id, Stikprøver.Nr, Stikprøver.Dato, Stikprøver.Kode, Stikprøver.[Kar nr], Stikprøver.[Sort/type], Stikprøver.[Vægt i gram], Stikprøver.Godkendt
HAVING (((Stikprøver.[Bedømt uge])=[Bedømt uge:]) AND ((Stikprøver.Bedømmelsesrunde)=[Bedømmelsesrunde:]) AND ((Stikprøver.[Ny rækkefølge])<>0))
ORDER BY Stikprøver.[Ny rækkefølge];

By the way I read your FAQ about avoiding space characters. NOTED...

 

[LittleSmudge]

By the way I read your FAQ about avoiding space characters. NOTED...

Good lad !


Your problem

GROUP BY

ANY query with a Group By clause MUST be Un-Updateable as one row in the dynaset ( query output ) results from many rows in the underlying table(s)

so if you try to update a record - then JET has no way of knowing which record to write to.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[severin]

To littlesmudge

I must admit you have earned the points, I believe you are spot on, but can you help me with an solution.

 

[PHV]

And what happens with this SQL ?
SELECT Stikprøver.[Bedømt uge], Stikprøver.Bedømmelsesrunde, Stikprøver.[Ny rækkefølge], Stikprøver.Id, Stikprøver.Nr, Stikprøver.Dato, Stikprøver.Kode, Stikprøver.[Kar nr], Stikprøver.[Sort/type], Stikprøver.[Vægt i gram], Stikprøver.Godkendt
FROM Stikprøver
WHERE (((Stikprøver.[Bedømt uge])=[Bedømt uge:]) AND ((Stikprøver.Bedømmelsesrunde)=[Bedømmelsesrunde:]) AND ((Stikprøver.[Ny rækkefølge])<>0))
ORDER BY Stikprøver.[Ny rækkefølge];


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Do you NEED theGrouping summary aspects of what you have created ?

PHV has just suggested a SQL string that will be Updatable that covers the same selection and ordering criteria.

If you can live without the Grouping then try PHV's suggestion.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[severin]

The SQL from PHV worked, thanks...

And thanks LittleSmudge for your part in it.

Had to give both of your a star.

Thanks from Denmark.