how should I do...

[Lhuffst]

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")
       cmbDEType.SetFocus
    Else
        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")
     cmbScrappedMeterSize.SetFocus
  else
   SMSelected = False
End If

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

 

[fdcusa]

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.

 

[Andrzejek]

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.

 

[Lhuffst]

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.

[b]Private Sub cmbCategory_AfterUpdate()[/b]
'lock the fields based on selection

On Error GoTo tagError

CmbCategory.BackColor = 16777215     'white
g_lastCategory = CmbCategory.Column(0)
TxtCategory = CmbCategory.Column(1)[b][b][/b][/b]
 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


tagError:

'   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)
[b]Private Sub CmdNew_Click()[/b]
' 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