Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


how should I do...

how should I do...

how should I do...

If I have a form that has multiple categories (selections), how would I check and save only the ones that they selected? They don't have to fill in every category each time.
I set Booleans for each category and when they select the group I set the Boolean to true.
On the save button, I check if that Boolean is true (they selected it), make sure that at least one of the fields has a value

I can do this for one category but what if the person selected 3 out of the 11 possible categories. How do I check to make sure that at least one field per selection has a value and then save all of the data at one time. If there are not any values, pop them back to the section missing data and then try the save again.
Here is what I have so far


If DESelected Then
    If IsNull(cmbDEType) And IsNull(txtDataEntrySheets) Then
       nill = MsgBox("you must fill in at least one item in the Data Entry Section", vbCritical, "Missing data")
        DESelected = False      'reset
    End If
End If
If SMSelected Then
    If IsNull(cmbScrappedMeterSize) And IsNull(cmbBronze) And IsNull(txtNoMetersScrapped) Then
     nill = MsgBox("you must fill in at least one item in the Scrapped Meters Section", vbCritical, "Missing data")
   SMSelected = False
End If

  'if all selections are ok, then save
If DESelected Or SMSelected Then
    MsgBox "Almost there"
    'save it
      DoCmd.GoToRecord , , acNewRec
End If 

RE: how should I do...

From your description it sounds like you have repeating groups in your table - where Category is repeated in multiple fields with a variation in the name, such as a numbered index or additional text at the end.

The correct way to do this is have a child table to your primary table, adding a Foreign Key in your child table to the primary table's Primary Key. Then in your form, create a subform with the recordsource set to the child table and the Subform Control's Master/Child Links set appropriately (on PK/FK). Then your subform can have a combobox control with rowsource set to the different categories available for selection. Typically these would be stored in a Lookup Table that has its own Primary Key, the Category, and perhaps a SortOrder/Rank field if you want to control the order of the selections. The Primary Key of the Category would be stored, not the text.

RE: how should I do...

How do the users select the categories? How do you know which categories they selected? Do you have check boxes for them to check for the categories they want to use?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: how should I do...

Hi fdusa and andrzejek
the code above represents the rectangle that surrounds each type of meter action taken. There are really 11 sections so I did a Boolean for each selected section.
If you select a section, then on the save I make sure you fill in the fields to make sure you don't save blank records. the code that I have below works fine but I was trying to make more efficient code.
I couldn't do a select case because each section has it's own Boolean check (deselected, smselected etc).


current code when you initially select the category.

Private Sub cmbCategory_AfterUpdate()
'lock the fields based on selection

On Error GoTo tagError

CmbCategory.BackColor = 16777215     'white
g_lastCategory = CmbCategory.Column(0)
TxtCategory = CmbCategory.Column(1)
 Dim ctl As Control, subctl As Control
Select Case CmbCategory.Column(1)
  Case 1    'Data Entry/CSIS
            DESelected = True       'set flag for saving
             Call LockSections("DataEntryLock", True)
             Call ctrlBorderChange("BxDataEntry", True)
  Case 2    'Scrapped Meters
            SMSelected = True
            Call LockSections("ScrappedLock", True)
            Call ctrlBorderChange("BxScrappedMeters", True)
  Case 3    'YesNos
            YNelected = True
            Call LockSections("yesnolock", True)
            Call ctrlBorderChange("BxYesNo", True)
 Case 4    'MeterTestingResults
            MTSelected = True
            Call LockSections("MTLock", True)
            Call ctrlBorderChange("BxMeterTestingResults", True)
  Case 5    'CrateCut
            MCSelected = True
            Call LockSections("MCLock", True)
            Call ctrlBorderChange("BxCrateCut", True)
  Case 6    'EquipmentRepair
            ERSelected = True
            Call LockSections("ERLock", True)
            Call ctrlBorderChange("BxEquipRepair", True)
  Case 7    'Fire Hydrant
            FHSelected = True
            Call LockSections("FHLock", True)
            Call ctrlBorderChange("BxFireHydrant", True)
    Case 8   'Meters Processed
            MPSelected = True
            Call LockSections("MPLock", True)
            Call ctrlBorderChange("BxMP", True)
  Case 9    'Meters In Stock
            MSSelected = True
            Call LockSections("MSLock", True)
            Call ctrlBorderChange("BxMS", True)
  Case 10    'Large Meters
            LMSelected = True
            Call LockSections("LMLock", True)
            Call ctrlBorderChange("BXLM", True)
  Case 11    'Returned Meters
            RMSelected = True
            Call LockSections("RMLock", True)
            Call ctrlBorderChange("BxRM", True)
End Select
Debug.Print g_BoxName


'   MsgBox "Error " & Err.Number & " (" & Err.Description & _
'         ") in procedure Form_Open of VBA Document Form_Transaction Header"
' Exit Sub

End Sub 


current code when you click the next button (ultimately saves the record)
Private Sub CmdNew_Click()
' DoCmd.GoToRecord , , acNewRec
 Dim Border As String

'make sure that catergory, date added, entered by and sub by is filled in
'necessary in order to save a record without key info being blank

Call checkBlueBox
If CKSlected Then

Select Case CmbCategory.Column(1)
  Case 1    'Data Entry/CSIS
              Call ResetBorderColor("BxDataEntry", True)
  Case 2    'Scrapped Meters
             Call ResetBorderColor("BxScrappedMeters", True)
             'Call saveit
  Case 3    'YesNos
            Call ResetBorderColor("BxYesNo", True)
 Case 4    'MeterTestingResults
            Call ResetBorderColor("BxMeterTestingResults", True)
  Case 5    'CrateCut
            Call ResetBorderColor("BxCrateCut", True)
  Case 6    'EquipmentRepair
            Call ResetBorderColor("BxEquipRepair", True)
  Case 7    'Fire Hydrant
            Call ResetBorderColor("BxFireHydrant", True)
  Case 8   'Meters Processed
            Call ResetBorderColor("BxMP", True)
  Case 9    'Meters In Stock
             Call ResetBorderColor("BxMS", True)
  Case 10    'Large Meters
            Call ResetBorderColor("BxLM", True)
  Case 11    'Returned Meters
            Call ResetBorderColor("BxRM", True)

End Select
'now save the selected sections
Call saveit
End If

End Sub 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close