Retrieve and amend records from database

**Dave32** · 04-11-2008, 11:51 AM

I am a building a database where each row represents a record. Each record has a unique identifier number.

In worksheet "Record Search and Amend", I have recorded a macro to look up the data.

The problem is once the record is retrieved I need to be able to amend the record and save it back into the database in the right row. This could be another button to initiate this action.

Could anyone help me with the VBA code? Spreadsheet attached.

Thanks,

Dave32

**royUK** · 04-11-2008, 12:40 PM

You don't need a macro to get the data, just place the formulas in the cells. there's no point running code for the sake of it.

To amend the record use the VBA Find method to look for the ID number & then copy the amendments with code adapted from the code that posts new records.

**Dave32** · 04-14-2008, 05:15 AM

Roy

Thanks for your answer. I used a Macro here because when the user amends a record, new data is entered in the cell and then the formula disappears. The user goes to look up a new record, it does not work as one of the Vlookup statements will be missing! I am trying to keep formulae out of the forms.

I don't know how to use the FIND function in VBA or amend the code so that when a record is found the record is saved again in the right place. I am new to VBA and even with the help of a book can't work out how to do everything.. Can you help?

Thanks,

Dave32

**Charlize** · 04-14-2008, 05:52 AM

On the tab 'Record Search & Amend' I would add the keyword 'False' to your vlookup formula. Because when no value is found, there will be an error.

Instead of those vlookup formula, use the macrobutton to get the number you fill in and display the values of the tables.

When changing those retrieved values, you can use the worksheet_change event to update the values, based on the number.

Charlize

**Dave32** · 04-14-2008, 06:11 AM

Charlize,

Thanks for your reply. Yes, I can add FALSE to the Vlookup statement, I have just amended the formula

=IFERROR(VLOOKUP(B3,Database!A2:Z20,2,FALSE),"Record Not found")

I am sorry I could follow the rest of your advice. I am new to VBA and don't understand all the functions yet. Could you please amend my code in the spreadsheet.

The spreadsheet is attached.

Thanks,

Dave32

**Charlize** · 04-14-2008, 06:39 AM

Changed coding for the macrobutton. Will do it for a couple of items. Now you can add a worksheet_change event on this sheet and look for the number in B3 and change the value in database that you've just changed in a cell. First check if number is valid before changing.

Please Login or Register  to view this content.

Charlize

**Dave32** · 04-14-2008, 07:30 AM

Charlize,

The code works well. However, I still need help on the code for the worksheet change event. Roy suggested amend the VBA code that I am using in the tab "DataEntry1". I don't know how to do this though.

Help!

Dave32

**Charlize** · 04-14-2008, 08:11 AM

This is a worksheet_change event of the tab 'Record Search & Amend'.

Please Login or Register  to view this content.

The code for fetching a record should be replaced with this one. It disables the application event on fetching an reenables it after the fetching. This is to avoid unnecessary changes to your database.

Please Login or Register  to view this content.

And there is a problem with the find part. XLValues will not work. You fill in 13 but 137 is needed. So you'll have to alter the XLValues part to something else. Found it, define the lookat part.

Charlize

**Dave32** · 04-14-2008, 09:08 AM

Charlize,

OK, I can now retrieve a record. But, I want the user to be able to change the record an save it into the same place in the database.

Lets take record 134, the user looks up the record and then changes the country field to DE.

I have created another button "Save amended Record"

Could you tell how I can save an amended record to the right place in the database.

New file attached.

Thanks,

Dave32

**Charlize** · 04-14-2008, 09:26 AM

Originally Posted by Dave32

Charlize,

OK, I can now retrieve a record. But, I want the user to be able to change the record an save it into the same place in the database.

Lets take record 134, the user looks up the record and then changes the country field to DE.

I have created another button "Save amended Record"

Could you tell how I can save an amended record to the right place in the database.

New file attached.

Thanks,

Dave32

See my previous post.

**Dave32** · 04-14-2008, 09:45 AM

Roy,

Thanks for your new code. However, I got an error:

Compile Error "End with without with"

I just changed 1 line: Sorry I don't know how to post code correctly, I am looking at the guideline and rule now

Please Login or Register  to view this content.

**royUK** · 04-14-2008, 10:05 AM

Ignore the previous code. Add this to one module

Please Login or Register  to view this content.

You need to go through your code and tidy it up before you do any thing else.

**Dave32** · 04-14-2008, 11:46 AM

Roy,

Thanks for the new code. It works fine, but I could not assign the new entry code to the "Save New Record to database" button on the dataentry1 tab.

This is probably now because my code is messy.

On the dataentry1 tab there should be 3 Macros:

Clear Form code
Save new record code
Save to autofillfrom2 code

On the Record & Search tab there should be 3 Macros or code

Record search code
Save amended record code
Clear form code.

Can you help me clear this mess up, I guess I have tried code from two different people now and it just got messy.

Thanks for your help.

Dave32 - new sheet attached

**royUK** · 04-14-2008, 12:28 PM

I will have a look later, if I can tonight but I have a long drive home now.

**royUK** · 04-14-2008, 02:32 PM

I've tidied this up, all the code is now in mMain and assigned to the buttons. i suggest that you stick to either Forms buttons or Control ToolBox buttons. I have also removed empty modules & userform.

I suggest that you take some time to understand what your code is doing.

**Dave32** · 04-15-2008, 03:16 AM

Roy,

Thanks very much for your help. It is greatly appreciated.

Just checked the file. sorry you attached the wrong one.

Dave32

**Dave32** · 04-15-2008, 03:19 AM

Roy,

Sorry you attached the wrong file. Please re-attach

Dave32

**royUK** · 04-15-2008, 03:26 AM

Sorry about that, it was a long day yesterday

**Dave32** · 04-15-2008, 05:46 AM

Roy,

Thanks for the new file.

Have a good day.

Dave32

**Dave32** · 04-15-2008, 08:06 AM

http://www.excelforum.com/showthread...02#post1908102

Roy,

Now I have the basics right in my spreadsheet.

I have just discovered a big issue. I have too much data per record to store in Excel's 255 columns! Posted above.

Each records refers to money spent on a marketing campaign, the problems is that each marketing can have several activities accross several countries.

I need to store data about each activity in each country and the amount spent per month per country and per activity.

Dave32

Retrieve and amend records from database

LinkBack

Thread Tools

Rate This Thread

Display