Hi all, I'm so tired of looking... please help me!!
I have an Access Data Access Page based on a query which is not updatable.
The query is based on a many-to-many table (e.g. OfficeProducts with foreign keys for OfficeID and ProductID and an Amount field). The query is not updatable as I have included outer joins to the Office and Product tables to get all their records back even if there is no entry in OfficeProducts for that office or product the record is still shown with an Amount of null.
(I'm not sure if I've done this the best way, but need to be able to list all the possible Products for each Office whether they use them or not.)
I want the user to be able to update the Amount field, so e.g. if it is currently set to 5 to be able to change it to 4, or if it is currently null (as there is no record so far for that OfficeProduct) then to insert a record into OfficeProducts with the OfficeID, ProductID and Amount value entered.
I thought this would be straightforward by creating a new textbox in the same section as the current Amount field, calling it NewAmount and letting the user update this, fire an event trigger to either update or insert into the table, and refresh the query and record on the DAP so that Amount shows the newly updated/inserted value.
I have been searching for ages... cannot locate either the best event or events to use for this nor the code to enter!! Please please help!!!!!
Please tell me if there is another forum better suited to my query. Can't find the DAP or Vbscript one.
Ta muchly.
I have an Access Data Access Page based on a query which is not updatable.
The query is based on a many-to-many table (e.g. OfficeProducts with foreign keys for OfficeID and ProductID and an Amount field). The query is not updatable as I have included outer joins to the Office and Product tables to get all their records back even if there is no entry in OfficeProducts for that office or product the record is still shown with an Amount of null.
(I'm not sure if I've done this the best way, but need to be able to list all the possible Products for each Office whether they use them or not.)
I want the user to be able to update the Amount field, so e.g. if it is currently set to 5 to be able to change it to 4, or if it is currently null (as there is no record so far for that OfficeProduct) then to insert a record into OfficeProducts with the OfficeID, ProductID and Amount value entered.
I thought this would be straightforward by creating a new textbox in the same section as the current Amount field, calling it NewAmount and letting the user update this, fire an event trigger to either update or insert into the table, and refresh the query and record on the DAP so that Amount shows the newly updated/inserted value.
I have been searching for ages... cannot locate either the best event or events to use for this nor the code to enter!! Please please help!!!!!
Please tell me if there is another forum better suited to my query. Can't find the DAP or Vbscript one.
Ta muchly.
