Modules & VBA :: Query Records By Current Date Then Email Results On Button Click

Feb 26, 2014

I am trying to query my records by the current records selected date then send the results in the body of an email on click. I believe I am close but I think there is a problem with the date format because I am getting 3421 Data type conversion error. Here is what I have:

Code:

Private Sub eMail_Click()
On Error GoTo EH
Dim dbExceptions As Database
Dim rstExceptions As Recordset
Dim dbDate As Database
Dim rstDate As Recordset

[code]...

View Replies


ADVERTISEMENT

Modules & VBA :: Create Multiple Records Based On Date Range - Button Click Event

Jul 31, 2014

I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.

We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.

The problem: For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.

I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.

View 3 Replies View Related

Modules & VBA :: Word Document Load When User Click Button And Perform Email Merge

Oct 7, 2013

I have a Word 2010 document linked to an Access 2010 data source. When a user clicks a button in Access, the Word document loads and performs a email merge using the below VBA code:

Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True

[Code] ....

However, as the .mailsubject part is not in the loop it is only retrieving the first Return Code. I have tried to integrate in the loop to no avail. Also, how do I add static text to the Subject, I need something like "Your Return Code" + "Return Code"..

View 6 Replies View Related

Modules & VBA :: Form Bound To A Table - Button Click And Then Date / TimeStamp

Jun 30, 2014

I have created a form that is bound to a table.

There is a button on the form that allows users to send email with the form as an attachment in pdf. I'd like to create a date/time stamp in another table called tblLog. Trouble is the code works uptil sending emails but it doesn't record the stamp.

Heres what I've done so far.

Private Sub cmdEmail_Amd_Click()
On Error GoTo cmdEmail_Amd_Click_Err
DoCmd.OpenForm "frmAmendment_Master", , , "[Record_ID]=" & Me.Record_ID.Value
DoCmd.SendObject acForm, "frmAmendment_Master", "PDFFormat(*.pdf)", "", "", "", "Amendment Form" & " " & Surname & " " & Firstname, "", True, ""

[Code] .....

View 3 Replies View Related

Getting Current Time Into Field With Click Of Button.

Mar 13, 2006

I have been playing with the sample DB alastair69 donated in the sample db section of the site. "The nice timer function".

If you don’t start the timer right away you don’t have the correct time.

I haven’t figured out how to add a reset type of button to display the most current time in the start time field. Can any one help me out please?

View 4 Replies View Related

Modules & VBA :: Send Query Results In Table In Body Of Email

May 24, 2014

I have created some code to get the records of a query and it puts them in the body of the email but it doesn't format it well for example the email looks like

James | halliwell | 31
Leanne | smith |27
Alexis | smith |8

I would like it to be in a table is this possible,

Code:
Public Function SendEmail()
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Set MyDB = CurrentDb

[Code] .....

View 3 Replies View Related

Modules & VBA :: Loop Through Query Results To Extract Email Addresses

Jun 27, 2013

I am trying to loop through a query results to extract email addresses from a query result.

I have the following code

NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String

[Code] .....

If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?

Someone on another post said that sql uses US date format so i tried converting to us date and still no results?

View 6 Replies View Related

Modules & VBA :: Append Query On Click Of Command Button

Oct 18, 2013

I would like to make an append query to piece together multiple tables into a holding table, once the command button is clicked.

a) how to make an append query in vba,
b) How to call an append query on click of a command button?

View 1 Replies View Related

Modules & VBA :: Click Button Setup - Run A Query And Then Export As Fixed Width TXT File

Oct 15, 2014

Basically im trying to setup a click button that will export my query as a text file ( the text file will then be fixed width and i have already setup the specifications for this).

I think i get how to complete the export part as below

OutFilePath = "file location i want the data to be exported to"

DoCmd.TransferText acExportFixed, "Welcome output query Export Specification", WelcomeOutput, OutFilePath, True

I just dont know how to run the query and link it to the export.

View 2 Replies View Related

General :: Program Crashes When Sending Email In Response To Click On Send Button

Nov 9, 2012

I am sending an email using SendObject. Sometimes it works, and sometimes it makes the computer freeze up with no error message. I have tried this with Outlook running or not running, seems to make no difference.

Code:

'The sub procedure below sends e-mail in response to a click on the Send button.
Private Sub SendMessagesButton_Click()

'For Access, define some object variables and make connections.
Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = myConnection

[Code] ....

I have added some MsgBox () to narrow down where it crashes. It is after 'Five' and Before 'Six'. On the line:

Set appOutlookRecip = .Recipients.Add(eMailAddress)

I am mystified as to why it works OK sometimes, and not others. The email address being used is valid.

View 2 Replies View Related

Modules & VBA :: Form Which Has Button To Email Data Out In Standard Email Message

Jan 15, 2014

I have a form which has a button to email the data out in a standard email message.

Private Sub Command60_Click()
Dim MyDb As dao.Database
Dim rsEmail As dao.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

[code]...

This works well enough, however, FIELDS 11 through to 16 contain the venue address. This is all we ll and good if every field of the venue address is populated. here are times when not all of the fields are populated, for instance, the address might only be 5 lines.I know I can do this using IIf statements on a report, but how can i achieve the same thing for the email.

View 6 Replies View Related

Modules & VBA :: Use Macro Button To Pass Query Results To Listbox

Sep 30, 2013

Is it possible to have a macro button to pass query results to a listbox? I have a database where I have some fields with dates. What I need to do is to show a list of all cases that have a start date (and also the end date). So I will have 2 buttons, one that says "show started cases" and second one "show closed cases". If I press the first button, it will run a query and show only those cases that have a date filled in the started case field. I made a button that shows the results in a report, but I would like to have it show up in a listbox so I can double click it and go straight to the case.

View 14 Replies View Related

Modules & VBA :: Create A Form Button That Will Automatically Email Each Row Of A Query Result

Jul 22, 2015

I am trying to create a form button that will automatically email each row of a query result to myself. At first the VBA code worked fine with a standard query. However when I use it with a query that contains a reference to a combobox form such as "<=[Forms]![Reminder]![Monthsleft].[Value] And >=0" I get the 3061 run-time error and "Too few parameters. Expected 1." I have included the VBA code below.

Private Sub Command9_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

[code]....

If you intend to renew the lease, terms and conditions will need to be submitted for ECC for approval (regardless of changes or not in lease rates). If the terms have yet to be confirmed, it is important to begin the negotiation process as soon as possible with a target to provide the ECC submission at least two months prior to the commencement date of the renewed lease. To ensure sufficient time for ECC approval before the contract expiry date, please prepare the ECC paper and obtain necessary endorsements. Submission details can be found here. The ECC submission template and PSD Questionnaire could be found from this link

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False

.MoveNext
Loop
End With
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

View 2 Replies View Related

Email ONLY To Current Subform Records

Jun 9, 2006

I have been reading quite a bit on this and other forums about email automation and looping queries to gather email addresses. However, I am unable to restrict my list of email addresses to only that are currently being viewed in a subform.

An image of my form is attached. I have 3 tables being used:

Events
Contacts
EventAttendance


The EventAttendance Table is a join table to determine which Contacts attended which Events.

When I try to loop a query to capture email addresses, I am only able to return email addresses for the entire query (all people attending all events) not just those that are listed in the subform for the current event.

How do I adjust the code below to only use the emails that match the EventID I am viewing?


Private Sub Command19_Click()

Dim rs As New ADODB.Recordset
Dim strEmail As String

rs.Open "EventAttendance Query", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

strEmail = ""
Do While Not rs.EOF
strEmail = strEmail & rs!Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject , , , strEmail, , , "test", "Test", True

rs.Close
Set rs = Nothing


End Sub

View 11 Replies View Related

Adding Records Only On Button Click?

Oct 26, 2005

Adding Records Only On Button Click?

I am building a database with a single table of records. I have created a form to allow new entries to be made to the table in a user-friendly way.

The problem I’m stuck with is how do I restrict the addition of new records on the form? I want the new record to only be added to the table upon the click of a button, but at present if I tab through, or exit the form the record is saved anyway.

Not all fields on the form are required input so I’m thinking that validation of every field is not the way to go.

I only ever want the record to be saved if a user clicks on a button and under no other circumstance.

I've been trawling through the posts back to about page 15 so any help would be appreciated.

Ksan

View 4 Replies View Related

Forms :: Update Records On Button Click?

Jul 3, 2014

I have a form listing out a bunch of clients. There is a button associated with each client that pulls open a new form with additional client information. There is information that is calculated on the backend and stored in the database when a user is inserted or different fields updated (i.e. there are reports that are due different time frames after the clients admission date. I automatically populate the database with those dates once the clients admission date is updated).

The problem I am having is that when I enter a new client or update a current client with new information and then click the button to go to their details page, those auto-populated dates are not populated. I am calling a subroutine that is within a module when the button is clicked. The subroutine works fine at other instances throughout the application so I know there is no issue with the code. I even call it if they click the close button, but before the information is auto-populated, I had to run a Me.Requery on the close button. I think this is because the records are not actually being updated in the database. I even have a DoCmd.Save acForms, "Clients" command before running the Call updateDatabase subroutine, but that doesn't do the trick. I can't run the Me.Requery after the click of the details button because when I do that it always opens the details form to the first ORDER BY record. I think that I need to update the selected record and re-call it in order for this to work.

View 5 Replies View Related

Modules & VBA :: Run A Function On Button Click

Sep 24, 2013

I try to run a function off of a button click.

The code is

Private Sub Command_Click()
Run fuctionname()
End Sub

The code is ran, but then I get a msg box : Run-time error '2517':

Microsoft Office Access can't find the procedure '.'

View 5 Replies View Related

Forms :: Continuous Form - Populating Date With Button Click

Oct 14, 2013

So I have a continuous form and I have a button that is pressed when a job is released and it record a date in a field. I wanted to turn that button to disabled after pressed but after lots of reading found that it couldn't be done in a continuous form due to it disabling all buttons . So I decided to just make the code populate the field only if it was a null value so that it wouldn't overwrite a value if someone pressed it more than once. Well the null is my downfall in all this. I cannot make it compare and populate the date when the button is pressed. I have tried all I can find on the net and all I can think of.

View 11 Replies View Related

Using A Button To Add A Year To The Current Date.

Jan 26, 2006

Hello,
i have an expiry date for my members and each year they need to renew it, so i would like to have a button that when clicked adds 1 year to the date.

I currently have this query
=DateAdd("yyyy",1,[Commenced Training])

as there membership will expire one year from their joining date.
this is currently shown for hte text box expiry date.

however i cannot work out how to make another one for the button so that when the button is pressed it adds another year and stores its.

the image shows my example

thanks

View 2 Replies View Related

Modules & VBA :: Generate Tables At The Click Of Button?

Dec 9, 2014

I am required to write a code that will allow me to generate a table at the click of a button. Is there such a thing?

The table name should be Table1, and it contains 7 columns.

First column: Auto number (PK)
Field1: Memo
Field2: Memo
Field3: Memo
Field4: Memo
Field5: Memo
Field6: Memo

View 2 Replies View Related

Modules & VBA :: Click Button To Expand Whole Menu

Sep 17, 2014

the login is working perfectly, once It logs in there is a button to expand all that is based in JS. I can't seem to work out how to get it to click the button to expand the whole menu, latest code is below:

Code:

Option Explicit
Function Checkpage()
Dim IE As Object
Dim lform As Object
Dim Document As Object
Dim item As Object

[code]...

View 3 Replies View Related

Modules & VBA :: How To Run Multiple SQL Commands On Click Of A Button

Jul 25, 2013

I want to run multiple SQL commands on click of a button. I have these three command,

CurrentDb.Execute "INSERT INTO UserMadeDeviceT(Product, ORESector) " & _
" VALUES ('" & Me.D_NewDeviceTxt & "', '" & Me.D_ORECmb & "')"
CurrentDb.Execute "INSERT INTO UserMadeDeviceT (RatedKilowattPower, KilogramWeight)" & _
" SELECT RatedKilowattPower, Weight FROM UserSelectedComponentT " & _
" WHERE [TotalComponent] = '" & D_ComponentNameCmb & "'"
CurrentDb.Execute "INSERT INTO UserMadeDeviceT (Cost) SELECT SUM (EuroCost) FROM UserSelectedComponentT"

all of these work indevidually and return the correct value but the add three rows to the table. How can I combine these three commands into one so that it will only add one entry?

View 8 Replies View Related

Modules & VBA :: Can Only Click Button Once Per Form Load

Feb 3, 2014

I have created a button that works fine however it will only work one click per form load and i cant seem to figure out why.

Code:
Private Sub BntExpired_Click()
Dim QryAllCourses As recordset
Do Until ExpiryDate > Date Or ExpiryDate = ""
MsgBox ([FirstName].Value + " " + [SecondName].Value + "'s course in " + [CourseName].Value + " has expired")
recordset.MoveNext
Loop
Exit_BntExpired_Click:
Exit Sub
Err_BntExpired_Click:
MsgBox Err.Description
Resume Exit_BntExpired_Click
End Sub

View 8 Replies View Related

Queries :: Update Multiple Table Records With Button Click Event

Oct 29, 2014

I have a table with many records, using a form with an update button click event, I would like to update ALL records where the Item in the table = the Item in the form.

There are 6 checkboxes and 6 text boxes that will need to be updated, but right now I am just trying to test if i can even change one text box to keep it simple. I have tried to start by using this:

Code:
Dim mySQL As String
Dim ItemNo As String
Dim SO As String

mySQL = "UPDATE BT200 SET Part = '" & SO & "' WHERE Item = " & ItemNo
DoCmd.RunSQL mySQL

So every time i run this command button, it gives me a prompt "You are about to update 0 row(s)". Nothing gets updated at all, I am clearly missing something vital here.

View 7 Replies View Related

Enter Current Date Into Field Button!!!

Jun 28, 2006

What code would I use and how would I insert it into my Code to make a button on a form fill in the current date into [fieldname]???

I think I already know who is going to respond to this! ;)

Thanks! :D

View 9 Replies View Related

Command Button To Add Current Date Into Text Box

Jul 21, 2006

I need to create a command button that simply, when pressed places todays Date into a text box, the text box should have no value in untill the command button is pressed.

Thanks in advance for any help i receive

Lee Pemberton

View 3 Replies View Related







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