Form based on query creates problem

[crich21]

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.

 

[Rob.Mills]

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?

 

[crich21]

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]);

 

[Rob.Mills]

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.

 

[crich21]

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

 

[Rob.Mills]

Oops. Sorry about that. Thanks for the correct, Pat.

 

[crich21]

???

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?

 

[Rich]

Can't you use a combo box to get the latest price?

 

[crich21]

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?

 

[crich21]

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.

 

[crich21]

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.

 

[crich21]

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.

 

[crich21]

?

I am getting a
"cannot add record(s): join key of table 'cost of goods' not in recordset
what does this mean.

 

[crich21]

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.

 

[crich21]

Thank you

I will look up the samples and give it a whirl.... I will definitely not be using this for order entry, as I am a novice and am thankful to have made it this far. Wish I could adapt order entry but I am a far cry from being able to accomplish that.