Preventing deletions from a combo box

[jdspinelli]

Good Morning:

I have a bound form for editing data. An unbound combo box is available for the user to choose a record. The intent is for the user to select from the combo box values and immediately move to the record in the underlying table. I have accomplished this using the Change event for the combo. However, if the user deletes the entry in the combo, I receive the "invalid use of null" error which locks up Access and I must close down. Using the Nz function to prevent the error does not trap the error in the change event. I can use a separate "Select Record" command button that works well. However, is there a way to 1) trap the "invalid use of null" error, and 2) place processing in an event procedure (to move to the requested record) for the combo box without leaving the combo box control?
Thanks,
jdspinelli

 

[dhookom]

Can you provide the code you used? Typically I would expect any code to run in the After Update rather than the Change event.

Duane
Hook'D on Access
MS Access MVP

 

[jdspinelli]

Sure can provide the code (see below). I will see if I can make it work in the AfterUpdate event.

many thanks,
joe

Private Sub cbProjNameSelect_Change()
On Error GoTo ErrorHandle

Dim vDispData As Variant

Application.Echo False

vDispData = Me.Recordset.MoveFirst
With Me.Recordset
Me.Recordset.MoveFirst
Do While Not .EOF
If Me.Recordset.CurrIntakeID = CInt(Me.cbProjNameSelect) Then
Me.Recordset.Requery
Exit Do
Else
Me.Recordset.MoveNext
End If
Loop
End With

Application.Echo True

RoutineExit:
Exit Sub
ErrorHandle:
MsgBox Err.Description
GoTo RoutineExit
End Sub

 

[dhookom]

I'm not sure why you didn't use the code created by the combo box wizard that finds a record in the form.

You could wrap most of your code in

If Not IsNull(Me.cbProjNameSelect) Then
  '...
End If

Keep in mind the On Change event fires with every keystroke. That's typically not what you want.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya jdspinelli . . .

When you delete a record you need to [blue]requery[/blue] the combobox so the record doesn't show and can no longer be selected. An Ideal spot for this would be the forms [blue]On Current[/blue] event:

[blue]   Me.[purple][b][i]ComboboxName[/i][/b][/purple].Requery[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[jdspinelli]

Thanks to both of you, Gents:

I will incorporate your input!
Joe