Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

update field in a table with conditions

Hi,
I have attached the sample file.
I need away to update query in such away like this:
in form1 i have a subform refer to a query , now i have unbound textbox  controls in the main form , i need to fill inside a unbound textboxes:
txtPackageType=1000
txtQuantity=15
txtCurrencyValue=20
when i click the fill quantity button it should fill the txtquantity =15 into the quantity field in the subform in such away that 15 will be distributed between the quantity field that are from the type txtPackageType=1000 and txtCurrencyValue=20 on condition that the max quantity for each cell depends on cellcapacity of the record.
if you look at the first line in the subform quary you will see that the quantity=3, which means it reachs the max of the cellcapacity (3*1000=3000)
and the second record has 2000, means it needs to fill in another 1 quantity to reach 3000
and keep going filling each record until filling all .
SmartStorageV2.5.accdb
0
drtopserv
Asked:
drtopserv
  • 7
  • 4
1 Solution
 
drtopservAuthor Commented:
I forgot to mention that when the CalculatedTotalInCell reachs it max (CalculatedTotalInCell=cellcapacity) then should gives occupied checkbox=true
0
 
als315Commented:
In this case you need  gustav's (cactus_data) approach. Look at sample
SmartStorageV2.5.accdb
0
 
drtopservAuthor Commented:
Wow, it`s working MAN!!!
but is it possible to fill the quatity in the record but starting from small to big number in shelfname.
means to start filling from shelf1 then shelf2 then 3 ...
cuz when i tried it it filles the records randomly .
see attached.
SmartStorageV2.5.accdb
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
als315Commented:
Yes, test new sample
SmartStorageV2.5s.accdb
0
 
drtopservAuthor Commented:
Nope not working:{
0
 
drtopservAuthor Commented:
Try put package type : 3000
quatity :4
currencyValue:20
0
 
als315Commented:
Your shelfname is text field and it is ordering according to text rules, where 3 goes after 21. Change 3 to 03 or change name field to number.
SmartStorageV2.5s.accdb
0
 
drtopservAuthor Commented:
so weird, still not working!, i changed it to number / checked ur code with 01 instead of 1
it puts the 3000 into shelfs 2,3,19,20 , and not 1,2,3,4
0
 
als315Commented:
I don't understand. For package type 3000 and currencyvalue 20 you have only following available:
orderHow Should we fill?
0
 
drtopservAuthor Commented:
You ARE the BEST of the BEST!!!!!
I`ll post another continious Q :}}}
0
 
drtopservAuthor Commented:
Pefecto!~
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now