How to create an Update Macro

[bigsistar12]

I have two worksheets, one is a fillable form (calculator) and the other one works as a database.

First I fill out the calculator and save the current new record to my database.
The database will constantly have new records but will be used to update old records too.

My calculator has a save as button that saves new records. My database has a open button that opens old records for updating or editing. I need a macro that allows me (after opening an old record) to save the current records updates or changse to the original record and not create a new one. Same concept as Save as vs Save.

Im not really sure where even to begin. The reason is because each record may have same name different values ex. Dave smith (dates of service) 10/21/10 $50, Dave smith (dates of service) 11/03/10 $2. I think the best way to reference the originting cell is to reference either the active cell in my database from which the original data was pulled from and then in the macro just update the dates or amounts. Or to update a record by two variables such as name and date.

Anyway to do this?

 

[bigsistar12]

A pity, because the built-in DataForm is a good and pratical tool to insert/delete or update records.

Let me ask a question: what is so specific that the DataForm cant handle?

M.

Marcello the form has wayy to many bells and whistles with very specific things I need done. I think it just makes it easier to write the macros myself. I may be wrong, but this is the easiest way. (btw my work has old software, and I tried to find the form according to the link you gave and it could not get it to work.) I am a begginer though and am teaching myself to do this from scratch which is another reason I want to learn how to do it.

 

[p45cal]

Consider, when populating the form from the database sheet, making a note of the row number it came from, say in a protected cell on the same sheet as the form, or somewhere on the database sheet itself, or perhaps in a global variable.
Then use this number when updating to identify which row to update:

Sub update()
i = 0
Set myAnchor = Worksheets("insurance1").Cells([COLOR=Red]thatnumber[/COLOR],1)
Application.ScreenUpdating = False
For Each cll In  Worksheets("pricingcalculator").Range("B1,G1,B2,G2,B3,G3,B4,F4,H4,B5,F5,H5,B6,F6,H6,B8,F8,H8,B9,F9,H9,B10,F10,H10,B11,F11,H11,B12,F12,H12,B13,B14").Cells
myAnchor.Offset(, i).Value = cll.Value
i = i + 1
Next cll
'etc.

This is a flaky solution, because it relies on nothing altering the position of the rows on the database between populating the form and updating from the form. You should put things in place which erase that value (so it can't be used): immediately after updating, when any other change is made to the database sheet, if the user wants to create a new record instead. Perhaps have two buttons, one labelled Update, the other Create new record? The update one uses the number then erases it, the other one doesn't use it and erases it.
To make this robust requires quite a lot of code - you are, after all, trying to use excel as a database which it isn't, so you'd have to mimic the things that a database management system does as a matter of course

 

[bigsistar12]

So possibly its easier and better, to say delete the original file and always update as a new one?

that might work...

 

[p45cal]

So possibly its easier and better, to say delete the original file and always update as a new one?

that might work...

it might indeed.

 

[bigsistar12]

it might indeed.

Thank you! i knew you'd understand?

is this easy to do? how would i go about this? This is what I have so once I run this macro then I would delete.


Sub openpatient()

Sheets("Insurance1").Select

Range(Cells(Selection.Row, 1).Address).Select

Application.ScreenUpdating = False

Worksheets("pricingcalculator").Range("B1").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 1).Address).Value
Worksheets("pricingcalculator").Range("g1").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 2).Address).Value
Worksheets("pricingcalculator").Range("b2").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 3).Address).Value
Worksheets("pricingcalculator").Range("g2").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 4).Address).Value
Worksheets("pricingcalculator").Range("b3").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 5).Address).Value
Worksheets("pricingcalculator").Range("g3").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 6).Address).Value
Worksheets("pricingcalculator").Range("b4").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 7).Address).Value
Worksheets("pricingcalculator").Range("f4").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 8).Address).Value
Worksheets("pricingcalculator").Range("h4").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 9).Address).Value
Worksheets("pricingcalculator").Range("b5").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 10).Address).Value
Worksheets("pricingcalculator").Range("f5").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 11).Address).Value
Worksheets("pricingcalculator").Range("h5").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 12).Address).Value
Worksheets("pricingcalculator").Range("b6").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 13).Address).Value
Worksheets("pricingcalculator").Range("f6").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 14).Address).Value
Worksheets("pricingcalculator").Range("h6").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 15).Address).Value
Worksheets("pricingcalculator").Range("b8").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 16).Address).Value
Worksheets("pricingcalculator").Range("f8").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 17).Address).Value
Worksheets("pricingcalculator").Range("h8").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 18).Address).Value
Worksheets("pricingcalculator").Range("b9").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 19).Address).Value
Worksheets("pricingcalculator").Range("f9").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 20).Address).Value
Worksheets("pricingcalculator").Range("h9").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 21).Address).Value
Worksheets("pricingcalculator").Range("b10").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 22).Address).Value
Worksheets("pricingcalculator").Range("f10").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 23).Address).Value
Worksheets("pricingcalculator").Range("h10").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 24).Address).Value
Worksheets("pricingcalculator").Range("b11").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 25).Address).Value
Worksheets("pricingcalculator").Range("f11").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 26).Address).Value
Worksheets("pricingcalculator").Range("h11").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 27).Address).Value
Worksheets("pricingcalculator").Range("b12").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 28).Address).Value
Worksheets("pricingcalculator").Range("f12").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 29).Address).Value
Worksheets("pricingcalculator").Range("h12").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 30).Address).Value
Worksheets("pricingcalculator").Range("B14").Value = Worksheets("insurance1").Range(Cells(Selection.Row, 32).Address).Value
Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub