Automation Error: Unspecified Error

[akrshaw]

Good Morning!~

I've been working on this database since 3am...no sleep...ugh.

OK, I have the following On Not in List:

Private Sub Country_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rst As DAO.Recordset
Dim Msg As String, Style As Integer, Title As String, DL As String

DL = vbNewLine & vbNewLine
Msg = "'" & NewData & "' is not an available item " & DL & _
"Do you want to add this item to the current list? " & DL & _
"Click Yes To Add " & NewData & " to the list" & DL & _
"Click No to re-type it."
Style = vbQuestion + vbYesNo
Title = "Add New Item?"

If MsgBox(Msg, Style, Title) = vbNo Then
Me![Country].Undo
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("TblCountries", dbOpenDynaset)
rst.AddNew
rst![Countries] = NewData
rst.Update
Response = acDataErrAdded
End If

Set rst = Nothing
Set db = Nothing
End Sub

Works Great!~

But I have another form that uses the same Country Table that I need to be able to do a On Not in List.

I keep getting th Automation Error and the VBA screen goes straight to the above code, it will not let me input another one.

I guess this is the first time I have ever done this before!~

Is it possible? Do I just need to forgo the On Not in List on the Second Form?

Thanks!~

 

[dhookom]

It isn't clear which line is causing the error. I expect it might be caused by selecting No from the message box in which case there is no rst or db to set to nothing. Try:

Private Sub Country_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rst As DAO.Recordset
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Msg = "'" & NewData & "' is not an available item " & DL & _
         "Do you want to add this item to the current list? " & DL & _
         "Click Yes To Add " & NewData & " to the list" & DL & _
         "Click No to re-type it."
   Style = vbQuestion + vbYesNo
   Title = "Add New Item?"
   
   If MsgBox(Msg, Style, Title) = vbNo Then
       Me![Country].Undo
       Response = acDataErrContinue
   Else
      Set db = CurrentDb
      Set rst = db.OpenRecordset("TblCountries", dbOpenDynaset)
      rst.AddNew
      rst![Countries] = NewData
      rst.Update
      Response = acDataErrAdded
      [red][b]Set rst = Nothing
      Set db = Nothing[/b][/red]

   End If
   
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[akrshaw]

Duane -

I change the statement....but still getting the Automation Error when I try and point to another field in a different form to the same field.

It almost feels like it is causing a circular reference...I have never had one On Not in List link itself to another statement in a different form...

I think I am just going to remove the reference from Country...and cross that bridge when it comes...?

 

[dhookom]

Try set a break point in the code to see which line is causing the error.

Does your code compile?

Duane
Hook'D on Access
MS Access MVP