Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    lisarisa333 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Space Coast, FL
    Posts
    17

    Question Dlookup As Control Source Or Default Value? Actual Quantities for Inventory Database

    ****To be up front, I posted my question on UtterAccess yesterday, but have not received any replies. Hoping maybe someone here may be able to assist. https://www.utteraccess.com/forum/in...ic=2054251&hl= ****

    Hello there. I've got an inventory database that I've designed from scratch (no easy undertaking for a noob with no prior Access experience). I've gotten most everything created and functioning properly, but have recently been tasked with adding the "Actual Quantity" to the db. Trying to figure out how to incorporate it into all my forms, tables, and queries.

    I know there's a lot of info in this post. I tried to be thorough and provide sufficient info.

    Here's the "situation" :

    For inventory transactions there are two forms with subforms.
    • Receive_mainform uses TransactionQuery as its record source and is filtered for "Add" transactions. Transaction types: 1 Receive and 3 Return to Stock (RTS).
    • Issue_mainform uses TransactionQuery as its record source and is filtered for "Subtract" transactions. Transaction types: 2 Issue and 4 Return to Vendor.


    The subforms both use TransactionDetailQuery as their record source.

    I'm wondering if instead of having all 4 types of transactions together, should the received transactions (type 1) be in their own query, form or table to be able to achieve my desired result?

    There is another form that shows all the transactions for each part and the total quantity on hand as well as a breakdown of qty avail per part tag - I'll have to incorporate the actuals into this as well.

    The tables:

    TransactionType
    -TypeID - auto - PK
    -Transaction - ST
    -AddSubtract - ST

    ChargeNumber
    -ChargeID - auto - PK
    -ChargeNumber - ST
    -OrderType - ST

    PartTag
    -TagID - auto - PK
    -TagNumber - ST

    Part
    -PartID - auto - PK
    -PartNumber - ST
    -Description - ST
    -PartPic - ST

    Transaction
    -TransID - auto - PK
    -TransDate - Date
    -TypeID - Number - FK from TransactionType
    -ChargeID - Number - FK from ChargeNumber

    TransactionDetail
    -DetailsID - auto - PK
    -TransID - number - FK from Transaction
    -Order - number
    -TagID - number - FK from PartTag
    -PartID - number - FK from Part


    -Location - ST
    -TransQty - Number
    -TransQtyUOM - ST
    -TransCount - Number
    -TransCountUOM - ST
    -TransNote - ST


    Below is a combination of how the process currently works and with the functionality of the "actual" that I want to incorporate.

    Step 1 – Receiving – Transaction Type 1
    1. “Part123” is received.
    2. Part Tag number “2250” is assigned. This is a unique number (mostly, a couple of older transactions are the exception) and a crucial part of the entire process.
    3. The Quantity, Quantity UOM, Count, and Count UOM are manually input. For example:
    a. Quantity – 2
    b. Quantity UOM – pack(s)
    c. Count – 10
    d. Count UOM - each
    4. The Actual Quantity is calculated using a query expression “Quantity x Count = Actual Quantity”
    5. Actual Quantity UOM will be the same as Count UOM…pulled as a DLookup, or whatever the best method is, from the Count UOM … 2 pack(s) x 10 each = 20 each

    Click image for larger version. 

Name:	step1.JPG 
Views:	48 
Size:	37.3 KB 
ID:	38914

    ***Note) Each part can be received multiple times which means multiple part tags. Example:
    • Part123
    o Tag 2250 - 2 pack(s) @ 10 each = 20 each
    o Tag 3941 – 15 each @ 1 each = 15 each
    o Tag 4141 – 4 pack(s) @ 10 each = 40 each
    • Inventory will show the Actual Quantity of 75 each. But ideally will also break it down to show that we have 6 pack(s) of 10 each as well as the 15 each.

    Step 2 – Issuing – Transaction Type 2
    1. 13 each of Part123 is issued from the available Actual Quantity from part tag 2250.
    2. This is manually input in the field “Actual Quantity” using a hidden textbox.
    3. The Quantity UOM, Count, Count UOM, and Actual Quantity UOM, are each pulled from the received transaction for Tag 2250 using DLookup or whatever is the best method.
    4. The Quantity is calculated using VBA and the hidden textbox method, “Actual Quantity / Count = Quantity” http://allenbrowne.com/TechniqueEnterCalcText.html

    Qty : -1.3 - calculated
    QtyUOM : packs - DLookup?
    Count : 10 - DLookup?
    CountUOM : each - DLookup?
    ActualQty : -13 - manually entered in a hidden textbox on top of the query expression field
    ActualQtyUOM : each - DLookup?

    Click image for larger version. 

Name:	step2.JPG 
Views:	48 
Size:	67.7 KB 
ID:	38912



    Step 3 – Return to Stock (RTS) – Transaction Type 3 (essentially the same as Step 2, except this is a positive transaction)
    1. 3 each of Part123 is Returned to Stock to the same part tag it was issued from, 2250.
    2. This is manually input in the field “Actual Quantity” using a hidden textbox.
    3. The Quantity UOM, Count, Count UOM, and Actual Quantity UOM, are each pulled from the received transaction for Tag 2250 using DLookup or whatever is the best method.
    4. The Quantity is calculated using VBA and the hidden textbox method, “Actual Quantity / Count = Quantity” http://allenbrowne.com/TechniqueEnterCalcText.html

    Qty : 0.3 - calculated
    QtyUOM : packs - DLookup?
    Count : 10 - DLookup?
    CountUOM : each - DLookup?
    ActualQty : 3 - manually entered in a hidden textbox on top of the query expression field
    ActualQtyUOM : each - DLookup?

    Click image for larger version. 

Name:	step3.JPG 
Views:	45 
Size:	63.8 KB 
ID:	38913

    Inventory would reflect
    Part123 / TagNumber 2250
    Receive 2 packs x 10 each = 20 each
    Issue -1.3 packs x 10 each = -13 each
    RTS 0.3 packs x 10 each = 3 each
    On Hand 1 packs x 10 each = 10 each


    If you've gotten all the way down here, I thank you so much for reading and for any assistance or guidance you may be able to provide =)

    -Lisa

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    How do you manually enter data in a hidden textbox? Wouldn't a dSum for the tag number give you what you need (quantity on hand) if transaction 1 and 3 are positive and 2 and 4 are negative: dSum("[Qty]"*"[Count]","[TransactionDetail]","[TagID] =" & Forms!frmYourForm.....)?

    Can you remove the data from the tables and upload a sample with a couple dummy records?

    Cheers,
    Vlad

  3. #3
    lisarisa333 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Space Coast, FL
    Posts
    17
    Quote Originally Posted by Gicu View Post
    How do you manually enter data in a hidden textbox? Wouldn't a dSum for the tag number give you what you need (quantity on hand) if transaction 1 and 3 are positive and 2 and 4 are negative: dSum("[Qty]"*"[Count]","[TransactionDetail]","[TagID] =" & Forms!frmYourForm.....)?

    Can you remove the data from the tables and upload a sample with a couple dummy records?

    Cheers,
    Vlad

    Vlad,

    Sorry it's taken me so long to respond. Maybe my verbiage isn't correct regarding the "hidden" textbox. It's an unbound textbox that sits on top of a bound textbox. My problem isn't so much with the sum portion of it, as I had that working prior to the addition of "actuals". Needing to figure out how to get QtyUOM, Count, CountUOM from the received transaction to populate automatically based on the tag number when I'm going to enter other transactions that aren't received.

    I'm attaching a copy of the database with some dummy records.

    Thanks for your input! =)
    Attached Files Attached Files
    Last edited by lisarisa333; 06-28-2019 at 07:57 AM. Reason: Corrected Dummy Database

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Lisa,

    Got the db, have a look at the issue_subform (I believe that is where you wanted that to happen). I created a new query to make it easier to get the "received" info for the tags. Please note that I commented out some code on the subform as it was not compiling.

    Cheers,
    Vlad
    Attached Files Attached Files

  5. #5
    lisarisa333 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Space Coast, FL
    Posts
    17
    Quote Originally Posted by Gicu View Post
    Hi Lisa,

    Got the db, have a look at the issue_subform (I believe that is where you wanted that to happen). I created a new query to make it easier to get the "received" info for the tags. Please note that I commented out some code on the subform as it was not compiling.

    Cheers,
    Vlad

    Vlad, can I just say.... YOU ARE THE MAN!! Thank you so much. I absolutely needed that to happen in the issue_subform.

    I do need it to happen in the received_subform as well. Only for the Return to Stock (RTS) transaction types. How do you recommend I achieve that goal?

    Many many thanks! =)
    Lisa

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Lisa,
    Just adjust the code in the Afterupdate event of the tagid combo of the issue subform, it should be quite similar, just add a check for the transaction type (=2).
    Cheers,
    Vlad

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Lisa.
    Have a look at the attached, not fully there but should get you on the right track. Remove the defaults you set for the text boxes and see how it works.
    Attached Files Attached Files

  8. #8
    lisarisa333 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Space Coast, FL
    Posts
    17
    That's so perfect. Thanks a million!! Now I'm having trouble getting the unbound textbox thing to work...http://allenbrowne.com/TechniqueEnterCalcText.html
    I know I've got to change quite a bit of that code to adapt to my needs, but I'm missing something. Do you have time to take a look at what I've done so far and see if you can tell what I'm doing wrong?

    Thank you =)

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you please let me know which one exactly (name of control and name of the form)?

    Cheers,
    Vlad

  10. #10
    lisarisa333 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Space Coast, FL
    Posts
    17
    Vlad,

    For the Receive transactions, all data is manually input. The transaction qty, uom, trans count, uom, and then the actual quantity is calculated.

    For all other transactions, most of the rec'd data is populated using the DLookup you helped me with. I'd like to use the unbound text box method so that I may enter the actual quantity and then calculate: transqty = actual quantity/transcount.

    Receive_subform will only need this for the RTS transactions. Issue_subform - it will always need to be this way. The control name, on each form, is txtActualQuantity and txtActualQuantityENTRY. Not sure if I made the ENTRY textbox on the version of my db you have.


    My initial post may give a better idea / overall picture of what I'm trying to accomplish. I hope I'm making sense, but I'm not so sure. :/



    Thank you,
    Lisa

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you please have a look at the two subforms and let me know if that is what you wanted?

    Cheers,
    Vlad
    Attached Files Attached Files

  12. #12
    lisarisa333 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Space Coast, FL
    Posts
    17
    Hi Vlad,

    Hope you had an excellent weekend. Thanks for your help with this! I saw your note about checking my control names - trying to figure out which I had issues with.

    I'm not seeing that the RTS transaction is working on the Receive_subform. Also I'm noticing that on the Issue_subform, if you enter a number in the quantity, and then change the number for some reason, it will not recalculate. Do you think it just needs a simple requery somewhere?

    Many thanks,

    Lisa

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Lisa,

    Have a look at the code, I specifically added a check to only do it when the box is empty (not to recalculate so you could manually over write). You can just take that check out - If IsNull(Me.txtTransQty) - and it will do it all the time. For the RTS please check the code also and look if I have referenced the right controls, as I said, the control names don't seem to be consistent. Post back if you still need help

    Cheers,
    Vlad

  14. #14
    lisarisa333 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Space Coast, FL
    Posts
    17
    Hey Vlad,

    I'm working on checking control names, etc. But in the mean time, I am curious...

    I noticed with the unbound textbox method there are basically 3 steps / events for each unbound textbox.

    1) Set Focus 2) Copy Value 3) Calculate.

    The receive_subform looks like it's only set up to do the calculate step; I'm working on adding the other two. The issue_subform you commented out the 2nd step. Are all 3 actually not required? I'm still pretty darn new to Access, so I'm not always sure how things are related and intertwine, etc. I was basically just copying from http://allenbrowne.com/TechniqueEnterCalcText.html and trying to input my controls. Not very successfully! LoL.

    I've been super overwhelmed and frustrated trying to get this dang thing done. You've been so much help, I truly appreciate it. I feel like I'm finally getting somewhere!

    Thanks,
    Lisa

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Lisa,

    The beauty of Access is just that, it is so (relatively) easy to set it up just right. Allen's example is a "catch all" try to show what's possible, you need to decide how you want it the data entry to happen. Are they ever going to enter any data into txtTransQty box? Is that manual entry meant to "overwrite" the calculated value (like a damaged box with only 5 usable items from the total 6).

    So my suggestion is to put your thoughts/ business rules down on paper and think what you want to happen for each possible outcome (Yes//No/ Cancel) of your user interacting with your form(s). Once you have that it should be a simple matter of implementing the rules in VBA and we can help you with that.

    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-02-2016, 12:37 PM
  2. Dlookup as control source on continuous form.
    By Ramun_Flame in forum Programming
    Replies: 4
    Last Post: 10-24-2012, 10:26 AM
  3. DLookup in Control Source
    By bgephart in forum Forms
    Replies: 2
    Last Post: 08-28-2012, 02:06 PM
  4. DLookup() for Text Box Control Source
    By tylerg11 in forum Forms
    Replies: 3
    Last Post: 02-06-2012, 02:21 PM
  5. Dlookup as Control Source
    By alsoto in forum Forms
    Replies: 1
    Last Post: 08-28-2011, 07:05 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums