I am trying to implement a button on a form (used
template wizard to link form to an excel list/database)
that will both save as "myfilename" and also force
the "create new record". So far, I can get it to save,
but the only way it will pop up the "template file - save
to database" input is by using the standard toolbar save
button. A save or save as macro will not bring up the
box.

I would also like it to pick the "create new record"
radio button, and click the "ok" box.

Is there a way to do this?

If not is there a way to make my button act exactly like
the standard toolbar button?

btw- I created a macro by clicking the std save toolbar
button, but it does not act the same, ie, I get no update
database.

tia
jason

I am still looking for this answer. I have found info on
forms, but still can't get the darn thing to update.

I am obviously inexperienced with programming, so I would
even take a hint at how to make a custom button act
exactly the same as the std toolbar save button.

Anyway thanks again.
Jason
-----Original Message-----
I am trying to implement a button on a form (used
template wizard to link form to an excel list/database)
that will both save as "myfilename" and also force
the "create new record". So far, I can get it to save,
but the only way it will pop up the "template file -
save
to database" input is by using the standard toolbar save
button. A save or save as macro will not bring up the
box.

I would also like it to pick the "create new record"
radio button, and click the "ok" box.

Is there a way to do this?

If not is there a way to make my button act exactly like
the standard toolbar button?

btw- I created a macro by clicking the std save toolbar
button, but it does not act the same, ie, I get no
update
database.

tia
jason
.

Thanks steve, but that returned a compile error -
expected function or variable, and the .save = is
highlighted.

Here is some more detail:
using excel 2000. I used template wizard to create a
user input spreadsheet. Each new record needs a
sequential number assigned. The list/database holds all
of the info for all the records, but each record is also
saved as its own file. Creating and updating an
individual record needs to update the list/database.

the workflow is:
-open the template for a new blank input sheet.
-clicks the first button to find the last record in the
database add 1, paste the number in the input sheet
-click second button and the worksheet is saved as the
number from the previous step
-enter data unique to the record
-click a button to update the list/database ***not
working***
-email the the spreadsheet with the unique data to a
responsible party (or email a link to the file on the
server)
-responsible party adds info, then clicks a button to
save to the orig. file and updtae the database (not
implemented yet).

Like I said before using the save button from the std
toolbar causes the database update window to open, but
coded saves do not.

Thanks again for any suggestions.
Jason
-----Original Message-----
Jason,

Put this code into a standard module and customize your
button and assign
this macro.

Sub saver()
ActiveWorkbook.Save = True
End Sub

"jason" <jht@onebox wrote in message
...
I am still looking for this answer. I have found info
on
forms, but still can't get the darn thing to update.

I am obviously inexperienced with programming, so I
would
even take a hint at how to make a custom button act
exactly the same as the std toolbar save button.

Anyway thanks again.
Jason
-----Original Message-----
I am trying to implement a button on a form (used
template wizard to link form to an excel
list/database)
that will both save as "myfilename" and also force
the "create new record". So far, I can get it to
save,
but the only way it will pop up the "template file -
save
to database" input is by using the standard toolbar
save
button. A save or save as macro will not bring up the
box.

I would also like it to pick the "create new record"
radio button, and click the "ok" box.

Is there a way to do this?

If not is there a way to make my button act exactly
like
the standard toolbar button?

btw- I created a macro by clicking the std save
toolbar
button, but it does not act the same, ie, I get no
update
database.

tia
jason
.



.

Jason,

My ouch... should be

Sub saver()
ActiveWorkbook.Save
End Sub

You don't need the "= True" on the end.

If you had used
ActiveWorkbook.Close = True
than you need the = True.
Change it to False and you close without saving.

steve

Try recording a macro and see if that works.
"Jason" <jht@oneboxdotcom wrote in message
...
Thanks steve, but that returned a compile error -
expected function or variable, and the .save = is
highlighted.

Here is some more detail:
using excel 2000. I used template wizard to create a
user input spreadsheet. Each new record needs a
sequential number assigned. The list/database holds all
of the info for all the records, but each record is also
saved as its own file. Creating and updating an
individual record needs to update the list/database.

the workflow is:
-open the template for a new blank input sheet.
-clicks the first button to find the last record in the
database add 1, paste the number in the input sheet
-click second button and the worksheet is saved as the
number from the previous step
-enter data unique to the record
-click a button to update the list/database ***not
working***
-email the the spreadsheet with the unique data to a
responsible party (or email a link to the file on the
server)
-responsible party adds info, then clicks a button to
save to the orig. file and updtae the database (not
implemented yet).

Like I said before using the save button from the std
toolbar causes the database update window to open, but
coded saves do not.

Thanks again for any suggestions.
Jason
-----Original Message-----
Jason,

Put this code into a standard module and customize your
button and assign
this macro.

Sub saver()
ActiveWorkbook.Save = True
End Sub

"jason" <jht@onebox wrote in message
...
I am still looking for this answer. I have found info
on
forms, but still can't get the darn thing to update.

I am obviously inexperienced with programming, so I
would
even take a hint at how to make a custom button act
exactly the same as the std toolbar save button.

Anyway thanks again.
Jason
-----Original Message-----
I am trying to implement a button on a form (used
template wizard to link form to an excel
list/database)
that will both save as "myfilename" and also force
the "create new record". So far, I can get it to
save,
but the only way it will pop up the "template file -
save
to database" input is by using the standard toolbar
save
button. A save or save as macro will not bring up the
box.

I would also like it to pick the "create new record"
radio button, and click the "ok" box.

Is there a way to do this?

If not is there a way to make my button act exactly
like
the standard toolbar button?

btw- I created a macro by clicking the std save
toolbar
button, but it does not act the same, ie, I get no
update
database.

tia
jason
.



.

Jason,

Most of your code looks pretty good. But I have added some questions marked
with ?? and comments !!

(watch out for word wrap!!!!!!!!!!!!!!!)

Do you have Option Explicit at the top of you module(s). This helps force
Excel to compile you code and locate problems. It is also a good idea to
step through the code (use F8) and see what is happening line by line.

I am still confused as to where the data is entered and stored. Which
workbook is which? (pardon my denseness, I am the kind that needs to "touch
& feel")

Now you got me hooked, so let's keep going with this.

steve

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR Database.xls"
Sheets("CAR Database").Select


' ?? What range are you sorting? Looks like a single cell.

' !! Also you can shorten it to Range("A2").Sort and get rid of the select

Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' !! again, get rid of select

' note that if the copy range is more than one cell, you still only need to
specify Range("G4")

Range("A2").Copy _

Destination:=Workbooks("QAD8001.xls").Sheets(1).Ra nge("G4").PasteSpecial
Paste:= _ Paste:=xlPasteValues


' ?? Not sure what is going on here but you can use the above idea.
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows("CAR Database.xls").Activate
Application.CutCopyMode = False

' !! you can replace these 2 lines with

ActiveWindow.Close = True


ActiveWorkbook.Save
ActiveWindow.Close


' !! not sure which book you are working on here (guess it is QAD8001)

' !! again you don't need to select

With Range("F1")

..Interior.ColorIndex = 3
..Font.Bold = True

End With


Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


?? Can you give anything more than "won't work"?

Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and some
other things I can't remember now.


?? Do you mean that the data doesn't get pasted/added?


The thing that will not happen with my coded saves is the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let me
know.


"jason" <jht@oneboxdotcom wrote in message
...
OK here is the first macro (find next record, add 1,
populate the cell, it also highlights a cell to red and
back):

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'

'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR
Database.xls"
Sheets("CAR Database").Select
Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("CAR Database.xls").Activate
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and some
other things I can't remember now.

The thing that will not happen with my coded saves is the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let me
know.






-----Original Message-----
Jason,

Post the code that isn't working. And define what you
want it to do.
(your outline is good but leaves me a little uncertain).

steve

"jason" <jht@oneboxdotcom wrote in message
...
Steve,
Thanks again, but this only saves the file. Clicking
the
disk icon (save) or the file-save menu command gives
you
a box with radio buttons (Template file - save to
database) and allows you to create a new record, update
existing recors, continue without updating.

I have recorded macros using those methods, and have
not
gotten the same box to come up. There is definitely
some
hidden command in the built in command that is not
happening in the macro. (ie the built in button
launches
a function, but the macro does not)

I have searched through the vb help, this board, ms
support, tried random commands in vb, and cannot find
any
refernce to another command that causes this "template
file - update to database"

It is now a matter of my stubbornness. I can easily
type
in "hey user, click the save button up there at the top
now" but that doesn't follow the logic that I set up
for
this. I really want button, button, type, button,
button, all in a nice column.

Thanks again.




-----Original Message-----
Jason,

My ouch... should be

Sub saver()
ActiveWorkbook.Save
End Sub

You don't need the "= True" on the end.

If you had used
ActiveWorkbook.Close = True
than you need the = True.
Change it to False and you close without saving.

steve

Try recording a macro and see if that works.
"Jason" <jht@oneboxdotcom wrote in message
...
Thanks steve, but that returned a compile error -
expected function or variable, and the .save = is
highlighted.

Here is some more detail:
using excel 2000. I used template wizard to create
a
user input spreadsheet. Each new record needs a
sequential number assigned. The list/database holds
all
of the info for all the records, but each record is
also
saved as its own file. Creating and updating an
individual record needs to update the list/database.

the workflow is:
-open the template for a new blank input sheet.
-clicks the first button to find the last record in
the
database add 1, paste the number in the input sheet
-click second button and the worksheet is saved as
the
number from the previous step
-enter data unique to the record
-click a button to update the list/database ***not
working***
-email the the spreadsheet with the unique data to a
responsible party (or email a link to the file on
the
server)
-responsible party adds info, then clicks a button
to
save to the orig. file and updtae the database (not
implemented yet).

Like I said before using the save button from the
std
toolbar causes the database update window to open,
but
coded saves do not.

Thanks again for any suggestions.
Jason
-----Original Message-----
Jason,

Put this code into a standard module and customize
your
button and assign
this macro.

Sub saver()
ActiveWorkbook.Save = True
End Sub

"jason" <jht@onebox wrote in message
...
I am still looking for this answer. I have found
info
on
forms, but still can't get the darn thing to
update.

I am obviously inexperienced with programming,
so I
would
even take a hint at how to make a custom button
act
exactly the same as the std toolbar save button.

Anyway thanks again.
Jason
-----Original Message-----
I am trying to implement a button on a form
(used
template wizard to link form to an excel
list/database)
that will both save as "myfilename" and also
force
the "create new record". So far, I can get it
to
save,
but the only way it will pop up the "template
file -
save
to database" input is by using the standard
toolbar
save
button. A save or save as macro will not bring
up
the
box.

I would also like it to pick the "create new
record"
radio button, and click the "ok" box.

Is there a way to do this?

If not is there a way to make my button act
exactly
like
the standard toolbar button?

btw- I created a macro by clicking the std save
toolbar
button, but it does not act the same, ie, I get
no
update
database.

tia
jason
.



.



.



.

Ok steve, you are a trooper. Here goes:

The poo poo code is because it was built using the macro
recorder. I really don't know better, and it works, so I
left it alone.

QAD8001.xls is the spreadsheet that is created from the
template QAD800.xlt

QAD800.xlt is tied to CAR Database.xls using the template
wizard. It has cells for data input that tie into the
appropriate columns in CAR Database.xls

When you open the the template (file-new-QAD800) it
creates an .xls called QAD8001.xls You then enter data
on this spreadsheet. Without any code or macros, if you
click the default save, save as, or close--you get
the "save template file to database" pop-up box (and it
also opens CAR Database.xls in the background). If you
click the "add record" radio button (and OK) it will add
a line to CAR Database.xls with the data that is in the
named/linked fields (from QAD8001.xls).

This is the desired result - add a record.

Also desired is to save that instance of QAD8001.xls as a
seperate file with the CAR# as the file name. This will
let other users (and myself) add data in the future, add
pictures, etc., still update the required fields to the
database, and not overwrite other records/spreadsheets.

Without the save as 1234.xls I will get gobs of
QAD8001.xls's or QAD80011.xls, QAD800111.xls, etc.

So, on to the code:

No, I have no option explicit clauses. But, all of the
code I posted here works fine. I am not trying to debug
any of it. Sorry for the confusion.

The find next macro:
The range I selected (using macro recorder) was a2..o2002
it sorts on column A (which is a unique CAR number
20030001, 20030002, 20030003, etc.) descending - so that
the highest number is at the top.

Then it copies that number to QAD8001, pastes it.
QAD8001 adds 1 to the number and pastes it in the CAR
field. So every time I run the macro, I get the last
number used and create a new number for the new record.
(BTW-if the "update template to database" doesn't occur,
the new number is not put back into CAR Database.xls)


After all that, I jump back to CAR Database.xls, to close
it out. I really did not want to save it after sorting,
but I didn't want a "yes, no, cancel" to come up when you
tried to close it without saving.

At this point you only have QAD8001.xls open.

The 2nd macro works fine. It takes the CAR# and does a
save as with that number as the file name. I now have a
series of files named 2003001.xls, 2003002.xls,
2003003.xls, etc.

Each of those files represents 1 row of data in CAR
database.xls.

When you open 2003002.xls and change or add data, the hit
the std toolbar save button the "update template to
database" window pops up. If you select update record,
it puts the current info into the 2003002 row of CAR
database.xls.

When you open the same file and use a macro (simply
activeworkbook.save) it does not update any info in CAR
Database.xls you merely get a saved 2003002.xls.

This is the crux of my problem. When I say "It doesn't
work" in my earlier post, I mean my attempts at creating
a macro that performs --exactly-- the same functions as
the std built in toolbar save button are unsuccessful.



Steve, I hope some of what I say here is understandable.
I don't want you to give up in frustration because I am
not communicating properly. From my seat, it is very
easy to know what I want, because I have been at this for
about 3 weeks (and before that with access data web
pages, and prior to that with plain access).

The bottom line is I need an easy way to input, then
email a corrective action, then get a response and
comment on it, all the while maintaining both a database
of all the records, and each individual record as a
seperate file.

I will be happy to elaborate further on anything and
everything...
Thanks,
Jason
















-----Original Message-----
Jason,

Most of your code looks pretty good. But I have added
some questions marked
with ?? and comments !!

(watch out for word wrap!!!!!!!!!!!!!!!)

Do you have Option Explicit at the top of you module(s).
This helps force
Excel to compile you code and locate problems. It is
also a good idea to
step through the code (use F8) and see what is happening
line by line.

I am still confused as to where the data is entered and
stored. Which
workbook is which? (pardon my denseness, I am the kind
that needs to "touch
& feel")

Now you got me hooked, so let's keep going with this.

steve

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR
Database.xls"
Sheets("CAR Database").Select


' ?? What range are you sorting? Looks like a single
cell.

' !! Also you can shorten it to Range("A2").Sort and get
rid of the select

Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' !! again, get rid of select

' note that if the copy range is more than one cell, you
still only need to
specify Range("G4")

Range("A2").Copy _

Destination:=Workbooks("QAD8001.xls").Sheets(1).R ange
("G4").PasteSpecial
Paste:= _ Paste:=xlPasteValues


' ?? Not sure what is going on here but you can use the
above idea.
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows("CAR Database.xls").Activate
Application.CutCopyMode = False

' !! you can replace these 2 lines with

ActiveWindow.Close = True


ActiveWorkbook.Save
ActiveWindow.Close


' !! not sure which book you are working on here (guess
it is QAD8001)

' !! again you don't need to select

With Range("F1")

..Interior.ColorIndex = 3
..Font.Bold = True

End With


Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


?? Can you give anything more than "won't work"?

Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and some
other things I can't remember now.


?? Do you mean that the data doesn't get pasted/added?


The thing that will not happen with my coded saves is the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let me
know.


"jason" <jht@oneboxdotcom wrote in message
...
OK here is the first macro (find next record, add 1,
populate the cell, it also highlights a cell to red and
back):

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'

'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR
Database.xls"
Sheets("CAR Database").Select
Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("CAR Database.xls").Activate
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and
some
other things I can't remember now.

The thing that will not happen with my coded saves is
the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it
just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let
me
know.






-----Original Message-----
Jason,

Post the code that isn't working. And define what you
want it to do.
(your outline is good but leaves me a little
uncertain).

steve

"jason" <jht@oneboxdotcom wrote in message
...
Steve,
Thanks again, but this only saves the file.
Clicking
the
disk icon (save) or the file-save menu command gives
you
a box with radio buttons (Template file - save to
database) and allows you to create a new record,
update
existing recors, continue without updating.

I have recorded macros using those methods, and have
not
gotten the same box to come up. There is definitely
some
hidden command in the built in command that is not
happening in the macro. (ie the built in button
launches
a function, but the macro does not)

I have searched through the vb help, this board, ms
support, tried random commands in vb, and cannot
find
any
refernce to another command that causes
this "template
file - update to database"

It is now a matter of my stubbornness. I can easily
type
in "hey user, click the save button up there at the
top
now" but that doesn't follow the logic that I set up
for
this. I really want button, button, type, button,
button, all in a nice column.

Thanks again.




-----Original Message-----
Jason,

My ouch... should be

Sub saver()
ActiveWorkbook.Save
End Sub

You don't need the "= True" on the end.

If you had used
ActiveWorkbook.Close = True
than you need the = True.
Change it to False and you close without saving.

steve

Try recording a macro and see if that works.
"Jason" <jht@oneboxdotcom wrote in message
...
Thanks steve, but that returned a compile error -
expected function or variable, and the .save = is
highlighted.

Here is some more detail:
using excel 2000. I used template wizard to
create
a
user input spreadsheet. Each new record needs a
sequential number assigned. The list/database
holds
all
of the info for all the records, but each record
is
also
saved as its own file. Creating and updating an
individual record needs to update the
list/database.

the workflow is:
-open the template for a new blank input sheet.
-clicks the first button to find the last record
in
the
database add 1, paste the number in the input
sheet
-click second button and the worksheet is saved
as
the
number from the previous step
-enter data unique to the record
-click a button to update the list/database
***not
working***
-email the the spreadsheet with the unique data
to a
responsible party (or email a link to the file on
the
server)
-responsible party adds info, then clicks a
button
to
save to the orig. file and updtae the database
(not
implemented yet).

Like I said before using the save button from the
std
toolbar causes the database update window to
open,
but
coded saves do not.

Thanks again for any suggestions.
Jason
-----Original Message-----
Jason,

Put this code into a standard module and
customize
your
button and assign
this macro.

Sub saver()
ActiveWorkbook.Save = True
End Sub

"jason" <jht@onebox wrote in message
...
I am still looking for this answer. I have
found
info
on
forms, but still can't get the darn thing to
update.

I am obviously inexperienced with programming,
so I
would
even take a hint at how to make a custom
button
act
exactly the same as the std toolbar save
button.

Anyway thanks again.
Jason
-----Original Message-----
I am trying to implement a button on a form
(used
template wizard to link form to an excel
list/database)
that will both save as "myfilename" and also
force
the "create new record". So far, I can get
it
to
save,
but the only way it will pop up the "template
file -
save
to database" input is by using the standard
toolbar
save
button. A save or save as macro will not
bring
up
the
box.

I would also like it to pick the "create new
record"
radio button, and click the "ok" box.

Is there a way to do this?

If not is there a way to make my button act
exactly
like
the standard toolbar button?

btw- I created a macro by clicking the std
save
toolbar
button, but it does not act the same, ie, I
get
no
update
database.

tia
jason
.



.



.



.



.

Jason,

Not frustrating - challenging!

Now lets comment on your comments and see where we can go.
See my comments within your comments...
(read all the way down to "end==============")

The poo poo code is because it was built using the macro
recorder. I really don't know better, and it works, so I
left it alone.
Recording is great but usally adds more than you need.
Especially with the select stuff. You'll do yourself a big favor by
changing those per my previous emails.


QAD8001.xls is the spreadsheet that is created from the
template QAD800.xlt

QAD800.xlt is tied to CAR Database.xls using the template
wizard. It has cells for data input that tie into the
appropriate columns in CAR Database.xls

When you open the the template (file-new-QAD800) it
creates an .xls called QAD8001.xls You then enter data
on this spreadsheet. Without any code or macros, if you
click the default save, save as, or close--you get
the "save template file to database" pop-up box (and it
also opens CAR Database.xls in the background). If you
click the "add record" radio button (and OK) it will add
a line to CAR Database.xls with the data that is in the
named/linked fields (from QAD8001.xls).

This is the desired result - add a record.
Is this working? If not we can make some simple code to copy data to the
database. Sounds like you only want to copy a single line, correct me if
I'm wrong.


Also desired is to save that instance of QAD8001.xls as a
seperate file with the CAR# as the file name. This will
let other users (and myself) add data in the future, add
pictures, etc., still update the required fields to the
database, and not overwrite other records/spreadsheets.

Without the save as 1234.xls I will get gobs of
QAD8001.xls's or QAD80011.xls, QAD800111.xls, etc.
From your previous posts it sounds like this isn't happening. It should be
straight forward to build in a save-as into the code and extract the name
from anywhere in the sheet.


So, on to the code:

No, I have no option explicit clauses. But, all of the
code I posted here works fine. I am not trying to debug
any of it. Sorry for the confusion.
Strongly recommend Option Explicit as a general rule. It helps catch typo's
and other stuff.


The find next macro:
The range I selected (using macro recorder) was a2..o2002
it sorts on column A (which is a unique CAR number
20030001, 20030002, 20030003, etc.) descending - so that
the highest number is at the top.

Then it copies that number to QAD8001, pastes it.
QAD8001 adds 1 to the number and pastes it in the CAR
field. So every time I run the macro, I get the last
number used and create a new number for the new record.
(BTW-if the "update template to database" doesn't occur,
the new number is not put back into CAR Database.xls)


After all that, I jump back to CAR Database.xls, to close
it out. I really did not want to save it after sorting,
but I didn't want a "yes, no, cancel" to come up when you
tried to close it without saving.
Workbook("CAR Database.xls").Close = False
should do this


At this point you only have QAD8001.xls open.

The 2nd macro works fine. It takes the CAR# and does a
save as with that number as the file name. I now have a
series of files named 2003001.xls, 2003002.xls,
2003003.xls, etc.

Each of those files represents 1 row of data in CAR
database.xls.

When you open 2003002.xls and change or add data, the hit
the std toolbar save button the "update template to
database" window pops up. If you select update record,
it puts the current info into the 2003002 row of CAR
database.xls.

When you open the same file and use a macro (simply
activeworkbook.save) it does not update any info in CAR
Database.xls you merely get a saved 2003002.xls.
This sounds normal. You'll need to call up an update function if you want
to change the database. But here you have me confused because you said
earlier that you want to close the database without saving???


This is the crux of my problem. When I say "It doesn't
work" in my earlier post, I mean my attempts at creating
a macro that performs --exactly-- the same functions as
the std built in toolbar save button are unsuccessful.



Steve, I hope some of what I say here is understandable.
I don't want you to give up in frustration because I am
not communicating properly. From my seat, it is very
easy to know what I want, because I have been at this for
about 3 weeks (and before that with access data web
pages, and prior to that with plain access).

The bottom line is I need an easy way to input, then
email a corrective action, then get a response and
comment on it, all the while maintaining both a database
of all the records, and each individual record as a
seperate file.

Suggest that you build a number of macros. Have each macro just do a small
portion of what you want. Than we can make sure each module works. Once
that is a go, it is just a matter to call what you want when you want it.


I will be happy to elaborate further on anything and
everything...
Thanks,
Jason


Now here's my interpretation of what you want.

Open the template
Add data to the template. (One line, or many???)
Save-As using a Car # from cell A1(?) for the name
Copy the data to the master database.
Save (?) the database.
email something to someone.

Get back to me

steve

end=============================================== ==





-----Original Message-----
Jason,

Most of your code looks pretty good. But I have added
some questions marked
with ?? and comments !!

(watch out for word wrap!!!!!!!!!!!!!!!)

Do you have Option Explicit at the top of you module(s).
This helps force
Excel to compile you code and locate problems. It is
also a good idea to
step through the code (use F8) and see what is happening
line by line.

I am still confused as to where the data is entered and
stored. Which
workbook is which? (pardon my denseness, I am the kind
that needs to "touch
& feel")

Now you got me hooked, so let's keep going with this.

steve

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR
Database.xls"
Sheets("CAR Database").Select


' ?? What range are you sorting? Looks like a single
cell.

' !! Also you can shorten it to Range("A2").Sort and get
rid of the select

Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' !! again, get rid of select

' note that if the copy range is more than one cell, you
still only need to
specify Range("G4")

Range("A2").Copy _

Destination:=Workbooks("QAD8001.xls").Sheets(1).R ange
("G4").PasteSpecial
Paste:= _ Paste:=xlPasteValues


' ?? Not sure what is going on here but you can use the
above idea.
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows("CAR Database.xls").Activate
Application.CutCopyMode = False

' !! you can replace these 2 lines with

ActiveWindow.Close = True


ActiveWorkbook.Save
ActiveWindow.Close


' !! not sure which book you are working on here (guess
it is QAD8001)

' !! again you don't need to select

With Range("F1")

..Interior.ColorIndex = 3
..Font.Bold = True

End With


Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


?? Can you give anything more than "won't work"?

Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and some
other things I can't remember now.


?? Do you mean that the data doesn't get pasted/added?


The thing that will not happen with my coded saves is the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let me
know.

Steve,

I'll start from the summary:

my comments have ****


Now here's my interpretation of what you want.

Open the template

***Yes***

Add data to the template. (One line, or many???)

***The template has 15 data entry points that correspond
to columns in the database. They are arranged in a
single column on the template*****

Save-As using a Car # from cell A1(?) for the name

***yes, kind of. sort database-descending, pull top
value of col A paste to template. Add 1 to value, paste
in cell B4 of template. save as file name = B4***

Copy the data to the master database.

***In my mind, this is not really a "copy". Somehow,
excel is tracking which record is associated with which
template file. For example, I have created a record and
the car# is 20030005. I created another record and the
car# is 20030006. If I open up the template file for
20030005 and change the number to 20030006, it will not
over write the existing 20030006. You end up with 2
records that have 20030006 car#'s with all of their other
data remaining intact.

This step is really an update to the database record or
the creation of a new record, definitely NOT a "copy"
function.

Save (?) the database.

***yes-sort of. the process of updating the database
seems to be- open, write record, save, close. This all
happens in the background (you can see CAR database open
for a couple of seconds, then close) as a part of
the "update template file to database" operation.***

email something to someone.

***yes. it can be either a link to the 2003xxxx.xls file
on the network, or the file itself.****


On to the code. I will try out what you suggest, as far
as selection, option explicit, and the like.


My code works, but it does not work how I want it to work.

I want it to save the file (this works)
I want it to update the database (this does not work)

I want my code to perform exactly the same set of
functions that the built in toolbar buttons perform.

ie- with 1 click of the std toolbar save button, I get a
save AND an update.

(when the above is recorded as a marco in "my button")-1
click gets me just a save.

This is my big problem. There is some underlying/hidden
functionality that the MSEXCEL save button offers that
does not get recorded in the macro (when you record that
button being pushed)

My quest is to find that command:

activeworkbook.updatetemplatefiletodatabase

Steve thanks again. I will have to pick this up in the
morning tomorrow, as it is time for me to go home. If I
have time tonight, I will see if you have replied.

##############################################end# #######








-----Original Message-----
Jason,

Not frustrating - challenging!

Now lets comment on your comments and see where we can
go.
See my comments within your comments...
(read all the way down to "end==============")

The poo poo code is because it was built using the
macro
recorder. I really don't know better, and it works,
so I
left it alone.
Recording is great but usally adds more than you need.
Especially with the select stuff. You'll do yourself a
big favor by
changing those per my previous emails.


QAD8001.xls is the spreadsheet that is created from the
template QAD800.xlt

QAD800.xlt is tied to CAR Database.xls using the
template
wizard. It has cells for data input that tie into the
appropriate columns in CAR Database.xls

When you open the the template (file-new-QAD800) it
creates an .xls called QAD8001.xls You then enter data
on this spreadsheet. Without any code or macros, if
you
click the default save, save as, or close--you get
the "save template file to database" pop-up box (and it
also opens CAR Database.xls in the background). If you
click the "add record" radio button (and OK) it will
add
a line to CAR Database.xls with the data that is in the
named/linked fields (from QAD8001.xls).

This is the desired result - add a record.
Is this working? If not we can make some simple code to
copy data to the
database. Sounds like you only want to copy a single
line, correct me if
I'm wrong.


Also desired is to save that instance of QAD8001.xls
as a
seperate file with the CAR# as the file name. This
will
let other users (and myself) add data in the future,
add
pictures, etc., still update the required fields to the
database, and not overwrite other records/spreadsheets.

Without the save as 1234.xls I will get gobs of
QAD8001.xls's or QAD80011.xls, QAD800111.xls, etc.
From your previous posts it sounds like this isn't
happening. It should be
straight forward to build in a save-as into the code and
extract the name
from anywhere in the sheet.


So, on to the code:

No, I have no option explicit clauses. But, all of the
code I posted here works fine. I am not trying to
debug
any of it. Sorry for the confusion.
Strongly recommend Option Explicit as a general rule.
It helps catch typo's
and other stuff.


The find next macro:
The range I selected (using macro recorder) was
a2..o2002
it sorts on column A (which is a unique CAR number
20030001, 20030002, 20030003, etc.) descending - so
that
the highest number is at the top.

Then it copies that number to QAD8001, pastes it.
QAD8001 adds 1 to the number and pastes it in the CAR
field. So every time I run the macro, I get the last
number used and create a new number for the new record.
(BTW-if the "update template to database" doesn't
occur,
the new number is not put back into CAR Database.xls)


After all that, I jump back to CAR Database.xls, to
close
it out. I really did not want to save it after
sorting,
but I didn't want a "yes, no, cancel" to come up when
you
tried to close it without saving.
Workbook("CAR Database.xls").Close = False
should do this


At this point you only have QAD8001.xls open.

The 2nd macro works fine. It takes the CAR# and does a
save as with that number as the file name. I now have
a
series of files named 2003001.xls, 2003002.xls,
2003003.xls, etc.

Each of those files represents 1 row of data in CAR
database.xls.

When you open 2003002.xls and change or add data, the
hit
the std toolbar save button the "update template to
database" window pops up. If you select update record,
it puts the current info into the 2003002 row of CAR
database.xls.

When you open the same file and use a macro (simply
activeworkbook.save) it does not update any info in CAR
Database.xls you merely get a saved 2003002.xls.
This sounds normal. You'll need to call up an update
function if you want
to change the database. But here you have me confused
because you said
earlier that you want to close the database without
saving???


This is the crux of my problem. When I say "It doesn't
work" in my earlier post, I mean my attempts at
creating
a macro that performs --exactly-- the same functions as
the std built in toolbar save button are unsuccessful.



Steve, I hope some of what I say here is
understandable.
I don't want you to give up in frustration because I am
not communicating properly. From my seat, it is very
easy to know what I want, because I have been at this
for
about 3 weeks (and before that with access data web
pages, and prior to that with plain access).

The bottom line is I need an easy way to input, then
email a corrective action, then get a response and
comment on it, all the while maintaining both a
database
of all the records, and each individual record as a
seperate file.

Suggest that you build a number of macros. Have each
macro just do a small
portion of what you want. Than we can make sure each
module works. Once
that is a go, it is just a matter to call what you want
when you want it.


I will be happy to elaborate further on anything and
everything...
Thanks,
Jason


Now here's my interpretation of what you want.

Open the template
Add data to the template. (One line, or many???)
Save-As using a Car # from cell A1(?) for the name
Copy the data to the master database.
Save (?) the database.
email something to someone.

Get back to me

steve

end============================================== ===





-----Original Message-----
Jason,

Most of your code looks pretty good. But I have added
some questions marked
with ?? and comments !!

(watch out for word wrap!!!!!!!!!!!!!!!)

Do you have Option Explicit at the top of you module
(s).
This helps force
Excel to compile you code and locate problems. It is
also a good idea to
step through the code (use F8) and see what is
happening
line by line.

I am still confused as to where the data is entered
and
stored. Which
workbook is which? (pardon my denseness, I am the kind
that needs to "touch
& feel")

Now you got me hooked, so let's keep going with this.

steve

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR
Database.xls"
Sheets("CAR Database").Select


' ?? What range are you sorting? Looks like a single
cell.

' !! Also you can shorten it to Range("A2").Sort and
get
rid of the select

Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' !! again, get rid of select

' note that if the copy range is more than one cell,
you
still only need to
specify Range("G4")

Range("A2").Copy _

Destination:=Workbooks("QAD8001.xls").Sheets(1).R ange
("G4").PasteSpecial
Paste:= _ Paste:=xlPasteValues


' ?? Not sure what is going on here but you can use
the
above idea.
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows("CAR Database.xls").Activate
Application.CutCopyMode = False

' !! you can replace these 2 lines with

ActiveWindow.Close = True


ActiveWorkbook.Save
ActiveWindow.Close


' !! not sure which book you are working on here
(guess
it is QAD8001)

' !! again you don't need to select

With Range("F1")

..Interior.ColorIndex = 3
..Font.Bold = True

End With


Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename
that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


?? Can you give anything more than "won't work"?

Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and
some
other things I can't remember now.


?? Do you mean that the data doesn't get pasted/added?


The thing that will not happen with my coded saves is
the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it
just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let
me
know.






.

Jason,

a.. Sounds like 15 lines (records) in a single column.
***The template has 15 data entry points that correspond
to columns in the database. They are arranged in a
single column on the template*****


a.. Sounds straight forward
1.. Sort database
2.. Workbooks(template).Sheets(1).Range("B4").Value = _
Workbooks(database).Sheets(1).Range("A1).Value + 1

3.. Workbooks(template).SaveAs Filename:= _
Sheets(1).Range("B4").Value & ".xls"

Code is rough and needs to be amended to your needs.
Save-As using a Car # from cell A1(?) for the name

***yes, kind of. sort database-descending, pull top
value of col A paste to template. Add 1 to value, paste
in cell B4 of template. save as file name = B4***


a.. This is confusing. If the data first goes into the template, than it
needs to
be transferred to the database. This is usually a form of copy. Unless
there
is something else happening that automatically updates the database.
Copy the data to the master database.

***In my mind, this is not really a "copy". Somehow,
excel is tracking which record is associated with which
template file. For example, I have created a record and
the car# is 20030005. I created another record and the
car# is 20030006. If I open up the template file for
20030005 and change the number to 20030006, it will not
over write the existing 20030006. You end up with 2
records that have 20030006 car#'s with all of their other
data remaining intact.

This step is really an update to the database record or
the creation of a new record, definitely NOT a "copy"
function.

Save (?) the database.

***yes-sort of. the process of updating the database
seems to be- open, write record, save, close. This all
happens in the background (you can see CAR database open
for a couple of seconds, then close) as a part of
the "update template file to database" operation.***

email something to someone.

***yes. it can be either a link to the 2003xxxx.xls file
on the network, or the file itself.****


On to the code. I will try out what you suggest, as far
as selection, option explicit, and the like.


My code works, but it does not work how I want it to work.

I want it to save the file (this works)
b.. Need to have a closer look at your code.
I want it to update the database (this does not work)

I want my code to perform exactly the same set of
functions that the built in toolbar buttons perform.

c.. There must be some formulas or code behind the workbook
if you are also getting an update with the save.
ie- with 1 click of the std toolbar save button, I get a
save AND an update.

(when the above is recorded as a marco in "my button")-1
click gets me just a save.

d.. There isn't any underlying functionality behind the save, except
maybe a calculate (you can build that into your code). So unless
it is more than the calculate, than there must be some more code
some where. An event macro in ThisWorkbook maybe.
This is my big problem. There is some underlying/hidden
functionality that the MSEXCEL save button offers that
does not get recorded in the macro (when you record that
button being pushed)

My quest is to find that command:

activeworkbook.updatetemplatefiletodatabase

Maybe you can send the workbooks to me via email (not to the group)
and I can get an up close and personal feel to this. (Hopefully it will
get
past any firewall they might have here.
steve

##############################################end# #######


***The template has 15 data entry points that correspond
to columns in the database. They are arranged in a
single column on the template*****

"jason" <jht@oneboxdotcom wrote in message
...
Steve,

I'll start from the summary:

my comments have ****


Now here's my interpretation of what you want.

Open the template

***Yes***

Add data to the template. (One line, or many???)

***The template has 15 data entry points that correspond
to columns in the database. They are arranged in a
single column on the template*****

Save-As using a Car # from cell A1(?) for the name

***yes, kind of. sort database-descending, pull top
value of col A paste to template. Add 1 to value, paste
in cell B4 of template. save as file name = B4***

Copy the data to the master database.

***In my mind, this is not really a "copy". Somehow,
excel is tracking which record is associated with which
template file. For example, I have created a record and
the car# is 20030005. I created another record and the
car# is 20030006. If I open up the template file for
20030005 and change the number to 20030006, it will not
over write the existing 20030006. You end up with 2
records that have 20030006 car#'s with all of their other
data remaining intact.

This step is really an update to the database record or
the creation of a new record, definitely NOT a "copy"
function.

Save (?) the database.

***yes-sort of. the process of updating the database
seems to be- open, write record, save, close. This all
happens in the background (you can see CAR database open
for a couple of seconds, then close) as a part of
the "update template file to database" operation.***

email something to someone.

***yes. it can be either a link to the 2003xxxx.xls file
on the network, or the file itself.****


On to the code. I will try out what you suggest, as far
as selection, option explicit, and the like.


My code works, but it does not work how I want it to work.

I want it to save the file (this works)
I want it to update the database (this does not work)

I want my code to perform exactly the same set of
functions that the built in toolbar buttons perform.

ie- with 1 click of the std toolbar save button, I get a
save AND an update.

(when the above is recorded as a marco in "my button")-1
click gets me just a save.

This is my big problem. There is some underlying/hidden
functionality that the MSEXCEL save button offers that
does not get recorded in the macro (when you record that
button being pushed)

My quest is to find that command:

activeworkbook.updatetemplatefiletodatabase

Steve thanks again. I will have to pick this up in the
morning tomorrow, as it is time for me to go home. If I
have time tonight, I will see if you have replied.

##############################################end# #######








-----Original Message-----
Jason,

Not frustrating - challenging!

Now lets comment on your comments and see where we can
go.
See my comments within your comments...
(read all the way down to "end==============")

The poo poo code is because it was built using the
macro
recorder. I really don't know better, and it works,
so I
left it alone.
Recording is great but usally adds more than you need.
Especially with the select stuff. You'll do yourself a
big favor by
changing those per my previous emails.


QAD8001.xls is the spreadsheet that is created from the
template QAD800.xlt

QAD800.xlt is tied to CAR Database.xls using the
template
wizard. It has cells for data input that tie into the
appropriate columns in CAR Database.xls

When you open the the template (file-new-QAD800) it
creates an .xls called QAD8001.xls You then enter data
on this spreadsheet. Without any code or macros, if
you
click the default save, save as, or close--you get
the "save template file to database" pop-up box (and it
also opens CAR Database.xls in the background). If you
click the "add record" radio button (and OK) it will
add
a line to CAR Database.xls with the data that is in the
named/linked fields (from QAD8001.xls).

This is the desired result - add a record.
Is this working? If not we can make some simple code to
copy data to the
database. Sounds like you only want to copy a single
line, correct me if
I'm wrong.


Also desired is to save that instance of QAD8001.xls
as a
seperate file with the CAR# as the file name. This
will
let other users (and myself) add data in the future,
add
pictures, etc., still update the required fields to the
database, and not overwrite other records/spreadsheets.

Without the save as 1234.xls I will get gobs of
QAD8001.xls's or QAD80011.xls, QAD800111.xls, etc.
From your previous posts it sounds like this isn't
happening. It should be
straight forward to build in a save-as into the code and
extract the name
from anywhere in the sheet.


So, on to the code:

No, I have no option explicit clauses. But, all of the
code I posted here works fine. I am not trying to
debug
any of it. Sorry for the confusion.
Strongly recommend Option Explicit as a general rule.
It helps catch typo's
and other stuff.


The find next macro:
The range I selected (using macro recorder) was
a2..o2002
it sorts on column A (which is a unique CAR number
20030001, 20030002, 20030003, etc.) descending - so
that
the highest number is at the top.

Then it copies that number to QAD8001, pastes it.
QAD8001 adds 1 to the number and pastes it in the CAR
field. So every time I run the macro, I get the last
number used and create a new number for the new record.
(BTW-if the "update template to database" doesn't
occur,
the new number is not put back into CAR Database.xls)


After all that, I jump back to CAR Database.xls, to
close
it out. I really did not want to save it after
sorting,
but I didn't want a "yes, no, cancel" to come up when
you
tried to close it without saving.
Workbook("CAR Database.xls").Close = False
should do this


At this point you only have QAD8001.xls open.

The 2nd macro works fine. It takes the CAR# and does a
save as with that number as the file name. I now have
a
series of files named 2003001.xls, 2003002.xls,
2003003.xls, etc.

Each of those files represents 1 row of data in CAR
database.xls.

When you open 2003002.xls and change or add data, the
hit
the std toolbar save button the "update template to
database" window pops up. If you select update record,
it puts the current info into the 2003002 row of CAR
database.xls.

When you open the same file and use a macro (simply
activeworkbook.save) it does not update any info in CAR
Database.xls you merely get a saved 2003002.xls.
This sounds normal. You'll need to call up an update
function if you want
to change the database. But here you have me confused
because you said
earlier that you want to close the database without
saving???


This is the crux of my problem. When I say "It doesn't
work" in my earlier post, I mean my attempts at
creating
a macro that performs --exactly-- the same functions as
the std built in toolbar save button are unsuccessful.



Steve, I hope some of what I say here is
understandable.
I don't want you to give up in frustration because I am
not communicating properly. From my seat, it is very
easy to know what I want, because I have been at this
for
about 3 weeks (and before that with access data web
pages, and prior to that with plain access).

The bottom line is I need an easy way to input, then
email a corrective action, then get a response and
comment on it, all the while maintaining both a
database
of all the records, and each individual record as a
seperate file.

Suggest that you build a number of macros. Have each
macro just do a small
portion of what you want. Than we can make sure each
module works. Once
that is a go, it is just a matter to call what you want
when you want it.


I will be happy to elaborate further on anything and
everything...
Thanks,
Jason


Now here's my interpretation of what you want.

Open the template
Add data to the template. (One line, or many???)
Save-As using a Car # from cell A1(?) for the name
Copy the data to the master database.
Save (?) the database.
email something to someone.

Get back to me

steve

end============================================== ===





-----Original Message-----
Jason,

Most of your code looks pretty good. But I have added
some questions marked
with ?? and comments !!

(watch out for word wrap!!!!!!!!!!!!!!!)

Do you have Option Explicit at the top of you module
(s).
This helps force
Excel to compile you code and locate problems. It is
also a good idea to
step through the code (use F8) and see what is
happening
line by line.

I am still confused as to where the data is entered
and
stored. Which
workbook is which? (pardon my denseness, I am the kind
that needs to "touch
& feel")

Now you got me hooked, so let's keep going with this.

steve

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR
Database.xls"
Sheets("CAR Database").Select


' ?? What range are you sorting? Looks like a single
cell.

' !! Also you can shorten it to Range("A2").Sort and
get
rid of the select

Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' !! again, get rid of select

' note that if the copy range is more than one cell,
you
still only need to
specify Range("G4")

Range("A2").Copy _

Destination:=Workbooks("QAD8001.xls").Sheets(1).R ange
("G4").PasteSpecial
Paste:= _ Paste:=xlPasteValues


' ?? Not sure what is going on here but you can use
the
above idea.
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows("CAR Database.xls").Activate
Application.CutCopyMode = False

' !! you can replace these 2 lines with

ActiveWindow.Close = True


ActiveWorkbook.Save
ActiveWindow.Close


' !! not sure which book you are working on here
(guess
it is QAD8001)

' !! again you don't need to select

With Range("F1")

..Interior.ColorIndex = 3
..Font.Bold = True

End With


Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename
that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


?? Can you give anything more than "won't work"?

Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and
some
other things I can't remember now.


?? Do you mean that the data doesn't get pasted/added?


The thing that will not happen with my coded saves is
the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it
just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let
me
know.






.