Adding Items To Combo Boxes

Hey Guy and Ladies,

In one database (Originally an Orders Database created by wizards)

This code to add items to a drop down list in the “orders details subform” works fine.


Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!ProductID.Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub

When the same code runs in my new database

Private Sub OptionID_DblClick(Cancel As Integer)
On Error GoTo Err_OptionID_DblClick
Dim lngOptionID As Long

If IsNull(Me![OptionID]) Then
Me![OptionID].Text = ""
Else
lngOptionID = Me![OptionID]
Me![OptionID] = Null
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!OptionID.Requery
If lngOptionID <> 0 Then Me![OptionID] = lngOptionID

Exit_OptionID_DblClick:
Exit Sub

Err_OptionID_DblClick:
MsgBox Err.Description
Resume Exit_OptionID_DblClick
End Sub

I get the error message

“You tried to assign the Null value to a variable that is not a Variant date type”

Any suggestions?

Also if anyone knows a better way to add items to a list box “On the Fly” I would really appreciate it.

It would be nice if you could type in the value and it would automatically add it to the list for future use.

Thanks for all of your help,
Joe