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
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.
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"..
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.
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
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?
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"
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.
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.
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.
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
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
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.
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.
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.
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 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
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?
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
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.
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.
