Backup Button

Cowboy_BeBa

Registered User.
Local time
Tomorrow, 04:38
Joined
Nov 30, 2010
Messages
188
hey all

im working on a backup button for my db, in a nutshell it does a number of things to summarize the months transactions, im coding in vba and am stuck in 2 places,

Copy Data-
another thing i need to do is change the stock on hand value of tblIngredients, deducting total productions from tblProductions and adding purchases from tblPurchases, i figured this would be easy as i already have a query called qryStockControl which works this out (among other things), so all i need to do is get the button to copy all the data from qryStockControl.ActualStockInHand, to tblIngredients.ingStock, each object has a value called ingID, which is what i want it to link to

ive created an update query in design view (sql below) and the button will use docmd.openquery to run it, but when i try running it manually it keeps telling me the operation must use an updateable query, no idea whats going on

UPDATE tblIngredients INNER JOIN qryStockControl ON tblIngredients.ingID = qryStockControl.ingID SET tblIngredients.ingStock = [qryStockControl].[ActualInHand];

anyone know what, or if theres another way to accomplish what i need to do?

File copy- Edit: This whole problem has now been fixed up, solution, in a nutshell, was BackDate = Format(Date, "Medium Date")
one of the functions the button performs is to backup the backend of the db
SourceFile = "C:\WAG\backend\WAG BE Test.accdb"
DesFile = "C:\WAG\backups\WAG " & BackDate & ".accdb"
FileCopy SourceFile, DesFile

backdate is a date value that =now()

this isnt working, however in a test i ran i changed backdate to an integer and made it =4, the code then worked perfectly, i believe the problem is caused by the date field having charachters that might not be allowed in the file name, ive tired to reformat the date by using
BackDate = Format(BackDate, DD/MM/YYYY)

But i keep getting a runtime error 6, overflow
anyone know what im doing wrong?
EDIT:
just changed the format to "DD/MM/YYYY", instead of dd/mm/yyyy,set up a message box to pop out with the value after the format function, works fine, but now am getting error 76 path not found, when it gets to the filecopy line.....

am guessing that as the date has slashes in it the filecopy is assuming they are folders and is crashing coz it cant find C:\WAG\backups\WAG \14\02\2011, but not sure how to remedy this
 
Last edited:
you can't format BackDate this way. this is invalid file name.
it should be formated as ddmmyyyy, or dd_mm_yyyy
 
thanks smig, changed to format to ddmmyyyy and dd_mm_yyyy, set up a message box to display the variable afterwards, and its still showing me 14/02/2011, not sure why but the format doesnt seem to be working properly
 
okat, so ive solved the first problem with the date
for anyone whos interested i simply had to do this
BackDate = Format(Date, "Medium Date")
returns 14-feb-2011, which is perfect

now all i gotta do is work out how to copy the date from the query to the table
 
sorry if I wasn't clear enough.
in the table you can save your date in any format you want.

you need to change the format for the File copy string
dd/mm/yyyy are not valid for filenames, and will not be identified as folders (the folder slash is directing the other way).
also File copy will not craete folders

what do you need the table for ? copying a set of files to the backup ?
if so:
Code:
dim rs as DAO.recordset
dim db as DAO.database
 
db = CurrentDB
 
rs = OpenRecordset("YourTable")
with rs
 .movefirst
 do while not .EOF
 FileCopy .......
 .movenext
  next
  .close
end with
set rs = Nothing
 

Users who are viewing this thread

Back
Top Bottom