Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262

    Complex function with conditional message boxes, calculations, auto adding in form/table

    This can get complex pretty quick due to the endless combinations of cable building x_x Ill try to explain this in steps.



    In a nutshell, I want to perform some human error checking on a table (specific field) to add things to my table that may not have otherwise been added. For example, if someone completes a parts list, and there are transitions that do not call out tape, the user must add tape to the parts list, even though it is not called out specifically on the list of parts given (it may be hidden in some drawing footnotes). If tape needs to be added, it will be calculated depending on the number of different transitions (3 legged, 4 legged, 5 legged etc), of which I want to be calculated before adding it to the parts list. A message box with a yes or no option would suffice for entering tape and such. My problem is that I'm not sure how to go from a pop up to an addition to a table, or autofilling my form would be ok too.

    1) If the word "transition" is in any record via the Description field, have a pop up that says "Transitions found. Add tape to the parts list?"

    2) On yes, enter a customer specified tape. Otherwise, set a default tape part number.

    3) Have a place (either on the same pop up or another pop up) where user enters # of 3 legged transitions, # of 4 legged transitions etc up to about 8 legged transitions. About 3 inches per leg * number of legs * # of x legged transitions = amount of tape needed in inches.

    4) Add this amount for every transition type and convert to FT.

    5) Confirm tape selection, append to table.

    I might have a second pop up for epoxy on transitions as well.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,508
    A VBA intrinsic InputBox can be used to capture user input and pass into field. But why not just controls on form for data entry? User inputs transition choice and quantity. The number of feet is calculated when needed using the raw inputs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    I don't have room for more controls, hehe. If an inputbox can fill it's info into already existing controls in my form, I'll be happy with that.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,508
    Too many controls? Maybe a Tab Control to organize the form?

    The problem with InputBox is validating the input. If you want a number, how to prevent user entering alpha?

    An example:
    Dim intReponse As Variant
    Do
    intResponse = InputBox("Enter hours for cost charging.", "Check Cost", 30)
    Loop Until IsNumeric(intResponse)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    By Tab Control you don't mean a Tab Index, right?

    Yeah that would do fine. How do I transfer inputbox info to the form though?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,508
    I mean a Tab Control.

    Me!fieldname = intResponse
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Ah ok.

    So how and where would I implement this in the code?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,508
    Include that additional line just below the Loop Until line. Of course, use your field name.

    The code needs to go in some event. I am not sure which in your situation. Not sure what you mean by 'error checking on a table'. Are you checking user input as they are doing data entry?

    Post your attempted code for analysis if you encounter issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Ah my bad. It will be attached to a button. Forgot about that I'll give it a try.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,508
    So you want to depend on user to click button to trigger prompts? What if they forget to click?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Then they're ignorant for thinking they remembered every little detail? hehe

    I could make the button obnoxious in some way so that they have to click it.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,508
    Many ways to accomplish. I don't know your business process so can't be specific.

    One way is to provide textboxes on form for data entry. Then when they try to save the record, code does a check and if determines data is missing, cancels the save and returns user to form. Users eventually learn what not to forget and they won't get annoying popups.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Unfortunately all of our customers are different. Some customers will include tape and consumables and such in their parts list. Some list them in footnotes on the original drawings, and some don't mention it at all. For someone who doesn't know how to put together a BOM, this can get expensive because of parts that are missing.

    It's difficult to automate something with so much variation, and so I'm trying to compile some concrete rules to automate, such as transitions always having some sort of tape to hold them in place and protect the cables. In this case, the customer might tell us what kind of tape they want, in which case that will be on the BOM. Other times, the customer won't even mention tape even though it is needed in the application. I have a general idea of how much is needed, but that is dependent on the number of transitions, how many legs each transition has etc. I'd rather do this check at the end because it will make adding the consumable easier to code.

    For example, a job has 5 types of transitions. Do I really want to keep modifying the amount of tape needed every time I come across a transition? Or can I wait until all 5 are entered with their respective quantities, and then glance at the drawing and enter how many 3 legged trans, 4 legged trans, etc., then calculate in one fell swoop how much tape I need, and append it to my BOM? The more transitions, the more tedious this can get.

    To me, it's a question of efficiency, and in my mind the latter suggestion is more efficient. Code to check for missing data would be too expensive due to the variation of situations. For example, I might remember to add tape to the list if it's needed, in which case I can just hit No on the pop up asking me if I need to add tape or not. In this process, it would have to be under the user's discretion whether or not to add consumables if they're needed, hence the pop up.

    However, if I can manage to modify the same part number (record) after every check if needed like you suggested, then I am open to the idea.

  14. #14
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    One thing that might help is when we complete our BOM generation in Access, we export it to an Excel sheet. Perhaps this check can be done prior to exporting? An export button with pre-exportation error checking perhaps?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,508
    Don't see why not but does sound complicated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-19-2014, 04:51 PM
  2. Replies: 1
    Last Post: 11-10-2013, 08:53 AM
  3. Adding calculations to a form.
    By FFLKing in forum Access
    Replies: 3
    Last Post: 06-01-2013, 12:25 AM
  4. Replies: 7
    Last Post: 09-12-2011, 12:03 PM
  5. complex calculations like in Excel
    By jacko311 in forum Database Design
    Replies: 2
    Last Post: 11-11-2009, 05:51 PM

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 - Senior Forums