NotInList Edit Form Question

[480hoyle]

I am a novice user, and do not know how to code at all. I plan to use this to track grievance filings and case status. I have attached a zip file with mock data.



I want to be able to use the Grievant combo box on my form to add the last name of a person to my people list--edit on-the-fly. I am able to add the person but then when I go to save the record, I get an error response saying the changes duplicate values in index, primary key or relationship. My properties allow list items to edit the form.

Before I changed the properties to allow list items to edit, I had to go to the other form (People) to input the person. The issue with this was that every time I did that, the name I chose from the combo box on the Grievances form would show on every Grievance file (the same name for every file; it was the most recently chosen name, but that name would be placed on every file).

I would appreciate any suggestions--Thank you!

[ranman256]

you get this error because of the table key.
you tried to enter a duplicate and the keys are preventing you.

in the tStates table, keyed on StateCode, if TX exists, you cannot add TX again. (youll get that msg)
so check your keys or your data being added.

[480hoyle]

ranman256--thanks for your quick reply. I get this response only when I ADD a member not already included in the table. If I choose a name that already exists, using the combo box, that same name is populated for every single grievance file.

[June7]

Grievant combobox is unbound. There is no NotInList event code. Review http://www.fontstuff.com/access/acctut20.htm

Should not save People lastname into Grievances - save their CustID. A customer ID based on name parts is bound to fail someday. Example:

Jane Roberts = jrober

James Robertson = jrober

Julia Roberts = jrober


I don't use macros, only VBA.

[480hoyle]

Thanks for your response. I want to use the Grievant's last name as a front facing searchable item for my director--we identify grievances either by file number or grievant last name when we are referring to them in conversation. He and I will be the only two using the db. I was told by someone who knows more than I do about Access that I should use an unbound box for this. That didn't make much sense to me, and I ended up with an endless circle back--that's when I decided to try the NotInList property. I would welcome any suggestions at this point--I just need to be able to see the Grievant's last name on this form. I can create a record of them in the people form, save that record, return to the grievances form and add their last name in, but if it exists already, the same grievant name shows up on every single grievance file I have.

For PK and other background: The CustID is the same as an individual's unique user login for the university, so I can't imagine it being duplicated for those within the institution. The data used in this example is all mock data. For those who are parties to a grievance and who do not have a university login (limited--I think perhaps there have been 6 in the four years I have worked here), I will use a combination of their name parts and the first file number they are associated with to eliminate duplicates (i.e. 99jarober). File numbers are determined by using year filed followed by letter (i.e. 2018-A, 2018-B, etc...). Typically, we have less than a dozen filed, but there a numerous dates to track, and we would like to be able to easily find data down the road as far a demographics, units, etc.

As you can see, this will be a very tiny database, but it will provide us lots of historical information as well as an easy way to show our work product. I greatly appreciate any suggestions.