Validating combo boxes

[mnevitt]

I have a form that has 2 combo boxes that I need to make sure an item is selected in before the record is upated. I have the following code in the BeforeUpdate event. The first "if" statement works, the message box pops up, then the focus is set to that control and I select an item. The next time i try to activate the BeforeUpdate event to test the second box i get the error:

"The Microsoft Jet database engine cannot find a record in the table 'tblBoro' with the key matching field(s) 'loc_boro'"

The second combo box that is being validated contains the items listed in tblBoro - a boro id (autonumber) and the boro (manhattan, bronx, etc). The table is linked to the main table, tblLocation, that has a numeric field, loc_boro.

Private sub form_BeforeUpdate(cancel as integer)
on error goto err_before
 
     if Me.cmbCommunity = "" or IsNull(Me.cmbCommunity) Then
          MsgBox "You must select a community."
          GoTo Exit_Before
 
     ElseIf  Me.cmbBoro = "" or IsNull(Me.cmbBoro) Then
          MsgBox "You must selet a county"
          GoTo Exit_Before
 
     End If
 
Exit_Before:
Exit Sub
 
Err_Before:
MsgBox Err.Description
Resume Exit_Before
 
End Sub

Thanks for your help.

Mike

 

[wazz]

looks like the error doesn't have to do with the combobox but the form. seems you're trying to add a record with a boro but the boro needs a loc_boro (a key field).

 

[mnevitt]

Ok that makes sense. When you select nothing in either combo box you get similar errors. My goal is to prevent the record add if there is nothing in both combo boxes. In other words something must be selected in each before you add. The code catches the first box but not the second.

And loc_boro is a foerign key to the PK in the tblBoro.

 

[missinglinq]

I think each of these needs to be in their own If...End If constructs, since one has nothing to do with the other! Also,rather than the GoTo Exit_Before which is simply exiting the sub, you need to use Cancel = True to cancel the update then move focus back to the appropriate combobox:

If Me.cmbCommunity = "" or IsNull(Me.cmbCommunity) Then
   MsgBox "You must select a community."
   Cancel = True
   Me.cmbCommunity.SetFocus
End If 

If  Me.cmbBoro = "" or IsNull(Me.cmbBoro) Then
  MsgBox "You must select a county"
  Cancel = True
 Me.cmbBoro.SetFocus
End If