Text box un-editable on form

[Guest]

Ok, so I've seen other posts about this, but as yet no conclusions.

I have a form which sources from a query which draws data from two tables.
The query does not allow me to enter any data, and hence neither will the
form.

The SQL is;

SELECT Orders.[Supplier details], suppliers.[address 1st line] & " " &
suppliers.[address 2nd line] & " " & suppliers.[address 3rd line] & " " &
suppliers.[address 3rd line] & " " & suppliers.postcode AS Address,
Orders.Qty, Orders.[Order Placed], Orders.Description, Orders.[Cost per unit
(Approx)], Orders.Total, Orders.[Ordered by], Orders.[Expenditure Code],
Orders.[ALO Authorised], Orders.[ALO Declined], Orders.Comments, Orders.[ALO
FWD], Orders.[Date Placed], Orders.[Date ALO Auth'd], Orders.[Date BSU
Ordered], Orders.[Order Number (BSU)]
FROM Orders INNER JOIN Suppliers ON Orders.[Supplier details] =
Suppliers.[Supplier Name]
WHERE (((Orders.[ALO Authorised])=-1) AND ((Orders.[ALO Declined])=0) AND
((Orders.[ALO FWD])=0))
ORDER BY Orders.[Date Placed] DESC;

Sorry it's a bit 'windy' but am new at this - can anyone throw any light on
this please? - Many thanks in advance.

 

[Carl Rapson]

Ok, so I've seen other posts about this, but as yet no conclusions.

I have a form which sources from a query which draws data from two tables.
The query does not allow me to enter any data, and hence neither will the
form.

The SQL is;

SELECT Orders.[Supplier details], suppliers.[address 1st line] & " " &
suppliers.[address 2nd line] & " " & suppliers.[address 3rd line] & " " &
suppliers.[address 3rd line] & " " & suppliers.postcode AS Address,
Orders.Qty, Orders.[Order Placed], Orders.Description, Orders.[Cost per
unit
(Approx)], Orders.Total, Orders.[Ordered by], Orders.[Expenditure Code],
Orders.[ALO Authorised], Orders.[ALO Declined], Orders.Comments,
Orders.[ALO
FWD], Orders.[Date Placed], Orders.[Date ALO Auth'd], Orders.[Date BSU
Ordered], Orders.[Order Number (BSU)]
FROM Orders INNER JOIN Suppliers ON Orders.[Supplier details] =
Suppliers.[Supplier Name]
WHERE (((Orders.[ALO Authorised])=-1) AND ((Orders.[ALO Declined])=0) AND
((Orders.[ALO FWD])=0))
ORDER BY Orders.[Date Placed] DESC;

Sorry it's a bit 'windy' but am new at this - can anyone throw any light
on
this please? - Many thanks in advance.

Have you tried joining the two tables (Orders and suppliers) in the
Relationships window? According to Access Help, a query joining two tables
can't be updatable unless the two tables are also joined in the
Relationships window. For this to work, the tables must be joined through a
primary key - foreign key pair (such as suppliers.[Supplier Name] (PK) and
Orders.[Supplier details] (FK)).

Also keep in mind that a user-defined field (such as concatenating the
address fields) won't be updatable.

HTH,

Carl Rapson

 

[Guest]

Thanks for replying Carl. I've tried that but doesn't seem to make any
difference.

I actually only need to update the order placed, date order placed and order
no from this form.

 

[Carl Rapson]

Well, that's about the extent of my advice. I set up a couple of tables
patterned after your description, and the query was indeed nonupdatable.
Once I joined the tables in the Relationships window, the query was
updatable (all fields but the concatenated one). So I'm not sure where else
the problem might lie. Sorry I can't help more.

Carl Rapson

Thanks for replying Carl. I've tried that but doesn't seem to make any
difference.

I actually only need to update the order placed, date order placed and
order
no from this form.

Have you tried joining the two tables (Orders and suppliers) in the
Relationships window? According to Access Help, a query joining two
tables
can't be updatable unless the two tables are also joined in the
Relationships window. For this to work, the tables must be joined through
a
primary key - foreign key pair (such as suppliers.[Supplier Name] (PK)
and
Orders.[Supplier details] (FK)).

Also keep in mind that a user-defined field (such as concatenating the
address fields) won't be updatable.

HTH,

Carl Rapson

 

[Guest]

mm, that's interesting Carl, how exactly did you join them, via supplier name
and supplier details? I sort of found a way around it by making another form
and then inserting it as a subform, and whilst not as good as how you appear
to have done it, it sort of gives me a fix - but it would be nice to know how
you done it exactly.

Well, that's about the extent of my advice. I set up a couple of tables
patterned after your description, and the query was indeed nonupdatable.
Once I joined the tables in the Relationships window, the query was
updatable (all fields but the concatenated one). So I'm not sure where else
the problem might lie. Sorry I can't help more.

Carl Rapson

Thanks for replying Carl. I've tried that but doesn't seem to make any
difference.

I actually only need to update the order placed, date order placed and
order
no from this form.

Have you tried joining the two tables (Orders and suppliers) in the
Relationships window? According to Access Help, a query joining two
tables
can't be updatable unless the two tables are also joined in the
Relationships window. For this to work, the tables must be joined through
a
primary key - foreign key pair (such as suppliers.[Supplier Name] (PK)
and
Orders.[Supplier details] (FK)).

Also keep in mind that a user-defined field (such as concatenating the
address fields) won't be updatable.

HTH,

Carl Rapson

 

[Carl Rapson]

I made the [Supplier Name] field the PK in the suppliers table, and I
indexed the [Supplier details] field in the Orders table. Then, in the
Relationships window, I joined those two fields, resulting in a one-to-many
relationship. Then, when I created the query, it was updatable.

HTH,

Carl Rapson

mm, that's interesting Carl, how exactly did you join them, via supplier
name
and supplier details? I sort of found a way around it by making another
form
and then inserting it as a subform, and whilst not as good as how you
appear
to have done it, it sort of gives me a fix - but it would be nice to know
how
you done it exactly.

Well, that's about the extent of my advice. I set up a couple of tables
patterned after your description, and the query was indeed nonupdatable.
Once I joined the tables in the Relationships window, the query was
updatable (all fields but the concatenated one). So I'm not sure where
else
the problem might lie. Sorry I can't help more.

Carl Rapson

Thanks for replying Carl. I've tried that but doesn't seem to make any
difference.

I actually only need to update the order placed, date order placed and
order
no from this form.


Have you tried joining the two tables (Orders and suppliers) in the
Relationships window? According to Access Help, a query joining two
tables
can't be updatable unless the two tables are also joined in the
Relationships window. For this to work, the tables must be joined
through
a
primary key - foreign key pair (such as suppliers.[Supplier Name] (PK)
and
Orders.[Supplier details] (FK)).

Also keep in mind that a user-defined field (such as concatenating the
address fields) won't be updatable.

HTH,

Carl Rapson