Trouble copying field for all records

[robsworld78]

Hi, I have a form that has 3 subforms, one subform is linked to the main form and the other 2 subforms are linked to that subform.

The 2 subforms that are linked to the same subform and bring up the same data and have the same amount of rows or records, these subforms are set to continious form. One of them uses a query thats locked and one uses a query that allows editing.

Theres a field on the subform thats locked and I want to copy it to the other subform, When I use the following code it uses the first record from the NightCountInventorySubform and fills all the records on the other subform with it.

=[Forms]![NightCount]![NightCountInventorySubform].[Form]![BarsReturned]

That code is in barsleft in subform3

This is what results

Subform 2 _______________Subform 3
BarsLeft ________________ BarsLeft
18 _____________________18
24_____________________ 18
12_____________________ 18

Obviously I need it to be
Subform2 ________Subform3
BarsLeft_________ BarsLeft
18______________18
24 ______________24
12______ ________12

As I mentioned Subform2 and subform3 are both linked to subform1 the same way.

 

[GinaWhipp]

I think this code can be adjusted to do what you want...

http://www.allenbrowne.com/ser-57.html

 

[robsworld78]

unfortuantely that doesn't really make sense to me, wuold it be easier to have all the records in the subform values turn to 0 when opens. There will be a value in barsreturned already but when I open a subform it would be nice if all those numbers went to zero.

Does that make sense?

 

[GinaWhipp]

Nope, not to me... I thought you wanted the values to carry over.

 

[robsworld78]

Yeah I do need the values to be copied over. Maybe you can help me string this line together, I've tried a lot of different strings for the one subform but can't recall the record.

But first I will explain how it all works.

The form is for items returned at the end of the night. When a new order is created its marked as "On Road" when it has that status if a person goes to the night count form the order will show up there and a person enters there night count.

On this form it shows the product and how many of each item were given at the beginning of the day, then it has a column for bars returned and bars sold.

So the field barsleft is making the query nonupdateable because it comes from a query that has sums and last of's. Doing that I can't enter bars returned which sucks because life would be so easy.

So to make the query updateable I made another subform which holds the barsleft field which was causing the product subform to be nonupdateable. So now I can enter numbers into bars returned however I can't make barssodl do the math from the otehr subform holding barsleft.

If everything is in the same subform I add the following to the afterupdate of barsreturned.

me.barssold = me.barsleft - me.barsreturned

That works great however when everything is on the same subform its not updateable so that can't do the job even though it wants to.

When I move barsleft to the other subform I can't string it to pull the records from barsleft.

me.barssold = NightCountInventorySubform.barsleft - me.barsreturned

Where I have the subform name what should I have to look for that field on that subform. Both subforms are linked the same way, to the orders subform using orderID.

The only string I can put together that actually pulls data from the new subform is by putting =[Forms]![NightCount]![NightCountInventorySubform].[Form]![BarsReturned] in the control source for a field but that of course copies the first record to all records.

Let me know if this clears things up

 

[GinaWhipp]

WOW, that's alot...

I think the issue might actually be the way the tables are set-up but without seeing the database hard to tell.

The only thing that comes to mind because of all the unupdateable queries is to use unbound text boxes and then to run an UPDAE query with a Me.Requery but not even sure that will work but it's worth a try.

 

[GinaWhipp]

Happen to come across your new post with the database attached and yep, it's the tables.

Inventory should just be Inventory and sold/returned/etc... should come from Order Details which I don't even see. Perhaps it would help to tell us what industry this is to be applied to?

 

[robsworld78]

Hi, are you sure my tables are wrong, I know there worded wrong. I have a table called product, which is all the product, then orders which holds all the orders and then inventory which is all the product that goes on an order or purchase or transfer, isn't that the way to do it. Inventory is linked to orders via the ID of whatever type of transaction it is. It all works good and if I knew that they wanted returns instead I would have used returns field in all the queries and calculated the sold. But to do that now is almost impossible its nested everywhere.

There must be some bandage that can fix the night count sheet, that's the only issue.

 

[robsworld78]

The db is inventory control, its for the ice cream industry. We need to track inventory by vendor, cooler, vehicle and trailer, its kinda complicated but all works great. I wish I knew from the beginning it would have been based on bars returned instead of bars sold that's really screwed things up for me.

 

[GinaWhipp]

I am not saying all your tables are wrong I'm saying you're missing tables and you fields instead of records. You also have information stored in tables that should not in *that* particular table which is why your calculations are giving you an issue. Typically something for Inventory Control would look like...

tblInventory

tblOrders
tblOrderDetail MISSING

tblVendors (not sure why you need stblSuppliers perhaps you could explain)

tblProducts (Is this different then tblInventory)

tblBins ???
tblCooler ???
tblTransfers ???
tblMoney ???
tblPuchases ??? (Why wouldn't this be under tblOrderDetails?)

tblVehicles
tblVehicleMileage (Not sure why this is needed)
tblVehicleTypes

...and I still have no idea what the Bars are or where they fall under.

 

[robsworld78]

lol yeah its a little confusing.

1. tblInventory = product
2. tblorders = orders
3. tblordersdetail = inventory (this is the order details, its actually details for transfers and purchases)
4. tblvendors = vendors
5. suppliers is there in case he wants to track who he purchases from at a later date.
6. transfers is the same as orders but for transfers
7. purchases is the same as orders but for purchase (details for transfers and purchases are stored in inventory)
8. the other ones are for tracking bins, vehicles, and vehicle type. The mileage is for a later date.

I'm sure the table structure is correct because it works flawlessly, calulations would work for bars returned but I would have to edit every query form and report as bars total would no longer be entered in, it would always calculate. The problem is because after I'm done they change from barssold to bars returned and I don't want to rewrite the whole thing, all I need is one the 1 form, "night count" I need those numbers to do math on that form instead of in queries because I would then need to update the whole db again.

Hopefully your just confused on the wording of my tables. Inventory is order details. Its not missing.

 

[robsworld78]

Open the form "night count" and you will see where I need the math done.

 

[GinaWhipp]

How could table Inventory by Order Details AND Product? Not correctly normalized which makes the data harder to work with.

Okay so I open *Night Count* and then go to Design View and I have a subform sitting on top of another subform but I ignore that and go to the RecordSource and once again the only way I see to do this in it's present state is by using an UPDATE query with a Me.Requery on all the forms with no guarantee it will work.

Maybe someone else can get this to work in its present state but with the tables the way they are with fields instead of records and the other items I have no other suggestion.

 

[robsworld78]

Does that update query force a query to be updateable? Is that what that's about? I've not used any query type but select.

If I make the same query I have made as a select query as an update query will I be able to update it?

As a select query I can't update it and thats the problem. If an update query can be updated then I think this will work.

 

[GinaWhipp]

The UPDATE query would update the fields in the table which is the underlining source for the query. If you turn the query into a SELECT query then no need for the UPDATE query because you will be able to type in the fields.