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.

View Replies


ADVERTISEMENT

Modules & VBA :: Adding Record To A Table - Closing A Form Without Saving

Nov 26, 2013

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.

View 2 Replies View Related

Reports :: Inserting Page Break To Show Week By Week Pages

Feb 11, 2015

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.

View 8 Replies View Related

Modules & VBA :: Converting Dates Into Week Numbers

Oct 2, 2013

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.

View 14 Replies View Related

Modules & VBA :: Calculating Week Of A Month For Given Date

Mar 3, 2014

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.

View 2 Replies View Related

Modules & VBA :: WeekVal -> First Day Of Week For Current Year

Feb 10, 2015

Given a specific week no. for the current year... I need to return the first date of that week....

For example : week4 of this year (returns) 19/1/2015

View 2 Replies View Related

Modules & VBA :: Month And Week Criteria In A Module

Sep 23, 2013

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())

Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1

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])

View 5 Replies View Related

Modules & VBA :: Calendar Database - How To Get Week To Start On Monday

Mar 17, 2015

I have found a calendar database witch I wanna use in one of my own databases.

But.. on the form the weeks start on sunday, I want the week to start on Monday but I can't figure out how to do it.

I've posted the original database as an attachment. I'd like to know what I have to change in the code and/or in the form to get what i want.

View 11 Replies View Related

Modules & VBA :: Command That Will Create Backup Of Access Database Once Every Week

Feb 24, 2014

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

[code]...

View 2 Replies View Related

Modules & VBA :: Insert One Week Dates Ahead Of Last Enter Date?

Jan 13, 2015

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.

View 6 Replies View Related

Week Commencing Day From Week Number

Nov 6, 2006

I have a query that groups records by date into week numbers using the following expression.

WeekNumber: DatePart("ww",[Date],2)

I would prefer to display the result as the week commencing Monday date.

How?

View 13 Replies View Related

Queries :: Table Of End Of Week Sales - Query Where There Is No Match

May 4, 2014

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.

tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;

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.

View 6 Replies View Related

Modules & VBA :: Create Directory Before Saving PDF

Nov 5, 2014

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

[code]...

View 3 Replies View Related

Modules & VBA :: Saving Outlook Attachment

Jun 16, 2013

The below code is intended to:

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

[Code] ....

View 6 Replies View Related

Modules & VBA :: Saving Images Into Access DB

Jul 1, 2014

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

[code]....

View 2 Replies View Related

Modules & VBA :: Loops Saving Data

Nov 19, 2014

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

[code]....

View 12 Replies View Related

Modules & VBA :: Word Doc Saving Path

Sep 30, 2013

I am saving my word doc thru below vba code

.......
..........
doc.SaveAs ("Customers.doc")
..........
.......

Which is saving here :

C:UsersashfaqueDocuments

I need to save it to desktop or somewhere I want.

How can I specifically mention the path in my vba code to save this document at the desired place?

View 3 Replies View Related

Tables :: Calculated Field In Table - Display Week Number

Jan 13, 2014

I am trying to create a calculated field in my table, I am trying to use the following code:

DatePart("ww",[Gas]![Date Opened])

I receive the following error message: "The expression DatePart("ww",[Gas]![Date Opened]) cannot be used in a calculated column."

Access 2007 - I really need to be able to display the week number in the table, based on the Date Opened field.

View 6 Replies View Related

Modules & VBA :: Why Does Emailing A PDF Work But Saving A PDF Doesn't

Jul 24, 2013

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

[code]...

View 11 Replies View Related

Modules & VBA :: Check If Directory Is Open Before Saving PDF

Aug 28, 2013

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

[code]...

View 1 Replies View Related

Modules & VBA :: Close Form Without Saving Record

Jun 3, 2015

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.

View 4 Replies View Related

Modules & VBA :: Navigation Control Subform Saving

Jul 18, 2013

Access 2010. one of the forms i load into a navigation form is in datasheet view and some vba re-sizes all the columns as it loads in.

However, this then means that when i try to navigate away to another form i always get prompted whether to save the form or not.

because the form is in a navigation subform i can't use:

Code:
DoCmd.Save acForm, Form_NavigationForm.NavigationSubform.Form.Name
' or this...
DoCmd.Save acForm, "my_form"

in both cases i get an error : the object my_form isn't open

View 1 Replies View Related

Modules & VBA :: Saving Combobox After Form Is Closed And Re-opened

Sep 26, 2013

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?

View 1 Replies View Related

Modules & VBA :: Send Pdf With Outlook Without Saving Actual File

Oct 13, 2014

I want to send an e-mail with a report as attachment.On the internet i have found two options, and both are not the best solutions.

1. option

Code:
DoCmd.SendObject acSendReport, sReport, acFormatPDF, sMailAdres

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.

View 2 Replies View Related

Modules & VBA :: Saving Picture To Specific Folder With Customized Name?

Feb 8, 2014

I have below module to save picture under named folder:

Code:
Option Compare Database
Public Enum acFileType
acPicture = 1
acFiles = 2
End Enum

[Code] ....

But how to save the picture with customized name, say "Materials 0001.Jpg"

View 7 Replies View Related

Modules & VBA :: Remove Special Characters From File Name (when Saving)

Feb 20, 2014

I use the below code as part of a database which creates documents and then saves them using data from the form fields.

Every now and again one of the form fields contains a "special" character such as /,@,& and this prevents the file from saving.

Is there a simple addition to the below code to remove special characters from the file name if there is one present?

It causes all sorts of issues as many people don't realise that there has been a problem until it is too late.

Code:
objWord.ActiveDocument.SaveAs2 FileName:="C:UsersPublic" & Forms![Front Page]![Site 2 Name] _
& " " & Forms![Front Page]![Combo79] & " " & "O2" & ".doc"
objWord.ActiveDocument.Close

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved