NotInList Problem (1 Viewer)

bob fitz

AWF VIP
Local time
Today, 11:12
Joined
May 23, 2011
Messages
4,717
Can someone please look at this and tell me what I'm doing wrong.

The code adds the NewData to the table but I get a message which tells me, "The text you entered isn't an item in the list"

Code:
Private Sub cboCusFilter_NotInList(NewData As String, Response As Integer)
Dim rec As Recordset
    If MsgBox("Add '" & StrConv(NewData, vbProperCase) & "' to the list of Customers?", vbYesNo, "Confirmation Required") = vbYes Then
'        DoCmd.RunSQL "INSERT INTO tblCus (CusName) Select (""" & StrConv(NewData, vbProperCase) & """)"
        Set rec = CurrentDb.OpenRecordset("tblCus")
        With rec
            .AddNew
            .Fields("CusName") = StrConv(NewData, vbProperCase)
            .Update
            .Close
        End With
        Set rec = Nothing
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub
I think I must be having a "Senior Moment" because as far as I can see this is how I've always handled the NotInList event :confused:
 

bob fitz

AWF VIP
Local time
Today, 11:12
Joined
May 23, 2011
Messages
4,717
Solved !!

I was indeed having a "Senior Moment". :eek:
I was trying to use the code in a combo designed to find a customer record rather than the combo which is used to create a new record for a customer :rolleyes:

Sorry to have wasted the time of anyone that has been kind enough to take a look a wonder. I know Gasman had a look at this.
 

isladogs

MVP / VIP
Local time
Today, 11:12
Joined
Jan 14, 2017
Messages
18,186
LOL. whether a senior moment or not, you're either psychic...or have been viewing Who's Online :D

Why do you need the recordset code at all?
You are inserting the new item using proper case with your sql statement.
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 11:12
Joined
May 23, 2011
Messages
4,717
Well, I may be septic but I'm certainly not psychic :D. I had a little peek at Who's Online ;)

The INSERT statement was actually commented out in the posted code but doesn't show very well.

To be honest, I've always used the recordset method purely because that's how I learnt to do it from the book that I taught myself from all those years ago.

I've only recently tried using the INSERT SQL statement but because I'm not so familiar with it, I commented it out and went back to the recordset when I ran into problems this morning.

Now that I've sorted myself out, I'll go back to the SQL method as it uses so much less code.

Thanks for your interest:)
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:12
Joined
Sep 21, 2011
Messages
14,038
Solved !!

I was indeed having a "Senior Moment". :eek:
I was trying to use the code in a combo designed to find a customer record rather than the combo which is used to create a new record for a customer :rolleyes:

Sorry to have wasted the time of anyone that has been kind enough to take a look a wonder. I know Gasman had a look at this.

I did, and I looked at what I did in the few times I have used it, but could not offer anything, so kept quiet. :D
 

bob fitz

AWF VIP
Local time
Today, 11:12
Joined
May 23, 2011
Messages
4,717
I did, and I looked at what I did in the few times I have used it, but could not offer anything, so kept quiet. :D
Well, I must have used similar code literately thousands of times in the last 25 years which is why I was so puzzled. It wasn't till I started to rebuild the form (because I thought something must have corrupted) that I realised my mistake.

Now I'm not confused but I am a little embarrassed :eek:.

Anyway, thanks again for your interest.
 

Users who are viewing this thread

Top Bottom