Save Macro to save form data for database

[Widya]

Hello,

I have a problem in creating a form for databases. Every time I record a macro to save the data I've inputted in the form I have created into the database all the macro does is save the worksheet. It doesn't ask me if I want to update the current record, or create a new record, and looking at the database file confirms the fact that nothing has been written to the database. However, when I click on the save icon or file menu/save, it does ask me for those options.

I record the macro by clicking on file menu/save (whereupon it *does* ask me if I want to create a new record, but that doesn't reflect that in practice.) and then saving the macro.

Any advice will be appreciated. I am using Excel 2000.

 

[Ziggy]

what database are you using, I don't exactly follow waht you are trying to do.

 

[keith]

not sure what the fix is yet but it sounds like you are tryin to use the "template wizard" add-in to add records to an Excel List. If this is the case it should update the record when the sheet is saved. Forget the macro for a minute. Does it update on a manual save of the form worksheet? If this is not what you are try to do then disregard this post...

 

[Widya]

Ziggy: I am using MS Excel to create a form (using the template wizard) and associated database workbook (created by the wizard.)

Keith: It sounds like what you refer to as a 'record' is what I refer to as a 'field'. To me a record is the data inputted into a form and then saved in the database worksheet.

When I do a manual save (ie. click on the save icon or go to File Menu/Save) it does ask me if I want to update the current record or add a new record to the database. When I try using a 'save' macro that I have recorded all it does is save the form worksheet- it does not save any data I have inputted in the form worksheet into the database, nor does it give me any prompts (as outlined above.)

Widya Santoso

 

[JeremyinCanada]

I am having the same problem. I am putting together a "database" in Excel since my employer refuses to invest in Access.

I have designed an input template for the database (using the template wizard). Basically, I want to set up a macro (by pushing a control button)that will automatically update the database.

When I do the save MANUALLY it prompts me for the dialog box asking if I want to 1) update or 2) create a new record. However, when I try to autmote it (after recording the macro) it only SAVES. It does not prompt for how I want to handle the record and THEREFORE DOES NOT update the database. What gives? Any assistance would be of assistance. I can't imagine I'm the only person in the world that can't figure this out...can I?

Was this issue addressed and repaired in a service pack I have yet to download or something? Any help would be appreciated.

Thank you.

 

[isbister]

I tackled this issue as well. After wrestling with the Template Wizard with Data Tracking add-in, I decided it was easier to write a macro which updates the database the add-in helped me create. See my source at the end of http://www.mrexcel.com/board/viewtopic.php?topic=10382&forum=2&4

-Brad

 

[Going Cross Eyed]

Dim AdminPrinter As String
Dim OldPrinter As String
I had the same problem and sorted with the code below: hope it helps


Const File_DB = "COMMON"
Const File_ATW = "WZTEMPLT"
Set CurrWorkbook = ActiveWorkbook
CurrWorkbook.Activate
OldPrinter = Application.ActivePrinter
AdminPrinter = "\londonHP4600N-Admin-1 " & GetNode
Application.Run File_ATW & ".XLA!Commit"
SaveLocation = ""