Can any one help me update my database?

[dfitting]

Please help if you can. I can not find an answer anywhere.....

I have created an Excel form (*.xlt) file that will let a
user add a record to a database. In order for the user to
add the record they manually have to "Save As" the form.
When they do this, a dialog box pops up asking them if
they want to create a new record in the database or not,
once they say OK to this box, the "Save As" dialog box
pops up and they are prompted for a file name.

I would like to put all these steps into a macro which
would automatically assume that they want to create a new
record and would save the file as a default name without
displaying the SAVE AS box or the alert that tells the
user that a file by that name already exists.

I have tried recording my steps but the only thing that
gets recorded is the SAVE AS procedure and when I run the
macro it will save the form but won't add a record to the
database. I obviously need to manually add some code to
my macro but I don't know where to find it.

Thanks very much.
Dan

 

[okrob]

Please help if you can. I can not find an answer anywhere.....

I have created an Excel form (*.xlt) file that will let a
user add a record to a database. In order for the user to
add the record they manually have to "Save As" the form.
When they do this, a dialog box pops up asking them if
they want to create a new record in the database or not,
once they say OK to this box, the "Save As" dialog box
pops up and they are prompted for a file name.

I would like to put all these steps into a macro which
would automatically assume that they want to create a new
record and would save the file as a default name without
displaying the SAVE AS box or the alert that tells the
user that a file by that name already exists.

I have tried recording my steps but the only thing that
gets recorded is the SAVE AS procedure and when I run the
macro it will save the form but won't add a record to the
database. I obviously need to manually add some code to
my macro but I don't know where to find it.

Thanks very much.
Dan

Post what you have recorded...

 

[dfitting]

Post what you have recorded...- Hide quoted text -

- Show quoted text -

When I recorded it simply gave me the

activeworkbook.save
activeworkbook.close

It did not show the message box that popped up asking me to choose
between Updating the database or Continue without updating the
database.

If I close the workbook using activeworkbook.close it simply closes it
without updating the database.

 

[okrob]

When I recorded it simply gave me the

activeworkbook.save
activeworkbook.close

It did not show the message box that popped up asking me to choose
between Updating the database or Continue without updating the
database.

If I close the workbook using activeworkbook.close it simply closes it
without updating the database.- Hide quoted text -

- Show quoted text -

I assume, you will want to show a different file name for each
record. I use a variation of this to copy a worksheet from my main
workbook into a separate file. You can set up a range on the
worksheet that is unique to each record and substitute it for the date
as well...


Sub ws_Save()
Dim tdate As String
tdate = Format(Date, "ddmmyyyy")
Dim rng1 As String
rng1 = Range("A1").Value ' You can change this range to the one you
want.
Dim FN As Variant
With Application
FN = ("C:\YourPath\" & rng1 & tdate & ".xls")
'<=== Change "YourPath" to where you save the files
If FN <> False Then
ActiveWorkbook.SaveAs FN
End If
End With
Activeworkbook.Close
End Sub