Modules & VBA :: Saving A Table On Certain Day Of Every Week
Jan 21, 2015
I am trying to find out what VBA Code I should use for a way to save a main table name: Master_Template on a certain day of the week (weekly) to happen automatically on close of the database - of course on that certain day of the week.
I have a form to add a record to a table. How can I give the user a way to close this form without saving the just created record.
I tried
If me.dirty then DoCmd.RunCommand acCmdDeleteRecord endif
This works, but gives a messagebox in return to confirm the command, and I don't want that. And I try to avoid sendkeys. I also don't want to change the options of access.
I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.
The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.
I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.
I am trying to write some VBA to convert a date into a week number.
My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:
I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.
Start day for the week is Sunday. End day is the Saturday.
Some examples to check against:
22nd December 2013 = Week 52 2013
29th December 2013 = Week 1 for 2014 ----
21st December 2014 = Week 52 for 2014
28th December 2014 = Week 1 for 2015 ----
20th December 2015 = Week 52 for 2015
27th December 2015 = Week 1 for 2016 ----
25th December 2016 = Week 53 for 2016
1st Jan 2017 = Week 1 for 2017 -----
Tried various methods already
iso 8601, wrong week start day
intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.
I have found multiple ways of calculating the week of a month for a given date. Now, I want to reverse it, i.e. given a month and week and day of week calculate the date.
Note that in week 1 and last week, there will often be days with no value.
I am trying to return a value in an expression (call it FundedPeriod): CurrentWeek, CurrentMonth, PreviousMonth, based on a date value in field [funded_date].
Here are the criteria I am using:
Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())
Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())
Based on example, I expanded upon that and came up with this:
Code: Function FundedPeriod(FD) ' returns CurrentWeek or CurrentMonth or PreviousMonth or None based on FundedDate (FD) criteria Dim ret As Boolean ret=CurrentWeek
[Code] ....
Am I in the ballpark w/ this in order to return:
CurrentWeek or CurrentMonth or PreviousMonth
so in the query expression I think I would type FundedPeriodName: FundedPeriod([funded_date])
Im trying to create a command that will create a backup of my access database once every week. here is the code I've got so far. it works, but not once a week but every time I start the access:
MsgBox ("Hello! Starting Backup procedure")
Dim DateOfBackup As Date Dim strSourcePath As String Dim strSourceFile As String Dim strBackupFile As String Dim SQL As String
I would like to enter a week dates forward after looking up the last entered date. i have working codes that looks up the last date and increments it by one and enter the date into a new field.
Here is what i have:
Private Sub Form_AfterUpdate() Dim dtmNextDate As Date Dim dtmLastDate As Date Dim SDate As Date LastDate = Nz(DMax("[SDate]", "schedule"), _ DateAdd("d", -1, Date)) NextDate = DateAdd("d", 1, LastDate) CurrentDb.Execute "INSERT INTO Schedule(SDate) VALUES ('" & NextDate & "');" End Sub
enter dates into 5 new fields instead of one using a loop maybe.
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
My new table "tblRRP" Could contain Year int, Week int, CountryCode nvarchar (2), ProductId nvarchar (15), RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
come up with the below code to create PDF documents based on a query. It has been working great but I have a change that I need to make and can't figure it out. The code below creates all the documents in one folder "serverD$DocumentsInvoices" but I need it to create a separate folder for each PDF so that the file (Invoice1234.pdf) will be in the folder (serverD$DocumentsInvoicesInvoice1234Invoic e1234.pdf). That folder does not exist, so I need to create it and then save the PDF into it.
Code:
Dim db As DAO.Database Dim rst As DAO.Recordset Dim qdf As QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("Invoice_Report") Set rst = qdf.OpenRecordset
1. open outlook mail; 2. copy the email details into the database; 3. save the attachment into a E: drive location; 4. move the email to another folder.
1,2 and 4 were working fine until I tries to save the attachment and got stuck on the SaveAs line.
Dim Ola As Outlook.Application Dim Nsp As Outlook.NameSpace Dim pf, Inbox, ib, newdest As Outlook.MAPIFolder Dim msg As Outlook.MailItem Dim Atts As Outlook.Attachments
Ok. So I have learned my lesson on saving images into an Access DB. Now I'm using VBA (another good learning experince) to save the images back to a folder. I have a code working that will save the images but I need it to also rename the images after saving.
The section "MyFile = OldFile" needs to reference the rsChild Attachment file name...
Quote:
Private Sub Command859_Click() Dim db As DAO.Database Dim rsParent As DAO.Recordset2 Dim rsChild As DAO.Recordset2
Having problem with loops. The inner loop updates a table. The outer loop pulls the record number from the "tblChangeOrderTable_Edit_Count" and is assigned to strRecordID . The inner loop uses strRecordID to find the right record. I keep getting errors like (Object variable or With Block variable not set.)
Code: Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim db As Database, rs As Recordset, rs1 As Recordset2 Dim Criteria As String Dim strAns1 As String Dim strAns2 As String Dim strCount1 As String Dim strAns3 As String
I'm trying to figure out why I can email a report as a PDF, but not save it as a PDF using the code below? When I save, I get the dreaded error 2501.
The searching I did for this said to uninstall and reinstall printers (I've done that - seems to have no effect), or took issue with filtering the report with a where clause (but if that was an issue, why does it email a PDF just fine?).I'd just like to be able to save a report as a PDF to a specified location.
CB Developing in: Access 2010; Win7 For use in a mixed 2007/2010 XP/7 environment
Code:
Private Sub EmailDietCardBtn_Click() On Error GoTo MyErrorHandler Me.Refresh Dim stReport As String Dim stWhere As String Dim stSubject As String Dim stEmailMessage As String Dim stCaption As String Dim myPath As String
I have some code that resides in a button on a report that allows me to save a PDF version of this report. My problem is that if i have the destination directory open on windows explorer, i get a runtime 2501 "The output To action was cancelled" error.Is there any way to check if the directory is already open, and cancel the output to, or close the window to avoid this error?
Here is my code:
Code: Private Sub CmdSavePDF_Click() Dim MyFileName As String Dim MyPath As String 'Dim CompanyName As String
I have a form I would like to close without saving the record using a button. here is the code I have but it closes the form and it also writes the record to the table.
Code: Private Sub ClosewoSave_Click() Cancel = True Me.Undo DoCmd.close DoCmd.OpenForm "frmMenu" End Sub
I found in a thread that Cancel = True would not write the record.
I have a main form called frmAF54. In this main form I have a subform named frmPassdown. The subform record source is from a table called tblCards. This table contains many different cards. I have a combobox called lstOpCard in the main form as well. The combobox lists all of the different cards in "tblCards". I have a button called "buttonFilter".
A user opens the form and clicks (or selects) the different cards in the combo box "lstOpCard" that they want to filter for. Once their selections are made, they can click the "buttonFilter" button and the subform "frmPassdown" will be filtered based off of their selections.
The filter is applied using the IN() function as shown below: Me.AF54PassDown.Form.Filter = "[OpCardID] IN ( " & sTemp & " )" --sTemp is a string containing the keyIDs of the cards selected.
My problem is that people will be opening and closing the form throughout the day / week. When the form closes, it loses the applied filter. How can I have my filter on the subform be saved after the form is closed and the filter is still applied when the form is opened later?
With this construction I don't have enough control over the mail, and worse, I get an Outlook message when the mail is sending which I have to answer.
2. option
Code: Dim oMail As MailItem Dim oAtt As Attachment
Set oApp = CreateObject("Outlook.application") Set oMail = oApp.CreateItem(olMailItem) DoCmd.OpenReport ReportName:=sReport, View:=acViewPreview, WindowMode:=acHidden Set rpt = Reports(sReport)
[code]...
with this construction I have to enter an existing report.pdf on my filesystem (bold line), but I want to pass the rpt object straight to the mail as an attachment.