NotInList is giving me grief

[Barry A&P]

i have an unbound combobox that will set criteria for a query

i want to however give the option of if the value is not in the list open up
a form and add it or cancel out and clear the combo box.

heres the my code so far..

Private Sub PartNumberCombo_BeforeUpdate(Cancel As Integer)
Dim lnganswer As String
If Me.PartNumberCombo.ListIndex = -1 Then
lnganswer = MsgBox(Me!PartNumberCombo.Text & " is not in the list would you
like to add it?", vbOKCancel)
Select Case lnganswer
Case vbOK
Me.PartNumberCombo.Undo
DoCmd.OpenForm "F_PN_Listing", acNormal, "", "", acAdd, acDialog
DoCmd.Requery "PartNumberCombo"
Case vbCancel
Me.PartNumberCombo.Undo
Cancel = True
End Select
End If
End Sub

but i have two problems
if i select cancel the msgbox the combo is not cleared and i can move to the
next control without getting a warning that the value doesnt match the list

also if i select ok in the msgbox when i close the dialog form and the
requery happens i get a record must be saved first kind of error.

does anybody have a good way to handle this type of operation??

Thanks
Barry

 

[Dirk Goldgar]

i have an unbound combobox that will set criteria for a query

i want to however give the option of if the value is not in the list open
up
a form and add it or cancel out and clear the combo box.

heres the my code so far..

Private Sub PartNumberCombo_BeforeUpdate(Cancel As Integer)
Dim lnganswer As String
If Me.PartNumberCombo.ListIndex = -1 Then
lnganswer = MsgBox(Me!PartNumberCombo.Text & " is not in the list would
you
like to add it?", vbOKCancel)
Select Case lnganswer
Case vbOK
Me.PartNumberCombo.Undo
DoCmd.OpenForm "F_PN_Listing", acNormal, "", "", acAdd,
acDialog
DoCmd.Requery "PartNumberCombo"
Case vbCancel
Me.PartNumberCombo.Undo
Cancel = True
End Select
End If
End Sub

but i have two problems
if i select cancel the msgbox the combo is not cleared and i can move to
the
next control without getting a warning that the value doesnt match the
list

also if i select ok in the msgbox when i close the dialog form and the
requery happens i get a record must be saved first kind of error.

does anybody have a good way to handle this type of operation??

What you're wanting to do is exactly what the NotInList event is for. Why
try to do it in the BeforeUpdate event? You have to set the combo box's
Limit To List property to Yes; then the combo will not allow the user to
enter a value that isn't in the list, but you can use the NotInList event to
add the value if the user wants to. The code wouldn't look all that
different from what you posted, but would set the event's Response argument
to either acDataErrAdded (if you let the user add the new value) or
acDataErrContinue (if the value should be rejected). There should be
example code in the online help.

 

[Barry A&P]

Dirk
After googling the acdataerradded i found a great explination on hoe not in
list works

http://www.fontstuff.com/access/acctut20.htm

I did not know the not in list expects a certain response

Thanks for the heads up
Heres what i ended up doing

Private Sub PartNumberCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo PartNumberCombo_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Part Number " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Inventory Database")
If intAnswer = vbYes Then

DoCmd.OpenForm "F_PN_Listing", acNormal, "", "", , acDialog,
AddNewSKU(NewData)

MsgBox "The new Part Number has been added to the list." _
, vbInformation, "Inventory Database"
Response = acDataErrAdded 'Requeries combo box
Else
MsgBox "Please choose a Valid Part Number from the list." _
, vbInformation, "Inventory Database"
Response = acDataErrContinue ' Cancels and returns to open list
End If
PartNumberCombo_NotInList_Exit:
Exit Sub
PartNumberCombo_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume PartNumberCombo_NotInList_Exit
End Sub

Thanks
Barry

 

[Dirk Goldgar]

Dirk
After googling the acdataerradded i found a great explination on hoe not
in
list works

http://www.fontstuff.com/access/acctut20.htm

I did not know the not in list expects a certain response

Thanks for the heads up
Heres what i ended up doing

[snip]

Looks good, Barry. I don't see that you need the line:

Dim strSQL As String

though. I expect you can get rid of that bit of detritus.

Good work!