Access Form - Combo Bpx

How many fields does your combo box have? In order for this to happen you'll need to set the [Limit To List] property to False. Then on the combobox's AfterUpdate event you will need to check the selected value to see if it's in your list. If it's not in your list then add it to the list.

What is the rowsource for your combobox? Is it a table or query? Or is it a list of values?

Not with the method you've been using in your other posts.  You will need all eight fields to populate a record unless you followed my original advice to normalize the database and create separate tables for each lookup.

The NotInList event can be used to open a data entry form that would allow you to enter new values but again, I doubt that method will work easily for you with an unnormalized schema.

Also, allowing people to add entries on the fly like this simply leads to sloppiness.  If they make a typo, they'll just add a "duplicate" entry.  The whole point of combos is to control the data and prevent typos.  Making it too easy to accept a typo defeats the purpose.

well so I created a separate search form and now want an add form to just add the new supplier. The process is they are first searching for the supplier, then if it is not found adding a new supplier. The first supplier id field will always be a new id, however, the immediate or ultimate parent name may exist.

See attached. I did some more work on it.
Example1v2mod2.accdb

First I have to say that I agree with what Pat stated above.

I am also not sure what purpose that form (or the "multi-field Search") serves?
(Add?, Search, View?)

I am also confused as to design and purpose of your main table...
It looks "un-normalized"

Perhaps you could take a step back and explain that main table fully?

The main table is currently an Excel sheet that my team manually updates with Supplier information. They receive other files monthly and currently do vlookups to maintain it.

I am trying to put the process into access. In addition, they need to be able to add suppliers manually and/or edit them. This way they will no longer need to do it in Excel.

I want to take this out of Excel since it gets confusing when 10 people are all editing their suppliers. We have over 100k. I realize the main table isnt normalized however if I break it apart I am not sure what purpose that serves since it all needs to go back together. For instance, I can use a query to make a table for the following:

1) Suppliers and IDs
2) Normalized Supplier and IDs
3) Immediate Parent and IDs
4) Ultimate Parent and IDs

However, if I make the above table I still need to store the id in the main table so it is all linked together. Not sure what the benefit is. It is  a hierarchy

Maybe this will help. Here  is a real world example. This is the excel sheet we have now.
sample.xlsx

For one thing, normalizing will mean you have only a single instance for each Name field instead of thousands.  That will reduce the physical size of the table plus it would allow you to change a single row if a supplier name changed.  Your current method would require changes to thousands of records for a name change.  Granted companies don't change their names frequently but they do change them.  Your current method relies on peoples typing skills.  Every time they enter a new relationship for an existing company, they must type the name EXACTLY as it was typed in other rows.

Each of the tables has a foreign key to its parent.  You join the four tables to get your current flat record.

Your real world example highlights the existing problem.  Surely Frito Lay and Frito Lay LLC are the same company, why would you want to have separate totals for them.

If Frito Lay is the real name, then using combos would not allow anyone to enter Frito Lay LLC.  Unless of course, you give them an easy way to enter "typos".

they are coming in under separate invoices. One invoice may say vendor 1, the other vendor 2. Both are to be added and added up. That is why the normalized vendor name says frito lay, but the spend for both is still valid and attached to 2 separate invoices.

when we pull the spend for frito lay now we can use the normalized vendor name column. That is the point. Or if i need the entire spend for Pepsi, I can use the ultimate parent column

Also the reason I need the combo box when they add a supplier is so they can search before they add any new supplier. The normalized, immediate, and ultimate names are the cleansed version of our raw data

Because you are thinking with your spreadsheet cap on, you can't separate yourself from the "report" you see in the spreadsheet.  This is NOT how you would keep the data in a properly normalized relational database.

Sounds like you need something of a cross reference of valid company names that you could use for the data entry of the invoices.  We do something similar at one of my clients.  They are third party suppliers of electricity and gas.  They deal with utility companies in many states and all their data is processed by a service bureau.  Because of this, thee may be variations on any given utility's name and also, utilities change names but not all the historical data gets changed.  So there is a cross reference of company name to companyID.  In your case both variations of Frito Lay would equate to the same companyID.  That is ALL the company info  that anyone would ever enter to record the invoice.  The other fields like the parent and ultimate parent are simply grouping levels for reporting.  Those groupings are done in your company master table NOT in the day to day data entry.

Can you show an example using the data? I am having a difficult time seeing how this would be layout? We still need to have all ids connected to pull the info. So I can create separate tables but still need to have both Frito lay and Frito lay Llc in the same table. They are different entities technically

I don''t really have time today to build an example for you especially since I really don't understand your application.

1. create a table with company name and company id.  Make a unique index on company name so there are no duplicates since you will be looking up companies by name.  If it turns out that BOTH company name and companyID are unique and by that I mean that you never have more than a single company name for any given companyid, then make companyID the primary key and define a unique index for company name.
2.  do the same thing for the other three ID's.
3.  go back to company name table and add the thee other IDs.  NOT the names, just the IDs.

The ONLY time you ever have to worry about the three other IDs is when you add a new company to  the Company table.  Then you have to make sure that either you already have the other three IDs defined for that company or you must add them also.

Then when you enter an invoice, you select by company name, but the combo MUST be bound to CompanyID because that is what you want to store.

For reporting, you would join the invoice records to the other four tables to get your groupings.

You mean like the following attached. I broke out the suppliers into different tables
sample_2.26.accdb

I see that Pat has given you a great amount of help with this, but I would like to add a few things...

Firstly, ...Pat is one of the top experts here, and if she cant get you going then we have to take a long, hard, honest look at your design here.

Just breaking out suppliers may not be a magic bullet to "normalize' this design..
So while you  did make an attempt, ...it is not clear if it was done correctly within the scope of a design that may not have been optimized for what you are trying to accomplish.

To be honest, ...It is still not clear what the ultimate purpose of this system is.
Highlighting this is the fact that there are no relationships established between any of the tables...

This says to me that we may have some knowledge gaps in the concepts of basic database design.
As the relationships should be established *before* you ever create worry about creating search forms or queries.
The table relationships allow you to set the logical flow of your design.
They also provide for data protection (Referential Integrity)
Examples:
tbl_Spend is still an un-normalized table
The table names are confusing and do not seem to relay any information on what the table cotains (tbl_ultimate?,  tbl_Immediate?)
It is even not clear what each record in the original spreadsheet represents.

It may be that you have to go back to the drawing board on this...
I suggest that you delete this Q, ...as the original topic of "Search forms, no longer applies.
It seems clear that the design may be at fault here, rendering any discussion about search forms, a moot point

I recommend that you post a new question about the normalizing your design (stating clearly the ultimate goal and scope of this project)

JeffCoachman

...to be fair, ...I am working on a VERY simple example of how I see this playing out, ...stay tuned...

I appreciate your answer Jeff. Some background: I just started at this company and am trying to get them out of a terrible process of 10+ people manually doing vlookups to update a 80+ column spreadsheet with over 500k records. Currently we are maintaining vendor hierarchies, spend, category info, and much more. I was trying to post  a simple example.

tbl_vendor - all vendor names and ids
tbl_normalized - all normalized vendor names and ids
tbl_immediate - all immediate vendor names and ids
tbl_ultimate - all ultimate vendor names and ids
tbl_spend - all spend by vendor id
tbl_master - hierarchy roll up with spend

The way this works is we receive a vendor name and id from many other systems. My teams cleans the vendor name which we call the normalized name. That normalized name rolls up to an immediate name and an ultimate vendor name.

So we have vendor name --> normalized name --> immediate name --> ultimate name. If you are a familiar with Dun & Bradstreet it is a similar concept.

Thank you so much Jeffrey, I will take a look and start a new thread for additional design help.

Thank you so much! This is the guidance I needed to get started.

OK, ..great
Remember, normalize and relate first