Notinlist disabled when doing copy/paste append from excel

[MushMouse]

When I enter data into a datasheet manually, my notinlist code works fine, but if I try to copy & paste append from an excel spreadsheet, invalid data can be entered - my notinlist seems to be disabled under those circumstances.
Any advice would be most appreciated!

 

[rjoubert]

Datasheet? Are you talking about an Excel spreadsheet that you're entering data into, or do you mean an Access table? If you're talking about pasting Excel data into another Excel spreadsheet, you may want to post your question to the Microsoft Office forum...

http://www.tek-tips.com/threadminder.cfm?pid=68...this

forum is for Access.

Where is your notinlist code? Could you post the code?

 

[MushMouse]

I am attempting to paste from an excel spreadsheet into an access subform.

The call to the NotInList module is as follows:
Private Sub FinishCombo_NotInList(NewData As String, Response As Integer)
Call ComboNotInList(NewData, Response, "Finish", "Finishes", "", "")
End Sub

The NotInListModule is as follows:

Option Compare Database
Option Explicit
Public Sub ComboNotInList(ByVal vstrNew As String, ByRef rintResponse As Integer, _
ByVal vstrField As String, ByVal vstrTable As String, varWhere As Variant, ByVal vstrArgs As String)
If vstrNew <> LTrim(vstrNew) Then
MsgBox "Leading blanks are not allowed"
' vstrNew = LTrim(vstrNew)
rintResponse = acDataErrContinue
Exit Sub
End If
If InStr(1, vstrNew, "'") <> 0 Or InStr(1, vstrNew, """") <> 0 Then
MsgBox "Quotes may not be entered here"
rintResponse = acDataErrContinue
Exit Sub
End If
If vstrArgs = "NoAdd" Then
rintResponse = MsgBox("Please pick a number from the list or hit the 'esc' key twice to return to previous value", 0, vstrField & " is not valid")
rintResponse = acDataErrContinue
Exit Sub
End If
If vstrField = "RegCat" Then
rintResponse = MsgBox("Category is not valid. Would you like to add it?", vbYesNo)
Else
rintResponse = MsgBox(vstrField & " is not valid. Would you like to add it?", vbYesNo)
End If
If rintResponse = vbYes Then
Dim dbs As Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(vstrTable)
rst.AddNew
If vstrTable = "Companies" Then

rst!ShortName = vstrNew
rst!CompanyName = vstrNew
If vstrArgs <> "COMPANIE" Then
rst(vstrArgs) = True
End If
Else

rst(vstrField).Value = vstrNew
If vstrField = "RegCat" Then
rst("RegCatID") = vstrNew
End If
End If
If vstrTable = "Terms" Then
If varWhere = "Sales" Then
rst!TermType = 2
Else
rst!TermType = 1
End If
End If

rst.Update
rst.Close
dbs.Close
If vstrTable = "Gifts" Then
DoCmd.OpenForm "Gifts Form", , , "[Gift]='" & vstrNew & "'"
End If
'If vstrField = "Payee/Payor" Then
' DoCmd.OpenForm "Payee Category Form", , , "[ShortName]='" & vstrNew & "'"
'End If
rintResponse = acDataErrAdded
vstrNew = ""
Else
rintResponse = acDataErrContinue
End If
End Sub

 

[rjoubert]

The first thing I would suggest trying is to put a MsgBox call at the beginning of your FinishCombo_NotInList procedure to see if it's actually being called when you are pasting data into your subform. If you don't see the MsgBox pop up when you paste, you may want to take a look at different events from which you can call your code.

 

[TheAceMan1]

How are ya MushMouse . . .

. . . and if you hit [blue]Enter[/blue] or [blue]Tab[/blue] after you paste?

See Ya! . . . . . .

 

[MushMouse]

rjoubert - I put the msgbox where you suggested. No message appeared, so it's not being called when I paste. But I can't imagine why not...

TheAceMan1 - I tried hitting enter & tab after pasting. It had no effect.

 

[TheAceMan1]

Roger That MushMouse . . .

[blue]I tried hitting enter & tab after pasting. It had no effect.[/blue]

If you indeed have the [blue]Limit To List[/blue] property set to [purple]Yes[/purple] . . . then I'm forced to say you have some kind of corruption!

With the [blue]Limit To List[/blue] property set to [purple]Yes[/purple], this puts that event before the [blue]Before/After Update[/blue] of the combo.

I'm not quite sure what to tell you to do at this point other than [blue] delete and reinstantiate the combo![/blue]

The problem is not with your code . . . its that the [blue]On Not In List[/blue] event is not being triggered!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[MushMouse]

I tried deleting & reinstating the combo. It had no effect.
I was afraid there might be some kind of corruption - because I believe this used to work.
The problem is, this is happening to every combo box in my system!
Do you have any suggestions on how I might be able to cure the corruption?

 

[MushMouse]

This problem is happening to ALL my databases!
Maybe I should reinstall office xp....

 

[MushMouse]

I uninstalled & reinstalled microsoft office & added all updates - it made no difference!

 

[missinglinq]

The first thought that occured to me when reading this post was that you were trying to do an awful lot with one sub, MushMouse. You've stated that with manual data entry the NotInList functions correctly. You've deleted and rebuilt your combobox with no luck. Now you say all of your comboboxes are exhibiting the same problem. I think you probably need to dump and rewrite your proceedure Public Sub ComboNotInList. If it were me, I'd rewrite it one step at a time, then test it before adding the next step. Write the part to check for leading spaces and check it, write the part looking for quotation marks and test it, etc. It's simply too easy to miss something in a prceedure this complicated.

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

MushMouse . . .

Wow! . . . put a breakpoint on the 1st line of the NotInList event. Do your testing . . . Does the code break?

See Ya! . . . . . .