How can I modify an Access mailing label report to accommodate a sixth line of address content? I used the Label Wizard to create our customer mailing labels (Avery 5160, size 8 Arial font), but the process doesn't seem to allow for a sixth line. First, I tried reducing the font size to 7; no improvement. Then I tried modifying the label report in Design view by copying the =Trim([Address1]) control, creating room for an additional control, pasting the copied control, and editing it to read =Trim([Address2]). The additional line will display in Print Preview and print out on a label sheet, but it forces the text for subsequent rows of labels downward, completely trashing the alignment. Finally, I tried altering the Can Grow properties of the Detail section of the report (both Yes and No) but neither got me the desired result. At size 8 Arial font, it seems there should be sufficient room to print all six lines on the label. Any ideas? Thanks!
I am trying to generate standard Avery 2160 address labels. Fonts are small enough to allow for up to 4 print lines none of which quite touch. Players have entered their own names and addresses via a website form, so I we never quite know what is in the fields. If the player has a foreign address, it will all be entered in the address field, whereas US addresses have 1 or 2 lines in the address field, and city, state and zip in their respective fields. Line 1 is set as name, no shrink or grow. Line 2 is set as address, shrink and grow set to yes, and line 3 (text4) is set for city & state & zip, concatenated and trimmed in the query, shrink and grow no. The detail OnPrint event is the following:
Code: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) Me.Text4.Visible = False If Len(Text4 & vbNullString) > 1 Then Me.Text4.Visible = True End If End Sub
The problem: Any row containing at least one label with 4 print lines pushes the next entire row of three labels down one line, throwing off the spacing of the labels. If I set line 3's can shrink to yes, then the label following the four-line label never has its text4.visible turned to yes, and the other problem (pushing next row down one line) persists. These labels were originally set up via the labels wizard (Access 2010).
How can I keep it from overflowing from one label to push down the next line? This just shouldn't be this hard!
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate() DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code: Dim strLine As String Dim intLineNum As Integer Dim MyDB As DAO.Database Dim rst As DAO.Recordset Open "C:TestTest.txt" For Input As #1 Set MyDB = CurrentDb Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset) CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1 Line 2 is placed in Row 2 Column 1 Line 3 is placed in Row 3 Column 1 . . . Line X is placed in Row X Column 1.
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line with me.graph47 .SeriesCollection(1).border.Color = vbblue ' change the line color .SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3 .SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4 .SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color, .SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
we want to run a ping command via a radio button or command button. We have IP addresses set up for multiple pieces of equipment, each IP address consisting of 4 different text boxes. We want to be able to launch a ping and have the program read the values from each of the 4 text boxes that make up the IP address.
How can I sort string addresses so as to get the correct number order? I know that strings sort on each character, which causes the problem. I have tried some things (with VAL and Len), but was not completely successful. I do not want to enter an address number as 0630, when I want 630.
Why does this not work:IIf(Len(Str(Val([address]))=3),"0" & [address],[address])? Address has 3 and 4 numbers only.
I am currently designing a contacts database for my business and need some advice on how to store addresses.
As I see it, I can either use a single field and carriage returns, or multiple fields which for some records may leave some blank fields.
The data will be used to perform mailmerges and will also be output to a contacts directory and I would be looking to display the address both horizontally and vertically. Is there a simple way of manipulating a multi line field so that it can be displayed on a form across the page on one line in a report?
Hi. I have an Access 2000 database running on Windows 2000 Operating System. The database is on a shared drive on a network with approx 20 concurrent users. It runs a bit sluggishly so I am looking for ways to speed things up. The network guys say that the bit rate is OK so I need to tweak the database. I have run the performance and table analysers and get no suggestions for improvement. However, I have spotted a potential problem with the way addresses are stored.
Currently addresses are stored in the person table along with personal stuff like date of birth, gender, ethnicity etc. the address part has six fields:
Oh by the way - this is for the UK so the address structure has to be a bit flexible to accommodate quite a few variations of how addresses can be written but a reasonable guide is:
Number (or house name) and street name (often a second line for part one of address) Village / urban area Town County Postcode
Part one of my question is - is this the best way to store this information or should I just gather it like this on a form and then store it as concatenated text in a single field. I have tried this on a small test database and it works OK and prints address labels etc. - - But is it better or not?
Part two is - should the addresses be stored in the person table at all or should it be in a separate table. This is the bit I cannot get my head round. If it is in a separate table then each record in the table needs to be unique but because of the nature of the client base (i.e. students) many students share accommodation - OK a 1 to many relationship - but what about students in halls of residence they will have an individual room but the remainder of the address is the same. So for say 1000 students in the same hall 95% of the address is repeated although each address is unique overall. And with 4 halls there are 4000 entries that could potentially be reduced to 4 plus a room number and hall name, but I am not at all sure how to achieve this. And then what about addresses for students not in halls but shared houses? I cannot get my head round this. I can see why it was set up the way it was but there must be a better way.
Do I need one table, two tables or six tables (one for each field)?
And how do I then enter data? Do I have to have combo boxes on each line to see if that value has been entered before?
The data gathered would always be entered via forms so I can get the fields together but I don't know how many tables I need nor how best to relate them.
Can anyone suggest how I might get round this problem?
I have a field in one of my tables that contains IP Addresses. These addresses can look something like 142.67.214.73/24 or 192.168.196.0. I would like create a calculated field so that ALL IP addresses just show the first 3 octet's. So the first one would just show 142.67.214 and the second one just 192.168.196
Simple query from an amateur. I have a list of people in a table, each with thier email address. How can I convert this into a list of addresses to paste into a group email? (Ideally with ";" as a separator between addresses)
Hello all I am using MS access for a little personal project and I have house number and streets along with a day they are serviced. example:
unit road type day 1 main st friday 2 main st friday 3 main st friday 4 main st friday 5 main st friday 6 main st friday 7 main st friday 13 turtle st monday 15 turtle st monday 16 turtle st monday
I want to make a report of this data to look like this
unit road type day
1-7 main st friday 13-16 tutle st monday
I figure I have to do some sort of query before I can make the report im jsut not sure how to seperate it or what symbols to use. I know this is faily simple I just can not get the computer to give me what I want....
ok I think i figured it out what I did was take unit and I put it in 2 times one fo min and one for max and then I grouped the road by ascending and it worked out
Hello everyone. I hope you are well. I am new to this forum and need some help with access.
I have a database which records an organisations addresses. There is the main address, where the activity takes place, and then there is the postal address, which is often different. What I want to be able to do is to select all the organisations in one town (e.g. Town1), but send information in the post to their postal address (Town2).
Is this something that can easily be done? I hope somebody can help me (especially with some user friendly terms).
Is it possible to send e-mails to all of your e-mail addresses in one single e-mail?
Right now I am able to open up an individual e-mails for each of my stored e-mailo adresses but it would be so much better (and more user friendly) if I could just have one e-mail with all addresses in the To: field.
I need some input on what to use for a field that contains a range of IP addresses, and have it sort numerically by octet. I currently have that field assigned as text, but were not happy with the sort order. I've played a little with the number format type, but still did not get the correct sort order. Any suggestions would be appreciated. Thanks
My database is a maintenance record of our building complex. The unit/building number is the key field. The problem I am having is having access recognizing the unit numbers and building numbers, not as whole numbers. Example: I-380, 10-380, 2-390, 13-370, and so on. When sorted, access treats them as whole numbers. I have tried two separate fields but it would be nice to have only one field and be sorted by building numbers. I am not a coder so be gentle. Can you help?
Hi I have a large file where I am trying to clean up the data. One of the fields is the email address. I want to check if the email address has something followed by an @ symbol followed by something else. If it does not, then I want to replace it with a null value. Can you help? I thought that I could use the wildcard features such as <>*@* but this does not appear to be working. Noel
This might be a basic Access application, but I'm having trouble. I want to create a name and address table that will include all patients in my clinic, identified by unique patient ID numbers. I then want to create 3 other tables for the information collected at the 3 appointments I will have with each of these patients. I've created 4 forms, 1 for each table; each a control for patient ID, name and address. How do I create a link so that I can enter the patient ID number in the appointment form and the name and address will appear automatically in my form? Is this possible? Thanks for your help on this. -Lorna
I am a relative newcomer to Access, and am trying to work out if there is a way to isolate email addresses in a line of text, and delete all the text around them.
I have basically got a column which contains blocks of text and I want to extract the email addresses that are embedded within them. Does anyone know if this is possible by running queries?
Hello I have a political database with a separate field for street number, street name and apartment number. I've been asked to prepare a query that will only list the first member of a household. Example, the query would only show one member from a two person household if they both have the same address. I figured that I would somehow prepare a query that combined these 3 fields in a expression and then display the total row and choose FIRST. That didn't work, perhaps my syntax was off. Here's what I tried:
I have a database set with user level security (via the wizard) and I want to give "Full Data Users" the ability to enter hyperlink addresses. Currently only I can do this as I am set to "Full Permissions / Admin"
The associated table is set to accept hyperlinks, and the relevant text boxes are in place and unlocked on the forms, however when logged in as a Full Data User, you currently can not set or add a hyperlink address, only follow the hyperlink path's that i've already put in.
I have a database in which I want to automatically update all the addresses which have United States Zip Codes to contain the 4-digit zip which can be located at www.usps.com.
I am a novice at setting up dbases, but seem to be able to get around when trying to figure something out. Yet this one has stumped me. Could anyone help me update my data automatically without having to do the tedious copy and paste of each record into the usps.com website to get the correct address information.
Im using the code below to send information by email. BUT:
I want to send the same information to more than one recipient. can i write more than one address in the SendObject?
What can i do if i do not know prior to sending how many addresses i want to include. (this means that once i might have tosend it to 1, then later to 3, etc...)?
The code is:
Private Sub cmdMailTicket_Click() On Error GoTo Err_cmdMailTicket_Click
Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text Dim RecDate As Variant '-- Rec date for e-mail text Dim stSubject As String '-- Subject line of e-mail Dim stTicketID As String '-- The ticket ID from form Dim stWho As String '-- Reference to tblUsers Dim stHelpDesk As String '-- Person who assigned ticket Dim strSQL As String '-- Create SQL update statement Dim errLoop As Error
'-- Combo of names to assign ticket to stWho = Me.cboAssignee stWhere = "tblUsers.strUserID = " & "'" & stWho & "'" '-- Looks up email address from TblUsers varTo = DLookup("[strEMail]", "tblUsers", stWhere)
stText = "You have been assigned a new ticket." & Chr$(13) & Chr$(13) & _ "Ticket number: " & stTicketID & Chr$(13) & _ "This ticket has been assigned to you by: " & strHelpDesk & Chr$(13) & _ "Received Date: " & RecDate & Chr$(13) & Chr$(13) & _ "This is an automated message. Please do not respond to this e-mail."
'Write the e-mail content for sending to assignee DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
'Set the update statement to disable command button 'once e-mail is sent strSQL = "UPDATE tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _ "Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"
On Error GoTo Err_Execute CurrentDb.Execute strSQL, dbFailOnError On Error GoTo 0
'Requery checkbox to show checked 'after update statement has ran 'and disable send mail command button Me.chkTicketAssigned.Requery Me.chkTicketAssigned.SetFocus Me.cmdMailTicket.Enabled = False
Exit Sub
Err_Execute:
' Notify user of any errors that result from ' executing the query. If DBEngine.Errors.Count > 0 Then For Each errLoop In DBEngine.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If
