Form based on query creates problem

crich21

Registered User.
Local time
Today, 10:39
Joined
Jan 10, 2003
Messages
97
Everything was great until this. I have a form which the recordsource was built with the sql, query builder option. My form worked great and I could add or change anything I wanted to. But I had to have a way to keep track of price changes so I add a table. Then I had to use a make a query with the max function to only show the latest prices. so naturally I had to include this in the recordsource builder. Now I can't change or add anything, it says this recorset is not updateable. I need the max function and to be able to add or change anything in my form except for the price. Is this possible? Please help me.
 
Not positive but I don't think the max records feature is what is preventing the recordset from being updateable. It probably has to do with the table you added.

What is the relationship between the two tables and are you trying to change data in fields from both tables or just one?
 
table added

small example of tables is
Parts:
PartID
PartName
Description

CostofGoods:
CostID
PartID
Cost
U/M

PartID's are linked one to many

Then I have a query: (LastCostDate)

SELECT CostofGoods.PartID, Max(CostofGoods.Date) AS MaxOfDate
FROM CostofGoods
GROUP BY CostofGoods.PartID;

Then the recordsource for my form is:

SELECT [Parts].[PartID], [Parts].[Description], [Parts].[PartName], [CostofGoods].[Cost], [CostofGoods].[U/M] FROM (Parts INNER JOIN LastCostDate ON [Parts].[PartID]=[LastCostDate].[PartID]) INNER JOIN CostofGoods ON ([LastCostDate].[MaxOfDate]=[CostofGoods].[Date]) AND ([Parts].[PartID]=[CostofGoods].[PartID]);
 
I understand more about what you're trying to accomplish. You want to have a separate table where you can create new price records and depending on the date of an order you want the right price selected, yes?

Not sure which one was added but I can tell you that when you have a one-to-many join in a query it will only let you update the many side table.

Something I've done in the past for the same challenge was in the prices table have an effective date and an expiration date. That way you can have queries look up prices based on the product id and whether the date is in between those two dates. Just a suggestion.
 
Maybe this will help

I have attached a file with the exact form i am using. What I need to do is be able to keep track of the price changes but everything I am trying is not giving me the desired results. Does anyone have a suggestion on how to add this feature and still keep the form working the way it is. I have added a doubleclick event on the cost field but this just creates a duplicate part entry. It would be ok to have to click an edit button to change the part# and Part description if needed. I am just burnt out trying to figure it out and only know what I have read here and in books. No Classes. Thank you
 

Attachments

Rob,
Not sure which one was added but I can tell you that when you have a one-to-many join in a query it will only let you update the many side table.
Access does allow updates to both sides of the join.

crich21, The problem is caused by the Max() function. Queries that include aggregate functions or join to queries that include aggregate functions are NOT updateable. To get around the problem, you can move the Max() into a subquery or you can obtain the data by using DMax().
 
???

Subquery? that's something I'll have to read up on. and the dmax() function, where would I add that to make that function. Would it still be possible to add a new record in my subform?
 
not sure what you mean

Not sure what you mean. Are you saying on the form use a combobox and have it find the correct part price. If so could you elaborate because this would be on a subform doesn't that make it all kind of tricky?
 
You should be able to use somtehing like query3 as the rowsource for the combo.


query1:
Select p.ProductID, p.EffectiveDate
From tblProduct as p
Where p.EffectiveDate Not > Forms!YourOrderForm!YourOrderDate;

query2:
Select p.ProductID, Max(EffectiveDate)
From query2 as p
Where p.EffectiveDate Not > Forms!YourOrderForm!YourOrderDate
Group By p.ProductID;

query3:
Select p.productID, P.EffectiveDate, P.UnitPrice
From tblProduct as p Inner Join query2 as q ON p.productID = q.ProductID AND p.EffectiveDate = q.EffectiveDate
Order By ProductID;

The first query selects the effective dates for each product that are <= the order date. You need this so you can look at historical orders and still get the correct unit price.

The second query gets the Max() effective date for each product. The selection criteria needs to be applied prior to obtaining the Max() so that the Max() only considers the correct set of data.

The third query takes the productID and the Max() effective date and finds the associated unit price. You should also include a join to the main product table in this query so that you can also obtain the product description.
 
Haven't tried this yet

Pat,
I am somewhat confused on the what you are telling me to do. I don't think this will work for a subform set to continuous records will it. In the file I attached, notice that this program is only used as an informational source and not a purchase order program. What I use it for is to select a vendor and be able to scroll through a "continuous" list of parts from that vendor with product name, part number, cost, u/m, if the part is currently used. So, please correct me if I am wrong but I don't believe the combobox will work in this situation. But maybe I don't fully understand the sql you have posted.

Sorry, I don't completely understand.
 
I took a look at the db you posted and there are some problems. What are you trying to do with the UnitPrice information? Do you really need a price history or do you just need to see the current price? You have price in two places and that doesn't make sense. It is with the product information and it is also stored in another table where it is dated. Your form shows the value from the product table and you need to double click on the field to see the info from the other table. You really should NOT be storing this information in two places. Either stick with a single most current value in the product table or remove the price field from the product table and use the dated version.
 
i'm sorry

This was a sloppy db to post. I originally had the unitprice in the product table and everything worked great except I had no way of keeping track of price changes. It was then that I created the cost table and queries. I am totally lost on a good way to be able to view the current prices and also be able to add or edit current information without having to switch between different forms. The main goal is to have the cost field only display the current price, then when double-clicked be able to see the past prices, date and the comments or reason for the change. I know I can use the 2 query method to find the most recent price and display it but I guess what I don't know how to do is how to make the subforms information editable. I also need to be able to create new records, relatively easy.
 
You have the locked property set to yes for the price and unit of measure columns. You can find that setting on the Data tab of the properties dialog for each control.

1. Unlock the 2 fields
2. Base the UM field on a combobox to standardize the units.
3. Rather than using the cost of goods form to enter pricing information, enter it on the subform and only use the pop up to display history. Use the AfterUpdate event of the subform to add the price history rows whenever the user changes the price on the subform. You also need to think about how to handle UM. If you store it in both places that implies that you also want to create history when the UM changes.
 
Correct me if I am wrong

I understand what you are suggesting, so what would the recordsource for the subform become. I am using a query but this is giving me problems with entering a cost before a part record has been created and the u/m field may work if I just lock it. But how would you suggest I locate the most current price. I should probably lock the price and make them double-click to change the price ?????

the updates I have made are attached. Thank you for all your help so far.
 

Attachments

?

I am getting a
"cannot add record(s): join key of table 'cost of goods' not in recordset
what does this mean.
 
You don't need to change any recordsource. Read my post again. I said to store the current cost in the subform's table. When the record shown in the subform is changed, store the historical values (you will have to save the old values in the beforeUpdate event of the form so they will be available in the AfterUpdate event for you to write them to the history table.

The UM field won't work if you just lock it. You need to be able to change it on the subform.

There is no need to make them double click to change the price. You want to use the double click event to open the history form.
 
I think we're getting somewhere

So I wasn't really to far off with my original design having the cost and the um in the tblparts. I have to say that I don't know very much at all about vba and I think that is what I need to make the beforeupdate event of the subform store the cost history. ? Can I use vba to take the previous cost and um values and open the history form, insert the values correspondingly, then I would like to be able to enter comments on the reason for the cost change. ? Pat, when you say cost history form, is this editable and if so is there a way to lock the date, cost, um and just leave the comments open. I hope I am not being to difficult or of any bother. Thank you for getting me this far.
 
So I wasn't really to far off with my original design
Only if you are ABSOLUTELY sure that you will NEVER need to use this structure to process order transactions. You said that you only want to keep old prices for historical reference.
Can I use vba to take the previous cost .....
You DO NOT do this by using the pop-up form. The pop-up is NOT updateable. It is ONLY for reference. In the AfterUpdate event of the subform, you would use DAO or ADO to insert a row into the history table. Look up OpenRecordset and AddNew in help for code samples. Don't worry about copying the original values as I said earlier. Just take the current values and add them. So, when you add the product for the first time, the first price goes into history, when the price is changed, the second price goes into history, etc. Just make sure that the user changed either the price or the UM. You could open the history form after the row is inserted so they can put comments in the comment field. Just make sure that all other fields on the pop-up are locked and the AllowAdditions and AllowDeletions properties are set to no.
 

Users who are viewing this thread

Back
Top Bottom