Need Query To Be Updateable

[robsworld78]

Hi, I have a query that I have to have updateable I've tried so much to work around the query but can't get anywhere. The query isn't updateable because it has sums on it and has to.

Does that mean there is no way to make it updateable period?

I have a query that calculates how much product is left. On a form I need people to enter how many they returned and in another field it will calculate how many were sold by saying barsleft - barsreturned

On the form in a subform I have a query that joins product and inventory together and its updateable but when I add a query to the query it goes not updateable because the query I add isn't updateable, I need this query on there because it says how many are left.

I've tried putting the how many left in another subform which works and I can then update my other subform because the query doesn't have the barsleft on it anymore. But the problem with that is I can't string anything together that will pull the values from the other subform that has barsleft on it.

If its on one subform I go Me.BarsSold = Me.BarsReturned will move barsreturned to the barssold field but I can't string anything to go

Me.BarsSold = VendorInventoryLevelSubform.BarsLeft - Me. BarsReturned.

I also tried adding another field to the orginal subform and in the control source I put
=[Forms]![NightCount]![VendorInventoryLevelSubform].[Form]![BarsLeft]
but it only puts the top record in all the records on the other subform, it doesn't do it line by line. The vendorinventorylevelsubform shows values for all the product remaining but when I call it to the other field only the top record is seen and put in all records on the other subform.

[SoftwareMatters]

If there is Grouping in the query then it can't be updateable.

I would probably write a function to return the required data and then either add this as a column to the recordset or add it to the controlsource of an unbound control.

If you need help creating a function let me know.

[boblarson]

Just keep the non updatable stuff out of the form's recordsource and then use another subform which has the non updatable query.

[robsworld78]

Hi, there is no grouping, the query isn't updateable because I added a query to the query which has sums and last of's on it so that query is making it nonupdateable.

I definitely need help with this one, I've been playing for days with it know and getting frustrated.

I tried making another subform with the nonupdateable stuff on there which works however I can't call that data. The only way I was able to call from barsleft is if I put =[Forms]![NightCount]![NightCountInventorySubform].[Form]![BarsReturned] in the control source for any field except that will only get me the first record of the subform and repeat that record on all records on the other subform.

The field thats screwing me up is BarsLeft, this sums the total given and subtracts total sold which leaves me with total barsleft. Now on the updateable subform I have barsreturned and barssold. What I ulitmately want is barsleft-barsreturned = barssold.

In the barsreturned afterupdate I put me.barssold = me.barsreturned.

That works great but I can't call to the other subform and have it retreive the barsleft.

[boblarson]

I guess I'm a bit confused as I'm not completely understanding what it is you have and why you have the same thing showing in two subforms. Perhaps uploading a copy of the database (with bogus data of course) would help.

[robsworld78]

Yeah I can understand how you could be confused I can't explain this stuff. Thanks for offering to take a look, I appreciate it! I uploaded the files, the db is split so I've linked them to be in c:\Inventory Control

I added 2 orders under the name Jennifer, you will see how much inventory she has when you select her name. The 2nd order is ready for a night count, so that's the form you want to open and I have all the problems with.

You'll see a few subforms on there as I was trying different things but ulmiately what needs to happen is a person needs to enter there returns in and on that form it needs to calculate how many were sold based on what was returned. When that order is posted in the night count the table "inventory" needs to have numbers in the bars sold.

When Jennifer goes out tomorrow she gets her inventory thats left and any new inventory. so the field "barsLeft" which I need to caluclate from and is bascially the sumofbarsgiven - sumofbarssold and a few other things. That field comes from the "VendorInventoryLevel" Query.

When playing if you happen to hit post on the night count form just open the "orders" table and change the field "status" to "on Road" and it will show up there again.

If on that order you can enter some bars returned and it figures out how many were sold based by the numbers on the column titled "Given" which is actually "BarsLeft" in the querys and can actually write those sold numbers to the "ineventory table" then all is good.

The barsreturned field in the "inventory" table isn't linked or used anywhere in the db, it was just a place to store that number for each item and hopefully do the math on the form. The field thats used thought out the system is barssold that's why it needs to be in that field when that form is closed.

[boblarson]

Okay, I'll have to wait until I get home tonight but I will try to do it either then or over the weekend. If someone else gets to it before me, then bonus.

[robsworld78]

Thanks!

I just checked my paypal and I have $11.15 Cnd or $11.99 US and it goes to however can solve my small issue and 2 other issues.

The other 2 I call small issues because I don't think are hard to fix if you know what you're doing.

When I create new orders I start on the subform for the order, then I go the inventory subform and enter the inventory for the order. The inventory is linked to the order via the OrderID.

When I select the product for the order I have a dropdown list with all the product available, I want it so once an item is on that order its removed from the combo box. So if I'm donig a new order and my first item is giant sandwich, then I go to the next line but when I hit that arrow on the combo box I don't want to see the giant sandwich.

Next issues is when a form is open, as in create new order, it goes to the first record, which is the first vendor. I would like the form to open blank and a person has to select from the combo box who they want to create an order for.

[robsworld78]

Whoever can fix my issues first gets $11 via payal.

I've got one major problem I've been trying to fix for days and I'm getting frustrated and need help but hands on help.

When the db was created it was to be that at the end of a day a person enters how much product they sold, after I'm almost finished they decide that won't work and want a person to enter how much product is left and do the math to figure how how much was sold. I didn't think it would be hard to do but wow was I wrong.

Vendors all have there own inventory and everyday new inventory gets added, so I have a query called "VendorInventoryLevel" which always says how much inventory a vendor has, it takes sumofbarsgiven - sumofbarssold. So that query isn't updatedable, big problem for me anyways.

On the "NightCount" form I have a field titled "Given" which is the field "barsleft" and it comes from the "VendorInventoryLevel" query. Then there was a field for barssold, which worked but now I've added a 3rd field barsreturned and been trying to enter numbers in that field and it automatically puts the correct number in bars sold based on the barsleft and barsreturned fields. Everything I've tried doesn't work. All has to happen on this form otherwise the whole structure of the db needs changing and for me that would mean starting from scratch because barssold is used in almost every form and query and report. So when that form closes the correct number for BarsSold needs to be put in the "inventory" table, otherwise everything breaks.

I've been explaining what I've done so many names and its very hard to understand so I thought I would just post the db and hope someone can get it working for me. If I had more money I would pay more but just got over $11 in the paypal which I'll happily give if someone can fix this.

The other 2 I call small issues because I don't think are hard to fix if you know what you're doing. I just need them done on the "createorders" form and I'll copy what was done and do it to the rest of them.

When I create new orders I start on the subform for the order, then I go the inventory subform and enter the inventory for the order. The inventory is linked to the order via the OrderID.

When I select the product for the order I have a dropdown list with all the product available, I want it so once an item is on that order its removed from the combo box. So if I'm donig a new order and my first item is giant sandwich, then I go to the next line but when I hit that arrow on the combo box I don't want to see the giant sandwich.

Next issues is when a form is open, as in create new order, it goes to the first record, which is the first vendor. I would like the form to open blank and a person has to select from the combo box who they want to create an order for.

I uploaded the files, the db is split so I've linked them to be in c:\Inventory Control

I added 2 orders under the name Jennifer, you will see how much inventory she has when you select her name. The 2nd order is ready for a night count, so that's the form you want to open and I have all the problems with.

You'll see a few subforms on there as I was trying different things but ulmiately what needs to happen is a person needs to enter there returns in and on that form it needs to calculate how many were sold based on what was returned. When that order is posted in the night count the table "inventory" needs to have numbers in the bars sold.

When Jennifer goes out tomorrow she gets her inventory thats left and any new inventory. so the field "barsLeft" which I need to caluclate from and is bascially the sumofbarsgiven - sumofbarssold and a few other things. That field comes from the "VendorInventoryLevel" Query.

When playing if you happen to hit post on the night count form just open the "orders" table and change the field "status" to "on Road" and it will show up there again.

If on that order you can enter some bars returned and it figures out how many were sold based by the numbers on the column titled "Given" which is actually "BarsLeft" in the querys and can actually write those sold numbers to the "ineventory table" then all is good.

The barsreturned field in the "inventory" table isn't linked or used anywhere in the db, it was just a place to store that number for each item and hopefully do the math on the form. The field thats used thought out the system is barssold that's why it needs to be in that field when that form is closed.

[robsworld78]

I'm getting closer, if I use the code below it works but on the subform with the column "Given" it takes the first record, 33, and uses that for all the records on the outline subform where I'm enter the returns.

Me!BarsSold = Forms!NightCount!NightCountInventorySubform.Form!B arsLeft - Me!BarsReturned

Any ideas why?

I attached a pic so you can see the form I'm working with.

[robsworld78]

So close I can feel it. I just discovered that on the subform with "given" column if I select the 2nd record it will use the number in that record, if I select the 3rd record it will use that number, great! Now I need to figure out when I select a record in the outlined subform the same record gets selected in the subform with given on it without having someone select it first manually.

Can that be done?

[robsworld78]

Everything is solved. Here's what I used.

Private Sub Form_Current()
With Forms!NightCount!NightCountInventorySubform.Form.R ecordset
.FindFirst "InventoryID = " & Forms!NightCount!NightCountEnterReturnsSubform.For m.InventoryID
If .NoMatch Then
MsgBox "Record ID " & Forms!NightCount!NightCountEnterReturnsSubform.For m.InventoryID & " not found!"
End If
End With
End Sub

Thanks for the help!