NotinList problem

[Tony Williams]

I have a combobox on a form where the values come from a table. I have the
columns limited to 1 and the bound column is 1. Here is my code for the
NotinList Event:

Private Sub Force_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Value."
strMsg = strMsg & " Do you want to add the new Value to the current
List?"
strMsg = strMsg & " Click Yes to Add or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("RecoveryForce", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!RecoveryForce = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub

I use this code on a number of comboboxes on my form but for some reason
that I cannot find when I try to add a value to this list I get the "An
error occurred. Please try again." message.
The database is split and the tables are in an mde file.

Can anyone see what could be wrong?
Thanks
Tony

 

[ruralguy via AccessMonster.com]

Have you single stepped the code to see which error you are getting?

 

[Tony Williams]

No am a novice with VBA how do I do that?
Incidentally I've added the new table to the mde file since I split the
database, would that make any difference? I wondered whether the error was
because the code couldn't find the new table.
Cheers
Tony

 

[ruralguy via AccessMonster.com]

Change your error code to:

If Err Then
MsgBox "An error occurred. Please try again." & vbCrLf & vbCrLf & _
"Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description

Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

Then we can see what error you are dealing with.

No am a novice with VBA how do I do that?
Incidentally I've added the new table to the mde file since I split the
database, would that make any difference? I wondered whether the error was
because the code couldn't find the new table.
Cheers
Tony

Have you single stepped the code to see which error you are getting?

[quoted text clipped - 42 lines]

 

[Tony Williams]

Hi thanks for that I get
Error3265
Description Item not found in this collection
Any help?
Thanks
Tony

Change your error code to:

If Err Then
MsgBox "An error occurred. Please try again." & vbCrLf & vbCrLf & _
"Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description

Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

Then we can see what error you are dealing with.

No am a novice with VBA how do I do that?
Incidentally I've added the new table to the mde file since I split the
database, would that make any difference? I wondered whether the error was
because the code couldn't find the new table.
Cheers
Tony

Have you single stepped the code to see which error you are getting?

[quoted text clipped - 42 lines]

Thanks
Tony

 

[George Nicholson]

1)

I wondered whether the error was because the code couldn't find the new
table.

If you didn't create a link after adding the table then yes, this could
certainly be the problem. If RecoveryForce doesn't appear in the list of
tables, then you need to create a link to the new table:
(File>GetExternalData>Link)

2)
(if #1 doesn't solve the problem)
Can you manually add a new record to the table, filling in just the one
field with the desired data, and not get an error?

i.e.:
Are there any other required fields in that table that also need to be added
at the same time as RecoveryForce?
Are there any validation rules in effect that are being violated?
Do NewData and RecoveryForce have the same Datatypes (text vs numeric,
square peg vs round hole)?

3)
there is usually very little point in turning a backend into an mde.
Creating an mde removes the ability to make design changes to forms, reports
and code. Typically, none (or very few) of those things reside in the
backend, so turning it into an mde pretty much does nothing. mde creation is
usually reserved for frontends.

 

[Dale Fye]

Tony,

I have encountered similar problems with NotInList events. The way I worked
around it was to set the recordsource of the combo box to "" before I try to
insert the new record in the table. Then, once that value has been added to
the table, set the recordsource of the combo box back to it's original value
and then set the value of the combo box = NewData

Don't know why that works, but it has for me.

HTH
Dale

 

[Tony Williams]

Thanks guys I'll try all these suggestions. It's 19.49 in the uk here and I
think my 63 year old brain has gone to sleep so I'll try tomorrow and post
back with my results
Thanks again
Cheers
Tony