Problem with NotInList event (1 Viewer)

bob fitz

AWF VIP
Local time
Today, 10:49
Joined
May 23, 2011
Messages
4,717
I have the following code in the NotInList event of an unbound combo box but the combo doesn't recognize added data unless it is selected from the list. It just repeats the message saying that the new data is not in the list :unsure:.
I have also attached a db to illustrate the problem.
Code:
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
    If MsgBox("Add '" & NewData & "' to the list?", vbQuestion + vbYesNo, "Confirmation Required") = vbYes Then
    
        CurrentDb.Execute "insert into tblHrRate (HrRate) VALUES ( """ & NewData & """ );"
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

End Sub
 

Attachments

  • Database1.accdb
    384 KB · Views: 405

Gasman

Enthusiastic Amateur
Local time
Today, 10:49
Joined
Sep 21, 2011
Messages
14,050
Bob,
I have only ever used NIL once in once DB of mine,, and cannot see anything different with yours.?
However you are executing
insert into tblHrRate (HrRate) VALUES ( "65" );
insert into tblHrRate (HrRate) VALUES ( "77" );
when the rate is currency?

Edit: No that did not fix it :(
 

bob fitz

AWF VIP
Local time
Today, 10:49
Joined
May 23, 2011
Messages
4,717
Bob,
I have only ever used NIL once in once DB of mine,, and cannot see anything different with yours.?
However you are executing
insert into tblHrRate (HrRate) VALUES ( "65" );
insert into tblHrRate (HrRate) VALUES ( "77" );
when the rate is currency?

Edit: No that did not fix it :(
Gasman
Thanks for taking an interest.
I've used the NIL event many times before and the only time I've had a problem is when I've done something silly like missing out a line like
Response = acDataErrAdded

I was just hoping that I'd done something similar this time without being able to see it. I have a lot of "Senior Moments" like that these days:ROFLMAO:.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:49
Joined
Sep 21, 2011
Messages
14,050
Bob, if it is general number, it works, so perhaps investigate there?
I was using strings in my NIL
 

bob fitz

AWF VIP
Local time
Today, 10:49
Joined
May 23, 2011
Messages
4,717
Bob, if it is general number, it works, so perhaps investigate there?
I was using strings in my NIL
OK. Thanks very much. I thought I'd used it with currency before but that might just be another of those "Senior Moments" that I mentioned earlier. I'll have a little play with it ;). Thanks again.
 

Minty

AWF VIP
Local time
Today, 10:49
Joined
Jul 26, 2013
Messages
10,355
It's something to do with the values. Even if I do this;
Code:
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
        
    Dim iId As Long
    Dim db As Database
    
    Set db = CurrentDb
    If MsgBox("Add '" & NewData & "' to the list?", vbQuestion + vbYesNo, "Confirmation Required") = vbYes Then
    
        db.Execute "insert into tblHrRate (HrRate) VALUES ( """ & NewData & """ );", dbSeeChanges
        iId = db.OpenRecordset("SELECT @@IDENTITY From tblHrRate").Fields(0)
        
        Response = acDataErrAdded
        Me.Combo0 = iId

    Else
        Response = acDataErrContinue
    End If
    
   Set db = Nothing
  
 
End Sub

I get the not in list message again once.
I tried using

Me.Combo0.Text = NewData

And that didn't work either.
Interesting problem / bug.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:49
Joined
Sep 21, 2011
Messages
14,050
Yet it adds the data to the table?
Setting the format to general in the table, gets past the error?
 

bob fitz

AWF VIP
Local time
Today, 10:49
Joined
May 23, 2011
Messages
4,717
It's something to do with the values. Even if I do this;
Code:
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
       
    Dim iId As Long
    Dim db As Database
   
    Set db = CurrentDb
    If MsgBox("Add '" & NewData & "' to the list?", vbQuestion + vbYesNo, "Confirmation Required") = vbYes Then
   
        db.Execute "insert into tblHrRate (HrRate) VALUES ( """ & NewData & """ );", dbSeeChanges
        iId = db.OpenRecordset("SELECT @@IDENTITY From tblHrRate").Fields(0)
       
        Response = acDataErrAdded
        Me.Combo0 = iId

    Else
        Response = acDataErrContinue
    End If
   
   Set db = Nothing
 

End Sub

I get the not in list message again once.
I tried using

Me.Combo0.Text = NewData

And that didn't work either.
Interesting problem / bug.
Minty
Thanks for your interest and input on this.
I've overcome the problem by changing the field type in the table from Currency to Double but I am surprised. After all, what is currency if not a number?:unsure:
Strange that it gets added to the table but acts as though the Response = has not been set.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:49
Joined
Sep 21, 2011
Messages
14,050
Bob,
You can leave the field alone, and just change the format if that is better?
 

bob fitz

AWF VIP
Local time
Today, 10:49
Joined
May 23, 2011
Messages
4,717
Bob,
You can leave the field alone, and just change the format if that is better?
Yes, that works as well, but I think I'll just change the field type to Double.

Once again, my sincere thanks to everyone that has responded.
This would appear to be another little oddity for us all to watch out for.
I can now move forward with the app that I'm working on.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:49
Joined
Oct 29, 2018
Messages
21,358
Yes, that works as well, but I think I'll just change the field type to Double.

Once again, my sincere thanks to everyone that has responded.
This would appear to be another little oddity for us all to watch out for.
I can now move forward with the app that I'm working on.
Hi Bob. Glad to hear you got it sorted out. I'm just responding so I can bookmark this thread to check your file later when I'm in front of a computer.

Sent from phone...
 

bob fitz

AWF VIP
Local time
Today, 10:49
Joined
May 23, 2011
Messages
4,717
Hi Bob. Glad to hear you got it sorted out. I'm just responding so I can bookmark this thread to check your file later when I'm in front of a computer.

Sent from phone...
theDBguy
Thanks for your interest.
I look forward to your observations and any comments that may have.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:49
Joined
Oct 29, 2018
Messages
21,358
theDBguy
Thanks for your interest.
I look forward to your observations and any comments that may have.
Hi Bob. Sorry for the delay... I just took a quick look at your file. Since I don't have the Pound symbol on my keyboard, I removed the formatting from your table, so it will default to $ for me.

When I entered only numbers in the dropdown box, I see the problem you described. However, if I enter the $ symbol first and then followed by the number, I don't get any issues.

So, you could give that a try in your original file. Try entering the Pound sign first before entering a new number/amount.

If that works, then I guess it has something to do with what Access sees as NewData, which could mean a Text value if the money symbol is being included in the evaluation.

Just my 2 cents...
 

Users who are viewing this thread

Top Bottom