Allow Edit OK but can't do it

[Stuart Grant]

I have a small table Curr for currencies with fields for description, rate
and one or two others. Each week I want to update the rates. I made a
small form with text boxes for the fields and it works fine, comes up with
the descriptions and rates but I cannot edit the rates figure to put in the
current rate. I cannot enter anything or delete the old rate. I have
checked the text box properties. It is not locked, Allow Edits is Yes as is
Allow Deletes but it won't let me do anything.

It sounds elementary but what am I doing wrong. Oh, and F2 doesn't do
anything either.

Stuart

 

[Dirk Goldgar]

I have a small table Curr for currencies with fields for description,
rate and one or two others. Each week I want to update the rates. I
made a small form with text boxes for the fields and it works fine,
comes up with the descriptions and rates but I cannot edit the rates
figure to put in the current rate. I cannot enter anything or delete
the old rate. I have checked the text box properties. It is not
locked, Allow Edits is Yes as is Allow Deletes but it won't let me do
anything.

It sounds elementary but what am I doing wrong. Oh, and F2 doesn't do
anything either.

Check the form's RecordSource property. Is the form based directly on
the table, or is the recordsource an inline query (SQL statement) or a
stored query? If it's not based directly on the table, what is the SQL
of the query? Maybe the query is not updatable.

 

[Stuart Grant]

Dirk
Thanks for very prompt reply.
The record source ofthe form is a stored query, QueryCurr.
The SQL statement is as follows
SELECT DISTINCT Curr.CDescLong, Curr.Rate, Curr.CNum, [Rate]/[Factor] AS
ExchRate
FROM (Curr INNER JOIN Master ON Curr.CNum = Master.CurrNum) INNER JOIN Holds
ON Master.ID = Holds.ID
ORDER BY Curr.CNum;
I don't see why the query would not be updateable but would appreciate your
advice.
Stuart

 

[Rick Brandt]

Dirk
Thanks for very prompt reply.
The record source ofthe form is a stored query, QueryCurr.
The SQL statement is as follows
SELECT DISTINCT

DISTINCT makes your query non-editable.

 

[Stuart Grant]

Rick
Thanks for the explanation but how do I do what I want ?
Each Currency only occurs once but in the Currs table but in Holds there are
various Securities and in one field the ID for the Master Record gives all
the details of this security inclung the currency number CNum, which in turn
is in the Currs table.
I only want to pull out those currencies which occur at least once in the
Holds table but not those which do not occur at all. Then I want to enter
the current value for the exchange rate. My SQL statement does just that -
pulls out one entry for each currency. But how do I enter a new value for
each of these currencies ?
Be very grateful if you can help.
Stuart

 

[Dirk Goldgar]

Rick
Thanks for the explanation but how do I do what I want ?
Each Currency only occurs once but in the Currs table but in Holds
there are various Securities and in one field the ID for the Master
Record gives all the details of this security inclung the currency
number CNum, which in turn is in the Currs table.
I only want to pull out those currencies which occur at least once in
the Holds table but not those which do not occur at all. Then I want
to enter the current value for the exchange rate. My SQL statement
does just that - pulls out one entry for each currency. But how do I
enter a new value for each of these currencies ?

How about something like

------ warning: "air SQL" ------
SELECT
CDescLong,
Rate,
CNum,
[Rate]/[Factor] AS ExchRate
FROM Curr
WHERE Exists
(
SELECT
Master.CurrNum
FROM
Master
INNER JOIN
Holds
ON
Master.ID = Holds.ID
WHERE
Master.CurrNum = Curr.CNum
)
ORDER BY
CNum;
------ end SQL ------

 

[Stuart Grant]

Dirk
Thank you very much for the SQL code. I did not know the keyword EXISTS in
SQL, or at least I must have forgotten it, because it is mentioned briefly
in "SQL Queries for Mere Mortals", which I have read.
The code works fine. Problem solved.
We inexperienced amateurs on this forum are extremely fortunate in having
pros like you and Rick, willing to spend time to look at problems and
provide answers. Thank you very much indeed.
Stuart