Can't stop system error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm not able to stop the system error message box ("The text you entered
isn't an item in the list...") from displaying once the user adds a new price
to the list. What am I doing wrong?

Here's my code:

Private Sub cboCurrentPrice_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
'Prompt user to verify they wish to add new value.
If MsgBox("Price is not in list. Would you like to add it?", vbOKCancel)
= vbCancel Then
Response = acDataErrContinue
Me.cboCurrentPrice = Me.cboCurrentPrice.ItemData(0)
Else
'add new value to table supporting cbobox
Set rst = CurrentDb.OpenRecordset("tblProductPrices")
With rst
.AddNew
!CatalogNum = Me.CatalogNum
!LotIncrement = 0
!PriceDate = Date
!Price = NewData
.Update
.Close
End With
'inform user of addition
MsgBox "New Price successfully added!", vbExclamation + vbOKCancel,
"New Product Database"
'trigger requery of cbo and stop system err
Response = acDataErrAdded
'clean up memory
Set rst = Nothing
Exit Sub
End If
End Sub
 
The NotInList is only useful if the NewData goes into the combo's bound
column. I suspect this is not the case in this example.

Additionally, the line:
Me.cboCurrentPrice = Me.cboCurrentPrice.ItemData(0)
actually tries to change the value of the combo, and will probably trigger
the error.
 
Allen -

Thanks for your response. I commented out the line you suggested might be
causing the error and it still throws the system error. In stepping through
the code, the error isn't triggered until it hits the End Sub. I've tried
commenting out key lines with no success. The cbo's row source is another
table which has the primary key set at an autonumber field. I want to give
the user the option of adding a new price for the product. I thought if I
used the NotInList event to add a new record to the Pricing table and requery
the cbo, it would work. It actually works well...except for the system
error. Is there another event that could do the same type of thing since the
bound column is not the primary key in the cbo?
 
A nice workaround is to use the DblClick event of the combo to OpenForm so
you can add the new record to the lookup list:
Private Sub cbo1_DblClick(Cancel As Integer)
DoCmd.OpenForm "Form2", DataMode:=acFormAdd
End Sub

Then in the AfterUpdate event of Form2, requery the combo on the original
form:
Private Sub Form_AfterUpdate
If CurrentProject.AllForms("Form2").IsLoaded Then
Forms("Form2").Requery
End If
End Sub
 
Thanks a bunch. I had something similar to this in the first place and
thought I was missing the "latest technique" by not using the NotInList
event. Go figure! Thanks again!
 
Back
Top