[Solved] I have a report that is based a query formed from 2 tables. The query pulls the address from one table for a client from the other table. This address then appears on the report. There are 6 lines for the address: Company Name, Contact Person, Person's Title, Address 1, Address 2, City/State/Zip. I have this working fine, until an address is shown where there is no Title, or Address 2. Then a blank line is shown. Any suggestions on how I can eliminate the blank line if there is no information to show?
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!
I am trying to generate letters from ACC2003 db into Word2003. Reason for Word is so client can edit letters if required. I have set up a maketable query to generate a temporary table in the Db and the mailmerge master document all formatted and pointed at the temporary table. I still need to get the automation running so that the letters merge and print on a command button. My immediate issue is that the address lines are intentionally spread across 5 fields Addr1, Addr2, Addr3, Town, Postcode. Not all fields in every record are full, and with the query it would be impossible to eliminate fields on the chance they might be empty. As a result my mailmerge has blank address lines which do not look professional. Can anyone point me to how I might solve this given my proposed use - if it was a report then I have a little code module that neatly eliminates blank lines but I cannot see how I could integrate this into the mailmerge process. Thanks for any help or pointers
I'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.
Error message highlights the red line below with the following message:
Runtime error '438' Object doesn't support this property or method
I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.
Code: Private Sub cmdSend_Click() Dim outApp As Object Set outApp = CreateObject("Outlook.application") Set outmail = outApp.CreateItem(olappointmentitem)
I designed and used an Access 2003 for some time but I have recently updated to Access 2013 and want to rejig and improve my database. My query is that I need to print invoices from my database, there is a header section with my details, then a section with the customers name and address at the left hand side, and the invoice details such as invoice number, invoice date, PO number, credit terms, etc. at the right hand side. I would like to remove any blank lines from the customers address but to still have the invoice details at the right formatted and spaced correctly. Is there any way of doing this, the canshrink property will not work due to the invoice details which still need to be printed to the right?
I have a main form where the user chooses a BoxID. There is a subform on this main form that has a relationship between BoxID on the main form and subform. The users are entering data in the subform and when they have entered 10 lines of info I have a message that tells them to start a new box (I could not work out how to do it any other way as I lack VB skills). The user closes the form or clicks add new record to create a new box. If I view the previous record there should only be 10 lines of information in the subform but there are 11 as it takes the blank line as a new record....
I have a main report / subreport relation, just want to have a fixed length of each printout. e.g the total number of lines of the subreport should be 8 lines. However, lots of subreports contain records less than 8. I would like to know how to insert of blank line in the subreport depending on the records with content at each print, in order to make the total number of lines in each subreport is 8 each print!!
I have an access table which lists a customers address, however, if the customer has a temporary address it will also list that on a separate line -
CustID Address Type 1234 5 The Street P 1234 12 The Street T 2345 13 The Road P 3456 12 The Avenue P
Where P = Permanent and T = Temporary
I want to set up a query to show the permanent address where there is only one address, but where the customer has a temporary address also, I want to list the temporary one instead.
I have a form bound to a table which stores contact info for a person. I have two controls that are email_address and website_address. I set them both as hyperlinks. So now when the user clicks on the email_address Outlook opens a new message with an email_address in the To field. (I had to use a function found on this forum to replace the "htttp" with "mailto" on AfterUpdate event in order for this to work properly.) However, now trying to add some other features to my DB I ran into problems listed below. I wonder if I should have rather left that control as Text instead of Hyperlink, and use the DoCmd.SendObject on doubleclick event instead of using the above solution. How people usually store email addresses and enable emailing on click?
The problems I ran into: 1) My main form is in popup mode and therefore right click on the hyperlink does not give an option to edit the hyperlink 2) I copied a function from this forum which gathers a list of emails and sends one message to the emaillist. However, my email list instead of generating as: email1@a.com; email2@b.com it generates as: email1@a.com#mailto:email1@a.com#; email2@b.com#email2@b.com#
I guess I could extract the email using vba (not sure how), but I still don't know how to solve issue number 1. So maybe it is better to siwtch the field to plain text and forget the hyperlinks?
Thanks, Mariusz
PS. What procedure on double click would open a default browser, since I also have to deal with web addresses and thought of turning them into text controls?
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?
I have 2 txtBoxes and a button (txtA, txtB, btn). When opening the form I fill in txtA and txtB. Now when clicking on the button "btn" (OnClick event), the program is supposed to compare the values in the 2 txtboxes. If they are equal (txtA=txtB), then I have to do something which is not relevant to my question. In the code I type:
if (Me.txtA.Value = Me.txtB.Value) then ' DO WHATEVER End if
and it does not even go through the conditional statement if to do whatever I want the program to do inside the if. I would really appreciate anyone's help.
Thank you very much PS: By the way, thank you boblarson and rainman89 for the past question, both solutions worked, I used boblarson's but I have to click on the form when it's opened in order for the changes in the table to take effect.
I've been struggeling with this problem for a couple of days now and finally decided to post here and kindly ask for assistance. I've got a pretty strong OO background, but haven't worked with databases much...
I'm trying to set up a small client database for my company. The client companies have staff members (StaffClients). Client staff can attend Events and work on Projects, which I've managed via junction tables. Our own Staff can also attend Events and work on Projects.
I've uploaded my first draft of tables and relationships here: http://img514.imageshack.us/img514/3937/snapjl9.jpg (sorry for the blurred bits)
Now I think I've done a fairly good job in normalising the tables, but one thing that I keep stumbling across is that I need two almost identical tables: one for our Staff and one for StaffClients.
They are subtly different, e.g. StaffClients have some extra attributes, such as JobTitle, Department and one StaffClient can be the assistant of another StaffClient (also done via a junction table). The other difference is that client staff can have different roles in a project (MainContact or Assistant) compared to our own staff (Manager or TeamMember).
As a result of these differences I have set up separate junction tables for both Staff and StaffClients, but are obviously very similar.
My question is: Is that structure sound? Or can I simplify it somehow before setting up the forms for the data entry?
I have a problem with one of my tables and don't know where to start to fix it. This is where I stand: I have a table with 5 columns, there is no single primary key. I have duplicate entries of 4 of the 5 columns and a count number as the last column. I need to eliminate the duplicates of the combination of the 4 columns but add the count of the duplicates. Let me illustrate for you to understand better:
this is the original table:
col1 col2 col3 col4 col5 A B C D 1 A B C D 5 A B C D 2 W X Y Z 3 W X Y Z 5 W X Y Z 4
I need to obtain this in a new table:
col1 col2 col3 col4 col5 A B C D 8 W X Y Z 12
Oh, and keep in mind that the original table has almost 6 million rows that will probably get reduced to about 500 000 after this process.
Hello All, I am looking for any advice on the following dilemma. I have a table with new monetary transactions that come in every day. I would like to have the transactions(one debit and one credit) that net to zero eliminated from the table automatically. What I was thinking was to have an abs amount column added and run the duplicates query as a delete query based on this column, but that would not only eliminate the amounts that net to zero...but similar debit and credit amounts. Does anyone have any thoughts on the most efficient way to do this? Im sure the answer is staring at me in the face, but I cant get my head around it. Thanks in advance!
Hi i was hoping someone could help me with this. I’ve spent the last hour trying to figure it out. I'll keep trying, and let you know if i have any luck.
I have a calculation box which is based on a sub form,
(Long story short it counts stock items) when there is no stock available there is nothing to count so it returns the #Error message.
My starting expression is simply =[Child96].[Form]![QtyCalc]
I have read in this forum there is a "hasdata" function that can remove the Error#, but it may only apply to reports?
ASP Forums Link
I have tried this in an IIF statement but can't get it to work. So far I’ve been looking at variations of the following
I have made a query from different tables; however, my query is returning about 5 rows for each person because some fields in the query return more than 1 row.
I know there a many posts about eliminating duplicate records because I did search, but I did not see anything that really answers my question.
I have a query in a local Access database that is using a Linked table from an SQL database managed by our corporate IT department. I have read access to only certain tables / fields in the SQL database. My query is based on 3 tables from the database and I have them linked together in my query.
When managers move from one site to another, they are given a different TeamID number. However, until they are replaced, the manager will have more than one TeamID assigned to him in the SQL database. Evidentally, the way our IT has the SQL database structured, when I query open items it duplicates the record for that manager because he has 2 TeamID numbers. So the only field that comes up differnt in the duplicate records is the manager's TeamID number.
I would like for my Select Query to ignore the duplicate record. I know I can use an Append Query to copy the data temporarily to a local table and set the proper fields as Primary Keys to do this. However, it would be nice if I did not have to go to all that trouble.
Is there a way to eliminate duplicate in my Select Query?
This is an intersting question that my friend ask me... hope someone can help to solve it.
In a combo box i use to list out all the company in a table that with the duplicate of the company is allowed. Do someone have a good idea to filter the combo box when detect a duplicate data and just showing one of each company only? or can i create a query with just listing one of each company from the table with all those duplicate company data?
