If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Text box un-editable on form
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. |
#2
|
|||
|
|||
Text box un-editable on form
"KneeDown2Up" wrote in message ... 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 |
#3
|
|||
|
|||
Text box un-editable on form
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 |
#4
|
|||
|
|||
Text box un-editable on form
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 "KneeDown2Up" wrote in message ... 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 |
#5
|
|||
|
|||
Text box un-editable on form
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. "Carl Rapson" wrote: 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 "KneeDown2Up" wrote in message ... 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 |
#6
|
|||
|
|||
Text box un-editable on form
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 "KneeDown2Up" wrote in message ... 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. "Carl Rapson" wrote: 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 "KneeDown2Up" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|