AfterUpdate - Not in List

[Dave Eyley]

I've found what seems to be a problem.

I have a form on which a user can use an unbound Combo box to select an item from the list.

I don't use the 'Not in List' facility - instead I wrote a short piece of code that compares the entry with the table to see if it exists already and if not, it asks the user if it should be added, if the response is in the affirmative then the routine adds the item to the table and requeries the combo Rowsource.

It works most of the time....there doesn't appear to be any problem with the code. But randomly and about 20% of the time, the routing stops with an error message. 3420 - Object invalid or no longer set...

the code in question is -

COMBO - AFTERUPDATE


Dim MyDbX As Database, MySetX As Recordset, Flag As Integer
Set MyDbX = DBEngine.Workspaces(0).Databases(0)
Set MySetX = MyDbX.OpenRecordset("ItemList")

MySetX.MoveFirst

Do Until MySetX.EOF
If MySetX![ItemDesc] = Me!ItemDesc Then Flag = 1
MySetX.MoveNext
Loop

If Flag = 0 Then
Dim MyReply As Integer
MyReply = MsgBox("This Item is not in the stored list." & Chr$(10) & Chr$(10) & "Do You want to add it?", vbYesNo, "New Item?")
If MyReply = 7 Then
Me!ItemDesc = Null
MySetX.Close
MyDbX.Close
Else
MySetX.AddNew
MySetX![ItemDesc] = Me!ItemDesc
MySetX.Update
MySetX.Close
MyDbX.Close
Forms!LostNew!ItemDesc.Requery
MsgBox "New Item added", vbInformation, "Confirmed Addition"
End If
End If


This is bugging me - as it works some of the time with no external changes apparent.

Can't even blame a network for this one....it's stand-alone

Dave E

 

[Rich]

Why not just use the NotInList, what's the advantage with your code?

 

[The_Doc_Man]

"Flag" is Dim'd but not actually defined. Which means it might start life as NULL sometimes?

Try adding

Flag = 0

before the search loop.

 

[Dave Eyley]

Thanks for the responses...

The Flag isn't the problem..and I write the routine for adding items to the list because I think it's more user-friendly.

Seven out of ten times the routine works fine..and I mean when the form is open repeatedly adding new items, so there can't be any differences in the state of the database or its objects....

The routine, when it drops out with the error, stops on the line -

MySetX.AddNew


It's almost as if the database recordset decides to randomly close...but for no apparent reason.

Dave E

 

[Mile-O]

Seven out of ten times the routine works fine

Then use the NotInList event and it will work ten out of ten times.

I've written code that does what you want using the NotInList event for both ADO and DAO methods in the FAQ forum.

 

[Dave Eyley]

Thanks - Mile-o-phile.
I take on board what you say but it doesn't explain why it's happening.

I have noticed differences in the behaviour in Access XP and Access 97. I wonder if this is just another quirky difference.

Dave E

 

[Dave Eyley]

Well, you'd never believe what the problem was.....

The routine was fine...no problem...worked a treat. But I had added a clock display to the main menu screen which refreshed every second - thought I was being clever!

Have now reset refresh time to 60 seconds and all's well...again.


Dave Eyley

 

[Mile-O]

Why do you have a clock?

 

[Rich]

Why do you use slow code in preference to built in methods???

 

[Dave Eyley]

Is there a quicker or more efficient way to display a clock in a form?

Why worry about slow code when speed isn't an issue?



Dave E

 

[Mile-O]

Is there a quicker or more efficient way to display a clock in a form?

What's the need? There's a clock in the system tray with system time already set aside for it. Are your users incapable of looking at that?

 

[Mile-O]

Why worry about slow code when speed isn't an issue?

Why start bad habits?

 

[Dave Eyley]

The reason for the clock is that the application is a Security Desk Information database and pretty much everything is time related - they want a clock on the screen they can see from across the room - they get a clock on the screen they can see from across the room...who am I to argue with the client?

Looked at the FAQ for NotinList and it was no different from my own so I dismissed this avenue....

Thanks for all you help and suggestions.....

Dave Eyley

 

[Rich]

Looked at the FAQ for NotinList and it was no different from my own so I dismissed this avenue....

Dave Eyley

The combo box already has the recordset open, your code opens it again and then loops through it with vba, I'd say that was a lot different

 

[Dave Eyley]

I wouldn't...they both worked, eventually.....


As I said earlier, the problem was elsewhere; not the routine...but the forum did confirm that it had to be something else - not the code. And that's the useful bit...

Thanks again

 

[Mile-O]

I wouldn't...they both worked, eventually

Why wouldn't you say that they were different methods?

The combobox on the form currently has the recordset open in order to display the records in its dropdown.

Using the NotInList event restricts the user from entering something not in the recordset and gives you the opportunity to add it.

Using your method opens the recordset for a second time, wastes time by going through all the records again, and then gives the opportunity to add it. Your process will take longer and longer the more records you add.

 

[Rich]

I wouldn't...they both worked, eventually.....


Thanks again

There's that laid back Westcountry attitude

 

[Dave Eyley]

The money I'm on, I can afford to be laid back....



Dave E