Merge Current Record
Mar 2, 2006
Using the following code to merge current record from an Access 97 form to a Word doc. It works fine except where there is no data in some of the address_line fields when it leaves the postcode orphaned by as many as three lines. Any ideas how to ignore empty fields and move the postcode to the line after the last populated adress_line?
Public Sub C12DocMerge(lngAppID As Long)
Dim dbs As Database
Dim strSQL As String
Dim rstMerge As Recordset
Dim AppWord As Word.Application
strSQL = vbNullString
' Return reference to current database.
Set dbs = CurrentDb
'Populate strSQL with details from current record
strSQL = "SELECT tblApplicant.ApplicantId, tblApplicant.App_Forename, "
strSQL = strSQL & "tblApplicant.App_Surname, tblApplicant.AccountNo, "
strSQL = strSQL & "tblAddress.Address_Line_1, tblAddress.Address_Line_2, "
strSQL = strSQL & "tblAddress.Address_Line_3, tblAddress.Address_Line_4, "
strSQL = strSQL & "tblAddress.Address_Line_5, tblAddress.Post_Code "
strSQL = strSQL & "FROM tblApplicant INNER JOIN "
strSQL = strSQL & "(tblAddress INNER JOIN tblAddressLink "
strSQL = strSQL & "ON tblAddress.AddressId = tblAddressLink.AddressId) "
strSQL = strSQL & "ON tblApplicant.ApplicantId = tblAddressLink.ApplicantId "
strSQL = strSQL & "WHERE (tblApplicant.ApplicantID =" & lngAppID & ")"
' Create Recordset object.
Set rstMerge = dbs.OpenRecordset(strSQL)
'Get an Application object to open Word.
Set AppWord = CreateObject("Word.Application")
With AppWord
.Visible = True
.WindowState = wdWindowStateMaximize
.Documents.Add "\CC12DearSomebody.dot", False
.ActiveDocument.ShowSpellingErrors = False
.Selection.Goto wdGoToBookmark, Name:="Forename"
.Selection.TypeText rstMerge![App_Forename]
.Selection.Goto wdGoToBookmark, Name:="Surname"
.Selection.TypeText rstMerge![App_Surname]
.Selection.Goto wdGoToBookmark, Name:="Add1"
.Selection.TypeText rstMerge![Address_Line_1]
.Selection.Goto wdGoToBookmark, Name:="Add2"
.Selection.TypeText rstMerge![Address_Line_2]
.Selection.Goto wdGoToBookmark, Name:="Add3"
.Selection.TypeText rstMerge![Address_Line_3]
.Selection.Goto wdGoToBookmark, Name:="Add4"
.Selection.TypeText rstMerge![Address_Line_4]
.Selection.Goto wdGoToBookmark, Name:="Add5"
.Selection.TypeText rstMerge![Address_Line_5]
.Selection.Goto wdGoToBookmark, Name:="PostCode"
.Selection.TypeText rstMerge![Post_Code]
End With
Thanks in anticipation
View Replies
ADVERTISEMENT
Aug 12, 2005
Hi,
Is there a way to merge only the current record to Word? I was going
to have access create a temp table to merge, but then realised that
would stuff up my autoref numbers (which I need).
I will only be merging one record at a time, and would love to hear any
advice.
I don't know VB so other instructions (or VB for dummy instructions)
would be most useful.
thanks!
View 14 Replies
View Related
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 Replies
View Related
Aug 19, 2013
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
View 5 Replies
View Related
Apr 9, 2007
Hi there..
I am having issues with MS Word mail merge. Issue is i am selecting the recepient list from access database. then i filter the list as per my need and send emails. But filter is not working properly.It is sending email to all the of records not to only selected ones.
Can it be any issue with MS Access?
Thanks
Danny
View 2 Replies
View Related
May 6, 2015
I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.
This is so the user knows that what they are entering is roughly in line with what has come before.
So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.
Records:
Date | Temp
2014 | 20.5
2013 | 18.5
2012 | 19.0
2011 | 22.7
2010 | 15.2
So when I enter the record for 2015 I have a box that says: Temp and next to that box is "Last year was 20.5" or something like that.
View 2 Replies
View Related
Aug 8, 2014
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
My attempted query: DLookUp("[Team]","[Team_qry]","[Team_qry]![Date]=#" & CStr(DMax("[Date]","[Team_qry]","[Team_qry]![Date] <#" & CStr([Date]) & "#")) & "#")
View 3 Replies
View Related
Feb 18, 2015
I have an access .adp project which is linked to a sql server tables (2008 r2), I have a button on an access form which I click and successfully gets customer details based on a query. What I wanted to know is when I am viewing a specific record i.e. details for Patient Number X, when I click on a mail merge button from their record form can the mail merge document only open with their details not all customers?
I have tried to delve into vba and modules to acheive this but I am a newbie.
View 4 Replies
View Related
Sep 15, 2005
In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e.
Name Value Previous
Record 1 1
Record 2 2 1
Record 3 7 2
Record 4 1 7
Is it possible to create some kind of simple expression to refer to data in another record?
Cheers,
Ben
View 6 Replies
View Related
Apr 14, 2007
I need to write a query which populates an empty field in the current record with information from a specific field in the next sequential record. Any ideas?:confused:
View 3 Replies
View Related
May 1, 2013
I have a query to bring in values, I need to select 2 rows of data but the criteria is as such:
x= starting value on form
now the row of data must match the following criteria previous row to current row(ref temp)<=x And Current row(ref temp)>xnext row to current row(ref temp)>=x And Current row(ref temp)<x
I have dealt with SQL before but how to do the above.The isolated 2 rows of data will then go into unbound boxes on a form from which I will do intercept and gradient calculations.
View 5 Replies
View Related
Jun 14, 2013
As mentioned before on a different topic I am building a database to sort through publication authors. I currently have the table sorted in a way that only the main author is listed for the 3000 records. Now I need a way to remove duplicate entries on a new table. Each entry is a different publication. Each record has a DOI code that is unique and an author which might be a duplicate. I want a new table listed by author once and a new field that list all of the DOI numbers for that author.
I know how to do a query to show only the unique names, but how to I also get the database to combine the records that share the same author.?
View 7 Replies
View Related
Sep 5, 2006
Hi all,
Another problem bothering me. Wish someone can help me to solve it. I am doing the 'Edit' Option right now ... As a user friendly propuse, I had try to use a combo box to let user to choose my employee record with all the detail on but the problem is the program 'JUST' list the record they can not go to the current record!! When i try to edit my record, the program is not allow (for sure because they can't have a duplicate record on it). The reason is because the record just stay in the FIRST record but can not go to the current record. I do believe is the problem of i using the way of listing the record as below:
Me.EmployeeName = Me.EmployeeName.Column(0)
Me.IC = Me.EmployeeName.Column(1)
Me.Nationality = Me.EmployeeName.Column(2)
Me.Race = Me.EmployeeName.Column(3)
Me.Sex = Me.EmployeeName.Column(4)
Me.FMU = Me.EmployeeName.Column(5)
Me.Position = Me.EmployeeName.Column(6)
Me.SectionField = Me.EmployeeName.Column(7)
Me.DOEmploment = Me.EmployeeName.Column(8)
Me.DOResign = Me.EmployeeName.Column(9)
Me.Remarks = Me.EmployeeName.Column(10)
So, Could someone help me to solve it like when i can use the combo box to select the Employee record with the details are on as the same time can go to current record... Thanks alot!!!
i do have the confident if i can go to the current record then my edit option can be working properly.
P/S: I had attach the picture... showing my program problem (with the red circle)
View 8 Replies
View Related
May 12, 2005
I have a table called tblFinishedGoods. There are 3 fields in the table:Serial Number, Model Number and Location.
I would like to have a form that will take input from my barcode reader and input the data for the 3 fields, if the serial number is not already in the table.(I have this already working).
But now I would like the same form to also check the table for any serial number that is scanned in...and if it is in the table already, have it bring up the model number and location for that record.
I'm thinking maybe there is something I can do with Serial Number afterupdate. Basically I'm looking for a way to not require the user to input anything via a mouse or keyboard.
If the 1 form could accept the 3 scans:Serial Number,Model Number and Location, and either 1)enter a new record if that serial number isnt in the table or 2)find that the serial number already exists and overwrite the model number and location with whatever the next 2 scans are.
Thanks
View 2 Replies
View Related
Jun 9, 2014
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com
Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
View 2 Replies
View Related
Feb 23, 2006
hello,
I have faxing setup through the XP wizard, I setup my report in Access to use the Fax as specific printer. So When I select a record, it generates the Fax wizard which then prompts for the senders name Fax # etc. and away it goes.
that is ok but It would be nice if I could use the Fax number from the record I am sending, either current record or all. I guess the main trick is how would I pass the fax number to the Fax Printer interface?
Is it possible? I've read a few articles on Microsoft Fax for workgroups to work with Outlook, Outlook help says to go to Office update and download it but I can't find it there? Also do the newer versions of Outlook/Access have a better means of doing this? I am running office 2000, but I can get 2002 installed if that is the case.
thanks
Ziggy
View 1 Replies
View Related
Dec 12, 2005
I have a query that pulls all orders out of 3 tables that fit specific criteria.
tblLotInfo.WOSD = Between ([tblLotInfo].[WOSD])<=Date() And [Forms]![frmRaisedPanelLots].[EndingDate]
tblDelivery.Status = <>"On Floor" And <>"Floor Hold" And <>"On Floor LV Losee" And <>"On Floor LV N. 5th" And <>"On Floor ONT Cucamonga" And <>"On Floor ONT Locust" And <>"Shipped" And <>"Installed" And <>"Invoiced" And <>"Completed" And <>"Cancelled" And <>"Returned to Floor LV Losee" And <>"RETURNED TO FLOOR N. 5th" And <>"Returned to Floor ONT Cucamonga" And <>"Returned to Floor ONT Locust"
tblLotInfo.DoorStyle = Like "*eagle*" Or Like "*H/E*" Or Like "*RP*" Or Like "*F/E*" Or Like "*CC-23R*" Or Like "*AR-756*" Or Like "*Deco*"
This query works fine through 12/29/05. Anything date entered after that gives me "NO CURRENT RECORD". I don't know why. We have orders through Feb of next year scheduled. Any help would be greatly appreciated!!
View 2 Replies
View Related
Jun 26, 2006
I need help to get current record with different ids:
Dim strDocName As String
Dim strWhere As String
strDocName = "16kanaler"
strWhere = "[skjema_16_gr1_ID]=" & Me!skjema_16_gr1_ID
DoCmd.OpenReport strDocName, acViewPreview, , strWhere
How can I use more Ids in the wherecondition? I want to get the current record for skjema_16_gr1_ID and skjema_16_gr2_ID, not only the first.
Please help me...
Håvar
View 2 Replies
View Related
Oct 27, 2005
Hi, I am a complete newbie to Access. I want to be able to double click on the field in the table, form or a query and have, as a result, another form pop-up with all the data from that record displayed. I have tried macros, but the doubleclick property only has things like goto first, last, etc. records. and always brings the first record in the table. Any hep in the right direction is greatly appreciated. Thanks.
View 2 Replies
View Related
Jan 5, 2006
I have got a tricky problem, for which I can find no answer - but then I am a bit of a novice here.
I have created a (probably too-) complicated relational database but what I really need is to be able to make abutton on the page of my address book form, so that when I hit it it creates a report of an address label for that current record on the screen only.
I hope this makes sense. If this is possible, I then need to extrapolate this technique to print a report of a subform (of samples sent) of a current client record.
All help gratefully received
Xenia
View 4 Replies
View Related
Feb 14, 2006
I have a form with data in formview. When I select a record I want it to highlight with a specific color.
How can I do that?
View 1 Replies
View Related
Apr 9, 2006
I am creating a query based on two tables: 1) tblClient (only one primary key "ClientID") and 2) tblContactDate (with two primary keys "ClientID" and "ContactDate").
Each client may have more than one contact date.
In the query, I only want the records from the tblContactDate showing for the MOST RECENT contact date of the client only; however, right now the query is producing more than one record for those clients with more than one contact date.
Not sure if I need add some special code to the "criteria" area in the Design View, or somewhere else. Your help is appreciated. :confused:
View 5 Replies
View Related
Jun 10, 2007
hi all, i create a form which have a button to append the current entry to another table but i only want to append the current entry tat is open. So how do u set the query to append one entry(the current open entry on the form) using the criteria.
i notice the query only have sum, aver, +- ...etc..
tks.
View 7 Replies
View Related
Jun 1, 2005
I am a relative Newbie so if my question sounds stupid, bear with me:
I have a form with a subform linked to a query.
On the subform i have a checkbox[Recieved] and when checked adds a date to
another field [Year] on the subform which in turn updates the record in a table[Orders].
I then click a command button on the form which runs a crosstab query based on the
updated fields in the table [Orders].
This works well except for the last record ammended which does not update the [Year]
field in the table [Orders]. When the command button is clicked it is still the current
record in the subform.
clicking another record in the subform solves this or closing the form and reopening it before running the crosstab query.
Is there an easier way to automate the update? So that the user does not have to select another record first.
View 4 Replies
View Related
Aug 8, 2005
Hi All,
I Built a form with a selectbox. On each form there a several records shown. After each record you see a selectbox wich you can turn on and off. Now, everytime I click on a selectbox it automatically goes to the first record on the screen, why is that? With this action I want the currentrecord to be modified, but can't figure out how.
I think it has something to do that an 'edit mode' is selected.
Does anyone know how? Thanks in advance.
View 8 Replies
View Related
Dec 9, 2005
Hi,
I have two combo boxes where the first lists values in one table. On selecting one I want a second combo populated with records in another table where foreign key = primary key.
Obviously the query is
select location from client,location
where client.client = location.client
However I can not get to the current record.
Alternatively, I could populate a list programmatically on the afterupdate event but I am not sure how to do this or if this is the best way. Any advice would be most appreciated.
Thanks John.
View 1 Replies
View Related