Can't turn off Access Warning Message

[BJS]

I want to suppress the Access Warning message when this code is run. No matter where I place my "docmd.setwarnings false", I still get the Access message.

What am I doing wrong???
This code is within the "Not-In-List" Event of a combo box.

Dim ans As String

DoCmd.SetWarnings False

ans = MsgBox("This item is not in the list. Do you wish to add it?", vbYesNo, "Add Item")

If ans = vbYes Then

Me.cboVesselID = Null
DoCmd.OpenForm "frmCountryMaintain", acNormal, , , acFormAdd
DoCmd.GoToControl "txtDescription"

Else

[Forms]![frmShipments]![cboCountry] = Null
DoCmd.GoToControl "cboProductID"

End If

DoCmd.SetWarnings True

Thanks
BJS

 

[Mile-O]

Forget the DoCmd.SetWarnings; you are not getting a warning but an error.

If they not want to add:

Response = acDataErrContinue

If they do:

Response = acDataErrAdded

 

[BJS]

Thanks Mile-O-Phile!

I wasn't sure how to apply your suggestion to my code, so I looked it up in Help and re-wrote my code as follows, but now, when the user wants to add the item, I get the error:

"Characters found after end of SQL Statement".

Here is the new code:

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!cboVesselID
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
DoCmd.GoToControl "cboSourceID"
End If

End Sub


What is wrong now?
Or, how would I add your two lines of code to my existing code?

 

[Mile-O]

I suspect that this is what you really want.

 

[BJS]

Mile-O-Phile - The following code in the link you provided works great, but I'm having a hell of a time trying to convert the code to use ADO instead.....I've been trying to find examples, but can't get it going:

What do I need to change to make it work with ADO?


Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

If MsgBox(NewData & " is not in the selection provided. Would you like to add it?", vbQuestion + vbYesNo, "Unknown Course") = vbYes Then
With rs
.AddNew
.Fields("MyField") = StrConv(NewData, vbProperCase)
.Update
.Close
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Set rs = Nothing
Set db = Nothing

 

[BJS]

I found some sample code and figured out how to re-write the DAO to ADO. It works, but I'll need more practice with this.

I can't seem to find a consistent/standard way of writing it though!

THANKS A BUNCH FOR YOUR HELP MILE-O-PHILE!!!!!!

Here is my re-written code with ADO:

Dim conDatabase As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ctl As Control

Set ctl = Me.cboVesselID


Set conDatabase = CurrentProject.Connection
strSQL = "SELECT Description FROM tblVessels"

Set rst = New Recordset
rst.Open strSQL, conDatabase, _
adOpenDynamic, adLockOptimistic

If MsgBox(NewData & " is not in the selection provided. Would you like to add it?", vbQuestion + vbYesNo, "Item Not In List") = vbYes Then
With rst
.AddNew
.Fields("Description") = StrConv(NewData, vbProperCase)
.Update
.Close
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctl.Undo
Forms!frmShipments.SetFocus
DoCmd.GoToControl "cboSourceId"

End If

Set rst = Nothing

 

[frios8102]

Hello,

I could fix this by importing a macro from an earlier version of Access which contained two actions (one first and one last):

1. Setwarnings (Off)
2. Setwarnings (On)

Just put any action between these two and will work.

Hope this helps