Solved

The Text you entered isn't an item on the list, when it is message

Posted on 2009-06-26
23
728 Views
Last Modified: 2013-11-27
During a sequence of adding records on the fly, after I try to close out of the second level of entry I  get a message telling me that the text I have entered is not an item on the list. I have tried checking references and shutting off allow auto correct, and changing the Response = acDataErrContinue settings, all to no avail. I believe it has something to do with data types but I can't seem to figure it out.  Here is the sequence:

1. From a main form, I want to enter text into a field called Topic via a combo box; if my topic is not there, I use the not in list event to add one on the fly.
2. This opens a form called Add Topics, into which I enter the new topic name.
3.  Each topic needs a job number, so I look that up with a combo box, and use the not-in-list event to add new job numbers on the fly.
4. Each new job number needs to be associated with a client, so I likewise select the client name from a combo box, and add new clients on the fly using the not-in-list function.

Every thing works except in step 3 above, after I have successfully entered the job number and either selected existing clients or added new clients I get the "the test you entered is not...." message. When I go back and check the tables, the data is in there, but my workflow is a train wreck? Any ideas for starters? I can add code in the next go 'round. Thanks


0
Comment
Question by:jlnero
  • 12
  • 11
23 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 24726186
Is this combobox a bound control? My guess the problem has to do with the data type of the field in the bound column and the data type of the field in the control source.
0
 

Author Comment

by:jlnero
ID: 24727244
It is a bound control. The data type of the bound column is "autonumber", which is what it is on the other events that seem to be working.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24727408
The value in an autonumber field is created automatically and you cannot change any data in autonumber fields.   So if you are trying to assign the value for the autonumber field in your not-in-list, it won't work and you will get the not-in-list message.  Post your code in the not-in-list event for more specific help.

It is a bad idea to allow users to see the autonumber or to use it for any purpose other than internal database reference. One reason for this is at some time, a person with decision making authority will say "lets change the format of the Job numbers to..."  At this point, you have several thousand autonumbers that are referenced by numerous other tables so you now have a mess on Your hands. You should use a separate field for your "job Numbers".  
0
 

Author Comment

by:jlnero
ID: 24727481
Thanks -- I must have gotten spoiled by the fact that the auto-numbered fields worked on some of the others, but I see how that could be bad practice. Should I get my unique id from a calculated field and bind to that?
Dim cbo As ComboBox, strMsg As String

    

    Set cbo = Me!ClientID

    strMsg = "This Client is not on the list.  Would you like to add it?"

    If MsgBox(strMsg, vbOKCancel) = vbOK Then

        Response = acDataErrAdded

        DoCmd.OpenForm "Clients", , , , acFormAdd, acDialog, NewData

    Else

        cbo.Undo

        Response = acDataErrContinue

    

    End If

Open in new window

0
 
LVL 39

Expert Comment

by:thenelson
ID: 24727559
The line:
Response = acDataErrAdded
is firing the "not-in-list" message. From VBA help:

acDataErrAdded Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by requerying the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message.

<Should I get my unique id from a calculated field and bind to that?>
By "unique id" do you mean for example the job number? Create a new field in your table for job number. Index it with no duplicates allowed. In the not-in-list event for the combobox bound to that field use Dmax() + 1 to assign a new number. Now you can use acDataErrAdded.  Do not use this field to relate tables together; use the autonumber field for that.
0
 

Author Comment

by:jlnero
ID: 24727807
Ok -- I've created the new field and indexed it, but am not sure where in the not-in-list event to put the Dmax()+1 expression.  Could I put it in the default of the table design?
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24727946
<Could I put it in the default of the table design?>
You could but I wouldn't recommend it because I have run into situations where that did not add the value to the new field. Better to put it in the not-in-list event.  For example (if Form "Clients" needs the job number - not knowing what is in "NewData"):


Private Sub cboJobNumber_NotInList(NewData As String, Response As Integer)
cboJobNumber = DMax("lngJobNumber", "YourTable") + 1
DoCmd.OpenForm "Clients", , , , acFormAdd, acDialog, cboJobNumber
Response = acDataErrAdded
End Sub
0
 

Author Comment

by:jlnero
ID: 24728836
I guess I am missing some step. There are two forms involved. One is called Add Job Number, which adds information to a TABLE called Job Number. The others is Clients, which adds values to a table of the same name.

Add Job Number has a look up to the Table Clients an the field ClientID, and summons the Client form on a not-in-list event. It is the ClientID autonumbered field that is causing the problem

I created a new field call ClientNum and added it to the Clients table, but that doesn't seem to be getting me anywhere.  Which tables and forms need the new field. Do I need to create lookup relationship from the Job Numbers table to the Clients table?

Thanks for your patience.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24729153
<Which tables and forms need the new field. >
You would create another field if you need to have reference numbers used for other purposes. In the medical industry, I need to save ID numbers for each insurance company for each patient.  For some God only knows reason, some insurance companies want to use our patient ID number! So I generate a patient ID Number in the database. When I am working with a client, they sometimes insist on Job numbers, customer numbers, Inventory numbers, etc. I try to dissuade them because, with computers, you can search on anything to find the correct record. But sometimes they insist and I then use a separate field. If I have complete control over a database design, I do not provide the user with any ID numbers - I only use ID Numbers internally to relates two records of different tables.

Now, about your original question:
I guess I have been unintentionally confusing you since I was not clear about your setup. Sorry about that. Your last message helped clear that up for me. Before going forward, let me make sure I understand you.

On the form "Add Job Number", you have a combobox that stores the ClientID that's called ClientID. The row source of cboClientID is a query that pulls data (including ClientID) from the Clients table.  In the not-in-list event of cboClientID, you popup the form Clients which adds a new record in the Clients table.  If that is correct, then the not-in-list event  for cboClientID you posted is almost correct.  You just have
        Response = acDataErrAdded
        DoCmd.OpenForm "Clients", , , , acFormAdd, acDialog, NewData
reversed. It should be:
Dim strMsg As String
   
    strMsg = "This Client is not on the list.  Would you like to add it?"
    If MsgBox(strMsg, vbOKCancel) = vbOK Then
        DoCmd.OpenForm "Clients", , , , acFormAdd, acDialog, NewData
        Response = acDataErrAdded
    Else
        Me!ClientID.Undo
        Response = acDataErrContinue
   
    End If

Although you can use the
Dim cbo As ComboBox
    Set cbo = Me!ClientID
    cbo.Undo
you don't need to. This will do it:
Me!ClientID.Undo
or even this:
ClientID.Undo





0
 

Author Comment

by:jlnero
ID: 24729394
Your assumptions are correct, I used the code you suggested and still get the same error. I tried going back to the original and reversing the order, but still get the same error. I think I do need unique IDs, because this is a CRM type application in which I have to lump transactions and records to a specific opportunity and have them all disappear simultaneously when the main opportunity is closed.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24729509
Do you want to post the database and have me look at it? Make a copy and delete any confidential info before posting it.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:jlnero
ID: 24729768
It's 17 mb and not easily split. I would prefer not to post the whole database but could share with you through an FTP site or desktop sharing. if you want to do that, send an email to jlnero@gmail.com and I will send you a link.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24729890
I would prefer the ftp site or email the db. if you make a copy of the db, delete all the tables, forms and reports that don't apply and delete all but a half a dozen records in the tables, then compres, the db should trim to 1-2 meg. My email actress is in my profile.
0
 

Author Comment

by:jlnero
ID: 24731160
Just sent FTP instructions to you via email... thanks.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24731689
Get a bunch of honey dos today. Will get to it later this afternoon or evening.
0
 

Author Comment

by:jlnero
ID: 24736997
Sent file this morning -- let me know if it didn't make it.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24737276
Didn't get it yet. Didn't see it in my spam filter.
0
 

Author Comment

by:jlnero
ID: 24738073
Just sent it again -- both by office email and gmail. One should work.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24739594
Got it will look it shortly.
0
 
LVL 39

Accepted Solution

by:
thenelson earned 500 total points
ID: 24765755
I fixed the problem for you in Invensys Results Manager.  You had two problems.
        DoCmd.OpenForm "Clients", , , , acFormAdd, acDialog, NewData
        Response = acDataErrAdded
were reversed and you were not setting Client in the clients form to

NewData in the Add Job Number form.  This code in the clients form does that:
Private Sub Form_Load()
If Me.OpenArgs <> "" Then Client = Me.OpenArgs
End Sub

In Invensys Results Manager2, I made the Clients form hidden and close so the user doesnt see it with this code:
Private Sub Form_Load()
If Me.OpenArgs <> "" Then
    Me.Visible = False
    Client = Me.OpenArgs
    DoCmd.Close acForm, Me.Name, acSaveYes
End If
End Sub

In Invensys Results3, I used an append query to add the so the clients form is not needed at all:
        CurrentDb.Execute "Insert Into Clients (Client, Active) Values ('" & NewData & "', False)"
        Response = acDataErrAdded

0
 

Author Comment

by:jlnero
ID: 24766954
Thanks -- your first solution solved the problem, but the second and third seem much more elegant and I will probably go with them. Thanks much.
0
 

Author Closing Comment

by:jlnero
ID: 31597079
Certainly a case of providing a good, better, and best option. Thanks much.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24767496
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now