Search, Add, and Edit Forms - Need Help!

[kwarden13]

Hello-



I have a database that keeps track of all spend and suppliers. The suppliers have a hierarchical relationship.

Supplier --> Normalized Name --> Immediate Name --> Ultimate Name

I need to have a form built where users can search to find suppliers they need to edit or update, and search to see if they need to add new suppliers that are not found. I was thinking of having a search form with a datasheet view where users can edit the data. Not sure how to build though. Any ideas?

Attached is the database.

[vicsaccess]

this format works very well for me.
http://www.allenbrowne.com/ser-62.html

[kwarden13]

So i saw that and was initially trying to build that, however, you cannot edit in the form, just search. Is there anyway to use a datasheet form with that? The user would need to edit a lot of records in some cases so I do not want to make them click on each one individually. Also, when using a text box, the search is taking a long time. I have over 100k records

[vicsaccess]

sample_2.26 v2.zip

you never know or get better until you try. and yes you can edit.

[kwarden13]

Thank you so much for posting with the example! How is the datasheet linked? Also, is it possible to have one search box but have it look in 4 different fields for the text string in the code?

Thank you again! I searched and searched and could not find anything like this.

[vicsaccess]

glad to help. the datasheet is linked with subform referances. take a look at the page i added in earlier post, he does a great job of explaining it and giving you the knowledge to understand it and alter what you need.

[kwarden13]

So I figured out the search part, however, am trying to now be able to edit. Since my subform is a qry and not a table is there a way to edit? I am having trouble finding examples.

[vicsaccess]

Not sure what you are trying to figure out with the edits. when i last looked at it you were able to change records but your query on the subform did have the totals set to grouped by which does limit your ability to add records, remove that since it does not look like you are using it. other than than check your subform data properties for allow additions, allow deletions and allow edits.

[kwarden13]

Ok will do. Thank you. I am now trying to edit the records after searching and I have the combo drop downs working however the ids do not change when the name changes. I want the ID fields to change when the name is updated in the combo boxes. Also, since it is a hierarchy, if the relationship changes it should alert to change the immediate and ultimate boxes depending on the normalized name.

I have been searching all morning and cannot find examples of doing this.