Access Database Form - Cascade Auto Fill In

You can't do both add and update with the DataEntry property set to Yes.  I added a search box so you can use the form for both purposes.  I also modified the table to make the record ID an autonumber and make certain values required.
Example1v2mod.accdb

Thanks PatHarman.

How can I make the search box filter as I type. So for example if I type "3" any supplier containing 3 will be displayed in the table to select.

I have 50k suppliers so scrolling will not work.

Kelly

Also, not sure how you would add a new supplier. When I start typing in other boxes I get an error.

Combos don''t work that way.  You will need to use a textbox and the user will need to enter the wildcards himself or you will need to enter them using VBA.  Then you'll need to make the form continuous or add a listbox with the possible matches in the header.  Then the textbox would be used to filter the listbox and clicking in the listbox would filter the form the same way the combo is doing it currently.

The Recordsource for the listbox would refer to the combo.
Select ... From ... Where SomeField = "*" & Forms!yourform!yourtextbox & "*"

The AfterUpdate event of the textbox would requery the ListBox

Me.lstNames.Requery

The original queries I had were working for the first 2 fields, just not all of them. I am confused

You CANNOT use a bound field for searching.  When you type in a bound field, you are CHANGING the current record.  I should have removed all the code since it will be dangerous in a form that can both view existing and add new records.

It appears that you have an unnormalized table and that could also be contributine to the confusion.

Each of the "key" fields should have its own table where the values are defined.  As it is now, you have both IDs and names in tbl1.  tbl1 should ONLY have the ID for the  four ID fields.

If you want to search on multiple fields, move the search combos to the form header.

I really can't tell where you are going with this so I can't offer a good solution.

Please tell us what the purpose of this form is.  The table as it stands contains nothing but what I would expect to be foreign keys to other tables but it doesn't contain any data of its own.

The purpose of the form is to be able to search and add the supplier. For example, say we have the following:

Supplier: Co1 - Doesn't exist, however rolls up to company 1 (this does exist)

We need to add Co1 as a new supplier, however, company 1 already has an id so we would want people to select that to avoid having misspellings with other Ids. Make sense?

Perhaps the top of the form should be to search and the bottom to add. Not sure the best way to do this.

Kelly

You can add on any form that allows it by pressing the star at the bottom or the New icon in the ribbon.

That doesn't help me. The solution I want is to be able to make sure records are consistently assigned to new suppliers. I can add the new supplier but how would i be able to find the parent company by searching. Thank you for your time but I think you missed the point on what I am trying to do. What I attached in my first email is what I am trying to expand upon.

Thanks
Kelly

As I said in another post, your schema is not normalized and that is getting in the way of a clean search.  And  you are correct.  I do not understand at all what you are trying to do.  But, what I see in your stripped down example doesn't make sense.

I can add the new supplier but how would i be able to find the parent company by searching.

You don't have a table that does this.

Do some reading on normalization.  That may help clarify your thinking on how the tables should be structured.  If a supplier has only a single parent company (and I don't see how that could be otherwise), then there is absolutely no reason for both supplier and parent to be in the same table EXCEPT for the supplier table which is used to define suppliers.  In all other cases, your table would include SupplierID as the foreign key and if you needed other info about the supplier, you would get it by including a join to the supplier table in your query.  You have only one table which is a muddle of purpose.  In your existing setup, there is noting to prevent row 1 for supplier 1 to reference parent company 1 and row 2 for supplier 1 to reference parent company 99.  That is why you need a supplier table where you would define these relationships.  Also, I'm sure that there are suppliers who do not have a parent company.

The search boxes must be unbound.  Put them in the form's header.  You can use them to filter the form.  The point is that you CANNOT use bound fields for searching.

Keep in mind that when you open a form with the Data Entry flag set to yes, it opens to an empty recordset and so on an empty recordset, you won't find anything.  That is why I removed the Data Entry checkbox.

Putting this in multiple tables will not work with my teams current process. I understand normalization and other concepts. However, there are reasons why this is all in one table. The actual spreadsheet has 75 columns and is imported and exported daily to keep records up to date. Splitting into multiple tables would be a nightmare and complicate the process. If I wanted they could do this in Excel, however, I need multiple people to be able to do this at one time and have had a difficult time using excel shareing workbooks

Access is not a good spreadsheet program.  Excel is not a good database program.

If the spreadsheet comes from some other source daily, what are you doing updating it?  If you control the master, then there is no reason why it can't be a database and properly normalized.  You can always export to Excel if you need to for distribution to other people.

it comes from different people updating the sheet manually throughout the day. Then my team takes it and updates certain information on new suppliers or existing. This is a pieced out process and we are just the middle team. So if we normalize it, we will have to piece it back together. There is no point in normalizing the data. We are using this for a different purpose.

We agree to disagree! Thank you for your help.

Thanks Pat!

You're welcome