Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 9th, 2012, 11:31 AM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: May 2012
Location: Edinburgh, Scotland
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Displaying data in Word bookmark

I have a database that records samples received into a lab. This includes the sample type and the area/region it's come from:

Material Region
Sliced portions of brain Frontal
Sliced portions of brain Parietal
Sliced portions of brain Temporal
Sliced portions of brain Occipital
Sliced portions of brain Cerebellum
Sliced portions of brain Basal Ganglia
Sliced portions of brain Thalamus
Organ sample Pituitary

To enable the data to be exported to a Word template for reporting purposes, I have to string all the information together so it goes out as a paragraph and is placed using a Bookmark. However the code I'm currently using is giving me the data like this:

Sliced portions of brain, Frontal; Sliced portions of brain, Parietal; Sliced portions of brain, Temporal; Sliced portions of brain, Occipital; Sliced portions of brain, Cerebellum; Sliced portions of brain, Basal Ganglia; Sliced portions of brain, Thalamus; Organ Sample, Pituitary.

This is the code I'm using to get it like this is:

Dim strSQL1 As String
Dim Answer As String
Dim i As Integer
Dim rs As New ADODB.Recordset

On Error GoTo HandleErr

strSQL1 = "SELECT qryFrozenReceived.MatRecCase, qryFrozenReceived.MatOption, qryFrozenReceived.MaterialType, qryFrozenReceived.Area " _
& " FROM qryFrozenReceived " _
& " WHERE qryFrozenReceived.MatRecCase = " & CaseID & ""

rs.Open strSQL1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.RecordCount >= 1 Then
Answer = rs!MaterialType & " - "
MatType = rs!MaterialType

For i = 1 To rs.RecordCount
Answer = Answer & (rs!Area) & ", "

Next i

FrozenReceived = Left(Answer, Len(Answer) - 2)
End If

Ideally I want the data to show as:

Sliced portions of brain: Frontal, Parietal, Temporal, Occipital, Cerebellum, Basal Ganglia, Thalamus;
Organ Sample, Pituitary.

I know that I need to create two recordsets and open the first one, the MaterialType, and then loop through and add that Material Type and the associated areas to the string before moving on to the next material type but I just can't get it to work.

If you had any thoughts on the matter I'd love to hear them.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Write Excel VBA Code to Target Bookmark in Word Doc slbibs Word VBA 3 July 31st, 2009 09:47 AM
Pass textbox string to Word document bookmark mii2029 ASP.NET 3.5 Basics 1 June 6th, 2008 01:13 PM
Me.Bookmark = Me.RecordsetClone.Bookmark rkellogg Access VBA 5 September 8th, 2006 06:28 AM
displaying a word doc using asp Adam H-W Classic ASP Databases 0 December 10th, 2005 09:15 AM

All times are GMT -4. The time now is 02:09 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.