By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,544 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Access Form Question about record verification

P: 16
I have a form and data is already imported from a previous step, but at this point one can check a box and all the information that is regarding to that check box will be added to another screen. For example, Tom, Katie, and Sonya all bought a red apple. When I check all the boxes for red apples it will send it to another form that will enable me to print out all the customers who bought a red apple. But sometimes when people are clicking a box they will click too many boxes. For example, they clicked all the red apple boxes but then clicked an orange as well. So when I go to print the customer list it will have that one person who bought an orange. Is there a way where I can have VBA check to see if all the items that are checked have the same item purchased? It would run the code and then it would say in a message box "Not all items are the same product type. Are you sure you would like to continue?". I can do a message box but I do not know how to make the message box pop up when an item has been selected incorrectly.
Feb 19 '14 #1

✓ answered by ADezii

I think that I have a good understanding as to what the OP is requesting. Here is how I interpret it:
  1. Set the Tag Property of ALL Check Boxes to represent the Category to which they are assigned, namely: Apples Check Boxes should have a Tag Property set to Apples, Grapes Check Boxes should have a Tag Property set to Grapes, etc.
  2. If a Check Box does not belong to any Category, set its Tap Property to N/A.
  3. Loop through every Control on the Form.
  4. If the Control is a Selected Check Box with a Tag Property of anything but N/A, set the Tag of the 1st Check Box to a Variable. All other valid Check Boxes should now equate to this String Value, namely: Apples, Grapes, Peaches, etc.
  5. If there is one Check Box that does not match such as: 3 Apples and 1 Grape, notify the User and exit the Routine.
  6. If all is well, confirmation will be via a Comma Delimited String with all the same Values, such as: Apples,Apples,Apples.
  7. Hopefully this is what you are looking for, any questions feel free to ask.
    Expand|Select|Wrap|Line Numbers
    1. Dim ctl As Control
    2. Dim strTag As String
    3. Dim intCtr As Integer
    4. Dim strBuild As String
    5.  
    6. For Each ctl In Me.Controls
    7.   With ctl
    8.     If .ControlType = acCheckBox Then           'Is the Control a Check Box
    9.       If .Value = True And .Tag <> "N/A" Then   'Is it Selected and does it have a Valid Tag?
    10.         intCtr = intCtr + 1                     'Check Box Counter
    11.           If intCtr = 1 Then strTag = .Tag      'Retrieve Tag of 1st Check Box
    12.             If .Tag <> strTag Then              'Tags are not the same
    13.               MsgBox "All Items selected must be of the same Type", _
    14.                      vbExclamation, "Improper Selection"
    15.                      strBuild = ""
    16.                 Exit For                        'Get outta here!
    17.             Else
    18.               strBuild = strBuild & .Tag & ","  'Items that have been 'cleared'
    19.             End If
    20.       End If
    21.     End If
    22.   End With
    23. Next
    24.  
    25. 'Confirmation on Items to make sure they are all the same
    26. If strBuild <> "" Then MsgBox Left$(strBuild, Len(strBuild) - 1)

Share this Question
Share on Google+
3 Replies


dsatino
100+
P: 393
You're really going to need to show some visuals of your forms and code to get any feed back on this.

On the surface, it sounds like you're getting more elaborate with your forms/code then you need to be.
Feb 19 '14 #2

NeoPa
Expert Mod 15k+
P: 31,186
Please understand that forms do not ever hold data. They simply display it.

Understanding what you've tried to explain therefore, is very difficult. It makes little sense, and leaves a lot to guesswork of what you mean.
Feb 20 '14 #3

ADezii
Expert 5K+
P: 8,597
I think that I have a good understanding as to what the OP is requesting. Here is how I interpret it:
  1. Set the Tag Property of ALL Check Boxes to represent the Category to which they are assigned, namely: Apples Check Boxes should have a Tag Property set to Apples, Grapes Check Boxes should have a Tag Property set to Grapes, etc.
  2. If a Check Box does not belong to any Category, set its Tap Property to N/A.
  3. Loop through every Control on the Form.
  4. If the Control is a Selected Check Box with a Tag Property of anything but N/A, set the Tag of the 1st Check Box to a Variable. All other valid Check Boxes should now equate to this String Value, namely: Apples, Grapes, Peaches, etc.
  5. If there is one Check Box that does not match such as: 3 Apples and 1 Grape, notify the User and exit the Routine.
  6. If all is well, confirmation will be via a Comma Delimited String with all the same Values, such as: Apples,Apples,Apples.
  7. Hopefully this is what you are looking for, any questions feel free to ask.
    Expand|Select|Wrap|Line Numbers
    1. Dim ctl As Control
    2. Dim strTag As String
    3. Dim intCtr As Integer
    4. Dim strBuild As String
    5.  
    6. For Each ctl In Me.Controls
    7.   With ctl
    8.     If .ControlType = acCheckBox Then           'Is the Control a Check Box
    9.       If .Value = True And .Tag <> "N/A" Then   'Is it Selected and does it have a Valid Tag?
    10.         intCtr = intCtr + 1                     'Check Box Counter
    11.           If intCtr = 1 Then strTag = .Tag      'Retrieve Tag of 1st Check Box
    12.             If .Tag <> strTag Then              'Tags are not the same
    13.               MsgBox "All Items selected must be of the same Type", _
    14.                      vbExclamation, "Improper Selection"
    15.                      strBuild = ""
    16.                 Exit For                        'Get outta here!
    17.             Else
    18.               strBuild = strBuild & .Tag & ","  'Items that have been 'cleared'
    19.             End If
    20.       End If
    21.     End If
    22.   End With
    23. Next
    24.  
    25. 'Confirmation on Items to make sure they are all the same
    26. If strBuild <> "" Then MsgBox Left$(strBuild, Len(strBuild) - 1)
Feb 20 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.