Access Form - Combo Bpx


I want to have add form with a combo box users can start typing in to see if the data already exists. For example, an id and name field. If when typing in the name field the data does not exist, can i have a selection called "new" and the id is auto assigned and the user inputs the name.

Thank you
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
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.
kwarden13Author Commented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jeffrey CoachmanMIS LiasonCommented:
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?
kwarden13Author Commented:
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
kwarden13Author Commented:
Maybe this will help. Here  is a real world example. This is the excel sheet we have now.
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".
kwarden13Author Commented:
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.
kwarden13Author Commented:
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.
kwarden13Author Commented:
You mean like the following attached. I broke out the suppliers into different tables
Jeffrey CoachmanMIS LiasonCommented:
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, 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)
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, 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)

Jeffrey CoachmanMIS LiasonCommented: be fair, ...I am working on a VERY simple example of how I see this playing out, ...stay tuned...
kwarden13Author Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
I see something roughly like this...
Ultimate output would something like: qryVendorSales_Crosstab

Perhaps you could use something roughly like this, as the basis for your re-design

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kwarden13Author Commented:
Thank you so much Jeffrey, I will take a look and start a new thread for additional design help.
kwarden13Author Commented:
Thank you so much! This is the guidance I needed to get started.
Jeffrey CoachmanMIS LiasonCommented:
OK, ..great
Remember, normalize and relate first
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.