Control on form problem

  • Thread starter Thread starter MJJ
  • Start date Start date
M

MJJ

This might be a query problem!

I use a form based on two tables and a query. I cannot
update the form when I use this query but if I take the
query out of the "query", I can update/input.

The query within the query is:
SELECT [Date Played].id, Last([Date Played].[Date Played])
AS [LastOfDate Played1]
FROM [Date Played]
GROUP BY [Date Played].id
ORDER BY [Date Played].id;

This shows me the last date the card was used.

I need to update the form but I also need to get the last
date used. Any sug's?
 
Hi MJJ

First, you should be using Max, not Last. Using Last requires that Access
assemble a complete ordered query in order to put the maximum value in the
last record, and then return that value. It's best to use First and/or Last
only when you want an arbitrary value returned.

Secondly, a GROUP BY query is never updatable. The simplest solution is
probably to use a calculated field with DMax:
DMax( "[Date Played]", "[Date Played]", "id=" & [cardID] )

This cal be either a calculated field in your query (best for a continuous
form) or the ControlSource of a textbox on your form.
 
completey off the record...

My son wants to come to New Zeeland for 1 year to study.
I would like to ask some questions about you country...
Are you willing to answer them?
Thanks
JP


Graham Mandeno said:
Hi MJJ

First, you should be using Max, not Last. Using Last requires that Access
assemble a complete ordered query in order to put the maximum value in the
last record, and then return that value. It's best to use First and/or Last
only when you want an arbitrary value returned.

Secondly, a GROUP BY query is never updatable. The simplest solution is
probably to use a calculated field with DMax:
DMax( "[Date Played]", "[Date Played]", "id=" & [cardID] )

This cal be either a calculated field in your query (best for a continuous
form) or the ControlSource of a textbox on your form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

MJJ said:
This might be a query problem!

I use a form based on two tables and a query. I cannot
update the form when I use this query but if I take the
query out of the "query", I can update/input.

The query within the query is:
SELECT [Date Played].id, Last([Date Played].[Date Played])
AS [LastOfDate Played1]
FROM [Date Played]
GROUP BY [Date Played].id
ORDER BY [Date Played].id;

This shows me the last date the card was used.

I need to update the form but I also need to get the last
date used. Any sug's?
Click to expand...
Click to expand...
 
Hi Jean-Paul

Certainly, if I can. Email me at (e-mail address removed)

- Graham

Jean-Paul De Winter said:
completey off the record...

My son wants to come to New Zeeland for 1 year to study.
I would like to ask some questions about you country...
Are you willing to answer them?
Thanks
JP


Graham Mandeno said:
Hi MJJ

First, you should be using Max, not Last. Using Last requires that Access
assemble a complete ordered query in order to put the maximum value in the
last record, and then return that value. It's best to use First and/or Last
only when you want an arbitrary value returned.

Secondly, a GROUP BY query is never updatable. The simplest solution is
probably to use a calculated field with DMax:
DMax( "[Date Played]", "[Date Played]", "id=" & [cardID] )

This cal be either a calculated field in your query (best for a continuous
form) or the ControlSource of a textbox on your form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

MJJ said:
This might be a query problem!

I use a form based on two tables and a query. I cannot
update the form when I use this query but if I take the
query out of the "query", I can update/input.

The query within the query is:
SELECT [Date Played].id, Last([Date Played].[Date Played])
AS [LastOfDate Played1]
FROM [Date Played]
GROUP BY [Date Played].id
ORDER BY [Date Played].id;

This shows me the last date the card was used.

I need to update the form but I also need to get the last
date used. Any sug's?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top