SubForm RecordSet Uneditable based on query structure! But I need it :/

[MissAran]

Hi There.

Hopefully I can clearly explain my woe.

Problem:

I have a Main Form and a SubForm.

The SubForm contains fields to a table (Booking Item) that records -
Qty, Item ID, Booking_Id Description and Price.

The Subform source is a query that gets the details from the following Tables.

Booking Form (which is the Main Form) and Stock Info with the Price calculated by the Booking Item Qty and Item ID.

This structure works fine and the users are able to add new Booking Items from the Form - see attached Image1 (very simplified version)
The query for the above is:

SELECT DISTINCTROW [Job Details].Units, 
[Job Details].[Stock code], 
stock.DESCRIPTION, 
[Job Details].job_id, 
IIf(IsNull([Job Details].Price),STOCK.SALES_PRICE,[Job Details].Price) AS SALES_PRICE
FROM ([Job Details] INNER JOIN booking_form ON [Job Details].job_id=booking_form.job_id) INNER JOIN stock ON [Job Details].[Stock code]=stock.STOCK_CODE;

HOWEVER
Here is the new requirements.
The Price for each Item will be depending on a special code that will be linked to each Customer.

So what I "thought" was the way to achieve this is to:

1. Create a new Table of PriceCode (autonumber) and description
2. Create a second table containing all StockID, PriceCode and the new Price.
3. Add a new field to the Customer table called PriceCode

The Second table "Stock_PriceCodes" is a one to many to one table between Stock and the Customer. That was logical thinking.


So I did so and set the correct data. Next was to tackle the query to calculate the new prices in consideration to the customer id within the Booking Form.


So, with the new structure in place I went to add the tables to the Query - attached is the query in the design view.


The Query created is below:

SELECT DISTINCTROW [Job Details].Units, [Job Details].[Stock code], stock.DESCRIPTION, [Job Details].job_id, IIf(IsNull([Job Details].Price),Stock_PriceBand.Price,[Job Details].Price) AS SALES_PRICE
FROM ((([Job Details] INNER JOIN booking_form ON [Job Details].job_id = booking_form.job_id) INNER JOIN stock ON [Job Details].[Stock code] = stock.STOCK_CODE) INNER JOIN customers ON booking_form.customer_id = customers.customer_id) INNER JOIN Stock_PriceBand ON (Stock_PriceBand.StockID = stock.STOCK_CODE) AND (customer.PriceBandID = Stock_PriceBand.PriceBandID);

The Query runs fine as expected, except, I can not pdate rows or add new records via the SubForm.


I have so :banghead:at this point as I just can not for the life of me figure out how to achieve what I thought was a sound data structure to special prices to get to work on the subform.


Seriously, any help or if anyone has had the same experience would be fantastic!

 

[spikepl]

Before proceeding with anything, I'd recommend that you select a naming convention and implement it consistently all over. Your stuff will be hard to change now and unmaintainable in the future if you do not fix this. http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[MissAran]

Fair enough, but this is work from a predecessor I.each I've been handed this from the client. So regards to naming conventions I don't think it stops folk from solving the issue. I'll recommend this to the client but time is money and I doubt they will pay for a clean up.

 

[spikepl]

Spaghetti code is VERY expensive. Additions and maintenance customarily exceed manyfold the original price of any system. Your client can pay X now , or 10 times X with time.

As to your problem: Anything about your query that strikes you as odd?

Check out http://allenbrowne.com/ser-61.html

 

[MissAran]

Again, completely understandable. I will approach them when I can regarding this.

I have seen those tips before and yes I understand why my query would not work i.e. too many joins is the obvious I think.

But, my question is really how to achieve what I am asking with a similar structure i.e. A subform that can calculate the price based off the customer's priceband and still be able to be editable. A reference to experience would be great, perhaps someone has achieved this requirement or could point me in the right direction to resolving it.