NotinList problem

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
Have you single stepped the code to see which error you are getting?
 
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
 
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.

Tony said:
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?
Click to expand...
[quoted text clipped - 42 lines]
Click to expand...
 
Hi thanks for that I get
Error3265
Description Item not found in this collection
Any help?
Thanks
Tony
ruralguy via AccessMonster.com said:
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.

Tony said:
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?
Click to expand...
[quoted text clipped - 42 lines]
Thanks
Tony
Click to expand...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
1)
I wondered whether the error was because the code couldn't find the new
table.
Click to expand...
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.
 
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
 
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
 
Back
Top