Give Null value and suppress error

[Pamela]

I have a cbo ShopName on my subform that is populated by ltblShop. I want
the the field blank if a ShopName is not applicable as this entry is later
used and a "None" entry would not work there. At this point, my first msgbox
opens confirming that there's no shop and upon "Yes" focus returns to
ShopName but the regular Access NotInList error message pops up - presumably
because it can't match " " in the list - I can tab beyond it into the next
field but I want that error suppressed. Thanks so much for your help!!
Here's my code:
If NewData = "None" Then
If MsgBox("Are you sure there's no shop?", vbYesNo, "Attention") = vbYes
Then
Me.ShopName.Undo
Me.EstimateSent.SetFocus
Else
Me.ShopName.Undo
Me.ShopName.SetFocus
End If
Else
If MsgBox(NewData & " is not in the list." & vbCrLf & _
"Are you sure you want to add " & NewData & " ? ", vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.ShopName.Undo

DoCmd.OpenForm "pfrmShop", , , , acFormAdd, acDialog
Response = acDataErrAdded

Else
Me.ShopName.Undo

Response = acDataErrContinue
End If
End If

Pamela

 

[theDBguy]

Hi Pamela,

Try adding a Response line to the first branch of your If/Then statement.
For example:

If NewData ...
If MsgBox ...
Response = acDataErrContinue
Me.ShopName.Undo
Me.EstimateSend.SetFocus
Else
Response = acDataErrContinue
Me.ShopName.Undo
Me.ShpeName.SetFocus
End If
Else
....

Hope that helps...

 

[theDBguy]

Hi Pamela,

I tried to respond to this post earlier but I don't think it went through,
so let me try again...

What I was saying was to try and insert a Response = acDataErrContinue lines
in the first branch of your If/Then statement. For example:

If NewData = "None" Then
If MsgBox ... Then
Response = acDataErrContinue
...
Else
Response = acDataErrContinue
...
End If
Else
....

Hope that helps...