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

 

[BruceM via AccessMonster.com]

When you say the combo box is populated by ltblShop I assume you mean its Row
Source.

The first part of the code depends on the user entering the text "None". Why
wouldn't they just leave it blank if it is supposed to be blank? BTW, " " is
a single space. Leave out the space ("") if you mean to have a zero-length
string. Make sure the table allows a zero-length string. I usually do not
allow a zero-length string, opting instead for Null. I don't see where that
comes into play in your code, but I thought I should mention it.

The way I have added an item using NotInList is based on this code:
http://www.mvps.org/access/forms/frm0015.htm
Note that you need to open a recordset. It may be possible to proceed as you
have done, but refresh the combo box in the next line after the code to open
the form, but I don't know, and don't have time right now to test.

If you are adding a record with a single field, the code in the link should
work after being adapted for the names you are using. If you are adding more
than one field to the new record you could use an input box or a series of
input boxes, or you could open a dialog form as you have done. However, when
you are done with the dialog form you need to hide it rather than close it,
so that the NotInList code can reference fields on the form. I won't go into
a lot of details without knowing some specifics. I suggest testing the code
in the link to add a single field. If that works but you need more fields,
post the code you have so far and a description of what needs to be added.

 

[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...