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

Mail Merge Current Record Only

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

Dynamically Update Field Of A Current Record Based On Previous Record

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

Modules & VBA :: Linking Tables By Auto Inserting A Record Using Current Record

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

Mail Merge Record Selection

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

Modules & VBA :: Display Last Record Next To Empty Space On Current Record

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

Queries :: Evaluating Current Record To Previous Record To Get Counts

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

Modules & VBA :: Mail Merge For Single Record

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

Refer To Previous Record In Current Record Query

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

Population Current Record With Information From Next Sequential Record

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

Queries :: Can A Calculated Field Look At Record Before Current Record

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

Queries :: Merge Unique Data With Same Persons Record

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

Can I Use Combo Box List Out My Record With Going To The Current Record ??

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

Need Form For New Record/edit Current Record

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

Tables :: Merge Duplicate / Similar Records Into 1 Record In Access 2010

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

Faxing Current Record Or Record Set

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

No Current Record

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

Help With Get Current Record On Different Ids...

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

Displaying Current Record

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

Report Of Current Record

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

Highlight Current Record

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

How To Show Most Current Record Only?

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

How To Set Query On The Current Record

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

Update Current Record

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

Edit Current Record

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

Filtering For Current Record

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







Copyrights 2005-15 www.BigResource.com, All rights reserved