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

 

[dmt32]

Hi,
should not need another form to capture a record in your database - existing form should be able to do it all.

Have a look here:https://www.mrexcel.com/forum/excel...ific-id-table-one-sheet-fill-out-another.html

Post #4 shows code I provided for another here with similar requirement to yourself - you just need to add a search button on your form & reference a cell for unique ID to search. In this example OP has B3 as the ID cell in his form & was searching Column 1 of his database.

You will note there are some cells shown in RED - these are your Forms Input cells which you will need to change as required.

The solution has TWO codes ensure you follow the instructions provided.

Dave.

 

[AFidytek]

HI Dave

I'm trying out the first part of the code that you suggested, but I am getting a error. the "Me" seems to be the error here.

I'm not sure what I am doing wrong.

Thank you for your help.

Ancois

 

[dmt32]

HI Dave

I'm trying out the first part of the code that you suggested, but I am getting a error. the "Me" seems to be the error here.

I'm not sure what I am doing wrong.

Thank you for your help.

Ancois

Hi, as a guess error suggests that you have placed the commandbutton code in a standard module.

This code MUST go in your capture sheet code page (right click tab > View Code) .

Presumably, the button on your sheet is named "CommandButton1" but you should amend code as required if this is different.

You will need the second code to be in your project which can be placed in standard module.


Dave

 

[AFidytek]

Dave

I'm not sure if I am being a idiot here. But I get a error on: DatabaseToDataEntry

Not sure, I moved it to the capture sheet but still no joy.

Ancois

 

[dmt32]

Did you copy that code to a standard module?

Dave

 

[AFidytek]

I added it to the button, so Command_Button2 - action on Click

What am I doing wrong?

Ancois

 

[dmt32]

I added it to the button, so Command_Button2 - action on Click

What am I doing wrong?

Ancois

You only add button to the 1st code (CommandButton) If still having problems, place copy of your workbook with sample data in a dropbox & provide link to it - will have look for you.

Dave

 

[AFidytek]

I'm having issues with the sharing. It nullifies my Macro. I'm not sure if its even possible to fix that.

 

[dmt32]

I'm having issues with the sharing. It nullifies my Macro. I'm not sure if its even possible to fix that.

Sorry, cannot help you with that but when resolve, post link here & should be able to help resolve the issues

Dave