Solved

update field in a table with conditions

Posted on 2013-06-08
11
628 Views
Last Modified: 2013-06-08
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
Comment
Question by:drtopserv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 

Author Comment

by:drtopserv
ID: 39231885
I forgot to mention that when the CalculatedTotalInCell reachs it max (CalculatedTotalInCell=cellcapacity) then should gives occupied checkbox=true
0
 
LVL 40

Expert Comment

by:als315
ID: 39231914
In this case you need  gustav's (cactus_data) approach. Look at sample
SmartStorageV2.5.accdb
0
 

Author Comment

by:drtopserv
ID: 39232077
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
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 40

Expert Comment

by:als315
ID: 39232502
Yes, test new sample
SmartStorageV2.5s.accdb
0
 

Author Comment

by:drtopserv
ID: 39232515
Nope not working:{
0
 

Author Comment

by:drtopserv
ID: 39232517
Try put package type : 3000
quatity :4
currencyValue:20
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 39232523
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
 

Author Comment

by:drtopserv
ID: 39232536
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
 
LVL 40

Expert Comment

by:als315
ID: 39232544
I don't understand. For package type 3000 and currencyvalue 20 you have only following available:
orderHow Should we fill?
0
 

Author Comment

by:drtopserv
ID: 39232574
You ARE the BEST of the BEST!!!!!
I`ll post another continious Q :}}}
0
 

Author Closing Comment

by:drtopserv
ID: 39232576
Pefecto!~
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question