Search and Update Records

[AFidytek]

Hi all

I have a excel database with a capture form (not userform - merely a separate sheet) running Macro to update a different sheet with the information captured in the capture form. Adding it to the last row. Now I need to find a solution to use a similar "capture" form or search form, whereby the user can find a record in the database. Once found they must be able to update the information and save (override) the record that they have updated.

I'm currently using a advanced filter (with background Macro) to search for the record in the database (unfortunately within the database and not on a separate sheet, the user must then identify the correct record and update it through the required columns, which reads difficult as my datasheet has 45 columns and is currently standing on 4298 rows.

The risk is that the incorrect column gets updated because of its cumbersome nature.

I know this is a tall order but if there is anyone out there that has a idea, please share.

I am not keen on using a MS Access database, it becomes a nightmare with a slow connection and we are working from different sites. Excel is more stable in my opinion.

Please share your ideas.

Ultimately:
Separate search sheet, with the same information as per the capture sheet (+1 extra which is the database ID) and transpose pastes the identified record into the sheet and we can update the information, then on save it will override the record with the same database ID.

Your help is appreciated.

Thanks

A

 

[AFidytek]

You are amazing, thank you so much Dave.

It works perfectly.

So I didn't solve the sharing like I wanted to. What I am going to try is to extract the capture document into a capture document per user and try and code it back to the database. I don't know if I can have connections written into Macro, but I am going to try. I will let you know if I need help. You have been so amazing.

Thank you again

Ancois

 

[dmt32]

You are amazing, thank you so much Dave.

It works perfectly.

So I didn't solve the sharing like I wanted to. What I am going to try is to extract the capture document into a capture document per user and try and code it back to the database. I don't know if I can have connections written into Macro, but I am going to try. I will let you know if I need help. You have been so amazing.

Thank you again

Ancois

Sorry about errors first post, I had my granddaughter with me & just glanced at changes I made.

If you intend to share Capture workbook with other users all you need to is place Database workbook in shared network folder and provide method in capture workbook that will allow users to set path to it.

Do be mindful though that the database workbook cannot be opened read/write mode whilst users need to write data to it.

Dave

 

[AFidytek]

I appreciate your help so much, especially during family time.

We all work on one shared drive.

I'm thinking of keeping the database in a protected folder and only giving them the capture and reports for use. Not sure how the permissions between the folders will effect it. I will test it and see how it works.

Thank you again.

Ancois