I have attached a DB that I am trying to create for my small business. Every month we have renewals of the client’s yearly contracts. I have created a form called frmRenewells, on this form you need to select a month and a year (please choose October 2006) this then runs query qryRenewell. The query then opens form frmRenewellSheet with the results. This works fine, but I really need the following to happen:
Open form frmRenewells and enter month & year, on clicking the enter button it launches a word template I have created and automatically pulls all the fields required into the word document. I can then press print and it will print a document for each client in the query.
I am unable to go into word and just open the query as it has * or wildcards in it. Therefore word will not allow me to select the query.
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
I have a very basic DB of customers names and addresses. What I want to do is find all the enquiries within the months of August, September and October, send all these enquiries to a new table so that I can use this new table for a mailshot. The date of the incoming enquiry is in a field on it own and written as dd/mm/2006. If there is an easy way to create a mailing list from the original table please let me know. Best Regards Keith:o
I have a query in Access and a word document that opens up the query. Currently the query contains every record in the database. If I alter the query in Access to select a certain set of records (based on dates) when Word opens the recipient list is empty. Checking for errors it says there were no records or no data records matched query options.
How can I get Word to open and use the modified query?
I am preparing mailing label for a political campaign. On my list of voters, many times two or more people are listed at a single address. By consolidating the labels (and postage) I am able to save a lot of money.
I have written a query to group these names into those with 1,2, or 3 or more/address. For labels I am able to print 2 names/label by using the FirstOfFirstname, FirstOfLastname, LastOfFirstName, LastOfLastName generated in my query. This works fine for 2 names per label.
My question concerns 3 names per label. Does anyone know how to include all 3 names? Suggestions have included using a label with "The {LastName} Family" etc. but many times there is more than one last name per residence. Other suggestions are to print individual labels for each individual and overlap the labels to show all the names but just one address. These does save postage of $.42/mailing, but seems wasteful of labels and looks a little crude besides.
What I think I need is a clever query or queries that will be the data source for the labels. I have room for up to 3 names/label. I have been thinking about printing 2 labels -- one with one name and address and another with the rest of the names but this brings up problems of getting everything on the right envelope.
I am trying to construct a query to extract mailing addresses from a table. I have individuals entered into a table (a separate record for each person) but if they are married I want an address such as Mr and Mrs J. Doe so that only one address label is printed off so that only one letter is sent out. If one of them dies then the address should only go to the surviving party eg Mrs J.
Ok, I've run into two serious problems in testing, and another question that I'm hoping you guys can help me solve. Below my questions is the code used for making the search query/email program.
Problem #1: In testing, this search program only is working for me for new data. Any data that I had in the database prior to implimenting the program will not move past the search stage. In other words, I will do a search, it will find the emails and bring them up in a box, I hit "OK" and it gets hung up and won't move on to opening up a new Email with the addresses implemented. However if I enter new data in the database, say with a weird name so its only bringing up the new entry and no old entry, it works fine and opens up the new email.
Problem #2: Items I would like to search like check boxes. Say I have a check box "Donor", this is listed in the database as a 1 or 0. I can't have my boss searching 1 or 0. Is there a way to change this to, say, being recorded in the DB as a Yes or No. That way he would check to search "Donor" and type Yes in the search box and this would search the DB "Donor" column for "Yes" and bring up the results.
New Question: My boss and I reviewed the form as it is so far yesterday. He was asking if I could add a Print Labels option in there. I'm wondering instead of adding another seperate search box and all that mess for a labels search print, can I rather add two check boxes, one labeled "Print Labels" and the other labeled "Email". What this would do is depending on the check box you selected it would either run the search and email like we have it, or clicking the other check box would run the search and print labels.
Quote: 'Author: Michael Walts, but use it as you like'Important information! this code requires a reference to the Microsoft DAO object library Option Compare Database Option Explicit
Private Sub cmdEmail_Click()
'will hold the dynamic SQL query Dim strSQL As String
'will hold the WHERE clause portion of our SQL query Dim strWHERE As String
'will hold all the recipients of this message Dim strRecipients As String
'the recordset we will use to get the emails of the records that match our criteria Dim rst As DAO.Recordset
'if there is input in the search criteria, then we will run the query and send the e-mail If txtSearch <> "" Then
'if you have more buttons, just add mosr cases (the value of the radio button '= the Case number, so Value of the State radio button is 1, etc.) Select Case opgSearch.Value
Case 1 strWHERE = "WHERE State = '" & txtSearch & "'"
Case 2 strWHERE = "WHERE City = '" & txtSearch & "'" End Select
strSQL = "SELECT EMail FROM tblUser " & strWHERE
'run the query and get the results into the recordset Set rst = CurrentDb.OpenRecordset(strSQL)
'Loop through the recordset and add all the EMails Do While Not rst.EOF strRecipients = strRecipients & ";" & rst!EMail rst.MoveNext Loop
'remove the first ; from the strRecipients strRecipients = Right(strRecipients, Len(strRecipients) - 1)
'stops a ' entered in the field from breaking the query Private Function SQLSafe(safeMe As String) As String SQLSafe = Replace(safeMe, "'", "''") End Function
I've almost completed the DB I'm working on, but still have a few loose ends to tie up that I can't seem to figure out. I've spent many hours already on this forum searching for solutions, and have tried a few different things, but I still can't work out these bugs.
First, I need my form to open with the latest records filtered first, so that the most recently added records will be easiest to find and edit. I'm not sure how exactly to do this. I've tried a run query when the form is opened, but I still need all the records displayed or accessible. The form is called frmDenial, and I would like it to be looking in the DateLogged field of the form. Any suggestions?
Second, I have command buttons that will export data in the fields to MSWord Templates via bookmarks. The button runs well by opening the document, inserting the data, and printing. I have also tried several different code syntax to close word once it is done, but it is still staying open after printing. Here is a sample of the code: ________________________________________ Private Sub Print_Letter_Click() Dim objWord As Word.Application 'Start Microsoft Word 2000. Set objWord = CreateObject("Word.Application") With objWord 'Make the application visible. .Visible = False 'Open the document. .Documents.Open ("G:PharmacyPrior Auth Docs and DataRevised Pharmacy Denial ProcessesKAN Not Nec or Benefit2.dot") 'Move to each bookmark and insert text from the form. .ActiveDocument.Bookmarks("bmkFirstName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRFirst)) .ActiveDocument.Bookmarks("bmkLastName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRLast)) .ActiveDocument.Bookmarks("bmkHRN").Select .Selection.Text = (CStr(Forms!frmDenial!MemberNumber)) .ActiveDocument.Bookmarks("bmkAddress1").Select .Selection.Text = (CStr(Forms!frmDenial!MBRAddress1)) End With Print_Letter_Click_Err: 'If a field on the form is empty, remove the bookmark text, and 'continue. If Err.Number = 94 Then objWord.Selection.Text = "" Resume Next End If objWord.Application.Options.PrintBackground = False objWord.Application.ActiveDocument.PrintOut objWord.Application.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Quit Microsoft Word and release the object variable. objWord.Quit Set objWord = Nothing Exit Sub End Sub _______________________________________
Finally, I have two cascading combo boxes set up that will auto pop related fields based on the selection made. But, if an item is not in the list, I would like the user to add it to the linked table to appear in the list. I also have this working well, with a pop up asking the user if they want to make the addition, type in the new item, and add to the list without requiring the user to refresh or exit then re-enter the form. But it will only add the item name, and not the item description (another field in the form and another column in the table). How can I modify the code to prompt the user to enter these other details? I can link it to a pop up sub form to enter the data, but if possible, would rather the boxes pop up to have the user type in the data. Here is the code I have so far in the NotInList Event... __________________________________________ Private Sub DrugName_NotInList(NewData As String, Response As Integer) Dim DB As DAO.Database Dim rs As DAO.Recordset Dim strMsg As String strMsg = "'" & NewData & "' is not an available Drug" & vbCrLf & vbCrLf strMsg = strMsg & "Do you want to add the new Drug to the current Database?" strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it." If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then Response = acDataErrContinue Else Set DB = CurrentDb Set rs = DB.OpenRecordset("tblDrug", dbOpenDynaset) On Error Resume Next rs.AddNew rs!Drug = NewData rs.Update If Err Then MsgBox "An error occurred. Please try again." Response = acDataErrContinue Else Response = acDataErrAdded End If rs.Close Set rs = Nothing Set DB = Nothing End If End Sub ______________________________________________ The two other colums in the tblDrug that I need the user to be prompted to fill are Denial Reason (column 3) and Alternative (column 4). What would be the best way to accomplish this?
I would really appreciate any help or suggestions with any of these problems. Thank you so much! :o
Thought I posted this once before, now can't find it -- so I apologize if it turns out to be a duplicate posting.
Anyway...Figured out how to design a table then extract the info I wanted via a query. Last thing I simply cannot figure out is how to get the query exported onto mailing labels.
Thank you in advance to anyone willing to share their time and expertise to assist me in this last step.
Is it possible to change the format of the mailing labels once the report has been created? I have one that has 24 labels on it, but I need for it to have 30. Also I can't remember how I created it in the first place. What steps do I need to go through to make a new report?
I have created a report for labels using the Label Wizard, and found the code on the MS KB about skipping the labels that have already been used, and printing on the next one along.
Page on MS KB (http://support.microsoft.com/?kbid=299024)
When I try using this code in Access 2003 however, it seems to go into some sort of loop, and produces 100+ pages for the report when I try and skip 1 label for example. Can anyone help me get this working for 2003?
Here's my module code, same as on the site above:
'************************************************* ******** 'Declarations section of the module. '************************************************* ******** Option Compare Database Option Explicit Dim LabelBlanks& Dim LabelCopies& Dim BlankCount& DimCopyCount& '================================================= ======== ' The following function will cause an input box to ' display when the report is run that prompts the user ' for the number of used labels to skip and how many ' copies of each label should be printed. '================================================= ======== Function LabelSetup() LabelBlanks& = Val(InputBox$("Enter number of used labels to skip")) LabelCopies& = Val(InputBox$("Enter number of copies to print")) If LabelBlanks& < 0 Then LabelBlanks& = 0 If LabelCopies& < 1 Then LabelCopies& = 1 End Function '================================================= ======== ' The following function sets the variables to a zero '================================================= ======== Function LabelInitialize() BlankCount& = 0 CopyCount& = 0 End Function '================================================= ======== ' The following function is the main part of this code ' that allows the labels to print as the user desires. '================================================= ======== Function LabelLayout(R As Report) If BlankCount& < LabelBlanks& Then R.NextRecord = False R.PrintSection = False BlankCount& = BlankCount& + 1 Else If CopyCount& < (LabelCopies& - 1) Then R.NextRecord = False CopyCount& = CopyCount& + 1 Else CopyCount& = 0 End If End If End Function
I need to generate 1 email with 3 attachments from an Access Db. These attachments are canned reports that are generated each week with fresh data.
I've done several searches and found a lot of good information here. Based on what I've read, I decided to output the 3 reports to a folder in My Documents and then automate Outlook to send the message.
I've used the output function to create the 3 files. No Problem, works well.
Then I found this code for automating Outlook. (Pasted below) I can get it to work (following either step 7 or step 8 below) but only if I include the attachment path in the SendMessage command.
Assuming the full paths are: C:My DocumentsReport1.snp C:My DocumentsReport2.snp C:My DocumentsReport3.snp
how do I modify the code to automatically attach all 3 files?
Any ideas? As always, thanks for taking the time to help, BeckieO
Sub SendMessage(Optional AttachmentPath) Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment
' Create the Outlook session. Set objOutlook = CreateObject("Outlook.Application")
' Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg ' Add the To recipient(s) to the message. Set objOutlookRecip = .Recipients.Add("Henny Penny") objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message. Set objOutlookRecip = .Recipients.Add("Lucky Ducky") objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message. .Subject = "This is an Automation test with Microsoft Outlook" .Body = "Last test - I promise." & vbCrLf & vbCrLf .Importance = olImportanceHigh 'High importance
' Add attachments to the message. If Not IsMissing(AttachmentPath) Then Set objOutlookAttach = .Attachments.Add(AttachmentPath) End If
' Resolve each Recipient's name. For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve If Not objOutlookRecip.Resolve Then objOutlookMsg.Display End If Next .Send
End With Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub
7. To test this procedure, type the following line in the Immediate window, and then press ENTER: SendMessage "C:My DocumentsCustomers.txt"
8. To send the message without specifying an attachment, omit the argument when calling the procedure, as follows:SendMessage
I have a database of customers for which I want to print address labels depending on what group I have entered them in. There are about 30 different groups that they could be a member of. I have entered them in to groups by using "yes/no" fields on the customer table to indicate who is a member of which set. My problem is that I know how to indentify which group is required using sql
eg select surname,address from table where GP
where GP is one of the possible groups. I cannot however see a way of selecting the group variable from a form and entering into a query, so that I can print the required label set.
Can anyone please advise me on this or point point me in the direction of an example. I tried using a combo box on form but could not get it to pass the parameter correctly. Many thanks in advance.
I have a database for clients and have set up a form and code to run a query for different types of clients and to send emails to the group.
Everything works fine until I try to send and then I get a Microsoft Outlook pop-up which states:
"A program is trying to automatically send e-mail on your behalf. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No."
This message stays for 5 seconds and then I can click on "YES" and it will send the email, and start all over for the next recipient.
I am assuming this is an Outlook Spam Blocker, but is there any way to stop it?
Hi--I have a contact database, and would like to be able to have a command button on the form for each contact to print one individual mailing label for that particular contact, preferably in a user-specified row/column on a page of Avery 6245 labels. Am realizing that this is a major hassle in Access but would like to do it within Access (why is it so easy to print huge lists of labels but so hard to print just one??). Any help would be appreciated. I'm pretty good at writing queries and reports in Access but a rank newbie at the underlying SQL and VBA so need major handholding if that's involved (and I know it is ). Thanks.
I need to e-mail weekly reports to several parties, but both Word and Excel are not working for me because I am losing the original formatting. For the sake of consistency and readability I need to preserve the original report format, but I am totally stumped as to how to do this. Please, someone, help!
How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.
I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below
Table A
Table B
Tag No
Tag No
2009
ZZZ-2030-DC
2010
ZZZ-2010-M9P
[code]....
They're both in MS Access.I am trying to match 2 tables - columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.What query is suitable to compare two tables.
Hi all,I've been struggling with this for a few hours... I sure hope that the solution to my problem is complicated so I don't feel really dumb!I'm trying to design a database to eliminate the HOURS that my mom spends trying to format mailing labels in Word. She deals with about 50 clients at a time, and sometimes needs to print labels for just a selection of them, but not all. I'd like to design a form where she can select (using an option button or similar) the clients for whom she wants to print a label, and print them all at the same time (rather than printing one at a time, which I can handle on my own). Creating mailing labels will be the only function of the db so the info stored in it will be relatively simple.I have the Northwinds db installed, and I've found the sample Macro controlling the Where condition for the labels report. I understand how to make it work for a single selection from a combo box (ie: only print labels for customers from a specific country)I have my report set up so it shows a label for each client, pulling data directly from the main table. No troubles with formatting. **knocks on wood**My problems:1. I searched this site and found a link to the MS KB file that outlines how to print multiples of one label or skip used labels before starting to print (Q95806 - "How to Skip Used Mailing Labels and Print Duplicates"). I'd like to use this, but can't make it work.2. I can't figure out how to "link" the option button to the client name as it's listed in the form, and then tell the report to only print labels for the selected clients.I have very little coding experience but I can make some simple code work and am OK at customizing pre-written code to my own needs if it's well commented, so VB isn't entirely out as an option.I have a possible solution in mind that involves creating a query that makes a new table with only the selected clients, which is then used to make the labels report. I think I could make that work if I could just figure out how to select the clients in the first place.I apologize in advance if this has been taken care of elsewhere. I tried to find it, but had trouble coming up with the right search string!Thanks in advance,~ Mel ~p.s. I'm using Access 2000.
I have to prepare invoice statements to send to my clients. I have managed to generate the invoices as a report.
However, I want to add two types of mailing addresses to this invoice report: "From" mailing address (my company's mailing address) and a "To" mailing address (the recipient's mailing address).
I have created a Clients table which contains the company's mailing address details and a separate ClientContacts table which contains the primary contact name and the secondary contact name (along with their email addresses) for the respective company.
My problem is how to incorporate this information on the invoice report. What I have done is to create an unbounded text box on the invoice report and manually type in this information. Is there a way to add my company mailing address as a "From" mailing address and the recipient's mailing address as a "To" mailing address on the report automatically?
I have a table that contains a lot of different information regarding to mailing.
There are columns for Name, Address, Address2, Address 3, City, State, Zip, Country, Base Name, etc.
Some units will only required parts of the above information. I'm using the Name as an identifier in the DB only and Address is the beginning of content that I want to use.
My issue stems out of the fact that the City, State, and Country are not always required but the Base Name, may be used in lieu of the City.
I was looking at making checkboxes next to each of the columns on the form on the field and then using those checkboxes somehow to create a report for Mailing Labels.
Hi there, I have created a form containing contact details for a 100 or so different people/companies. I want to have a button that will allow me to print an address label for the current displayed record (e.g. when I find the company I wish to contact in my form I wish to click [Print Label] and then get this company's address printed on my label). Have some programming experience but not sure how to go about this in access?
Also as an aside question, is it possible for me to display a list in my form instead of individual records. What I mean is that I would have Name, Address, etc as titles accross the top and then would have a list of all records below these. Then I could click on a record to get more detail and also click on one of the titles to re-order the data by this field?......I know these are pretty big questions but a pointer to somewhere with this info or a sample app doing something similiar would be great. :)
I'm a very new user of Access. I am creating a contacts database (3000+ contacts), with up to 6 addresses per contact, though most will only have around three. I'd like to find the best way to select the preferred mailing address (using a combo box??) in order to be able to regularly creating mailing labels (and letter mail merges).
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform 'Application.TempVars.Add "varcountryselect", "*" SELECT in the listbox "lstlocationsperproject" SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0) Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
My end goal is to populate a pre-existing table in an MS Word document with records from a query. The easiest way I've found (through scouring the internet) is to start with the code below (ran during OnClick() even in Access) to get the table the same size as the recordset:
Code: Dim wDoc As Word.Document Dim wTable As Word.Table Dim wCell As Word.Cell Set wDoc = appWord.Documents.Add(strDocLoc) wDoc.Visible = True
[Code] ....
The code will shrink the table down just fine if the table has more rows than the recordset +1 (for header column). My hangup with this is the last line ("Selection.InsertRowsBelow 5") isn't executing; rows are not being added to the table. I get no errors -- it just does nothing. I set it as "Selection.InsertRowsBelow 5" arbitrarily just to see if it would even add rows, and sure enough it's not.
Hi, i am designing a database but i need to do a query which will search for a specific word and bring up all the details regarding this this. An example is i need to search for the drug panacur to see which animal use this treatment, i then want it to bring the name and address oof the client as well as any other details. I just do not know how to search for a specific word. please help!! Thanks
