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
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: