Displaying more than one record in a single field 3

[spherey]

This is either going to be a very odd question or a very simple one. I have a report which is built upon a query which draws from more than one table. One table is "company information", and the other table is "company notes". The company information is always the same, whereas one company can have many different entries into the notes table.

The way the report is currently set up is that for each company, all the information is displayed for each separate note. While it would be easy enough to set all the fields to "Can Shrink" and "Hide Duplicates" so that only the new information (i.e., each individual note) would display, this is not exactly what the company management wants. Rather than having a row of fields for each company's information, and subsequent entries from the "notes" table appearing individually beneath this row of unchanging information, they would like to see ALL the entries from the notes table appear in the same field - that is, whether there is one note, or two, or twelve, they want all of them to appear in the same box, and they'd like the other fields to grow not merely to accomodate their own data, but to match the height of the notes field (which might only be an inch tall for one note, but if a company has several different entries, might be six inches tall instead).
Does anyone have any idea as to how I can go about setting this up? I'm stumped, though I can't help but think that there's probably some easy solution that I'm missing.

Thanks a lot,

Spherey

 

[CosmoKramer]

It looks like a subreport would be what you're looking for. Do a search here in TT, or in Access help.

Comany information would be in your main report, and the notes data in the subreport.

Hoc nomen meum verum non est.

 

[spherey]

I don't follow you - I'm certainly open to using a subreport, but I don't see how that would solve either problem. Whether the notes field was a part of the main report, as it is now, or was part of a subreport, I'd still have the same issues with it - I still don't know whether it's possible to output all the data into one field instead of having each entry go into its own cell, nor whether it's possible to make the size of the surrounding fields' boxes grow or shrink to match one another, not merely to accomodate their own data. And if these things are possible, I still don't know how to go about it. Could you clarify how a subreport would be what I'm looking for?

Thanks,

Spherey

 

[LarryDeLaruelle]

spherey:

Have you considered using sorting and grouping?

Group on the Company name (use at least the group header) and put all relevant information in that group band. Put the notes in the detail section.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[TwoOdd]

Yes, both things can be done. You can populate a textbox with values from multiple records and you can increase the textbox size of the other textboxes in the line to match the tallest textbox in the line. The only way I know of doing it, is writing some Intermediate to Advanced VBA code in the 'On Print' Detail Section.

Let me make sure I understand what you're wanting before I get into the details.

You want one line per company and I am assuming that each textbox has a border line around it. Basically the finished report would look like an Excel Spreadsheet where each cell has it's own information and the cell for the notes has all the notes (whether it's 1, 20 or more) in it. Also, as the 'notes' cell increases, you need the other textboxes to increase to the same height as the 'notes' cell -- assuming that the 'notes' cell will be the tallest cell.

If my assumption is correct and you're willing to write some VBA, let me know.

 

[spherey]

Larry - I think that that would work, but it wouldn't create a report which would look the way these people want it to work. I wish it were that simple, but they're picky.

Twoodd - That's exactly what I'm looking to do. And yes, I'm keen to write some code. I'd welcome any coding expertise you'd care to send my way.

Thanks a lot,

Spherey

 

[TwoOdd]

Spherey --
I apologize first of all for the length of the response, but I’m trying to be as specific as possible. With that said, I’ll break up the response into three responses. This one will give a summary of what we’re trying to do and the other two will be the two modules that need to be created.

We are going to create a temporary table to store one record per company and the notes field will contain all the notes for the given company. The report will then be based on this temporary table. Next, we’ll use a module to delete the contents of the temporary table and then update it with the newest information. Then we’ll create a module behind the report to format the report.

 

[TwoOdd]

Updating the Temporary Table

Create a temporary table that matches your query as far as field names and types go. One exception would be the notes field, you may want to make it a memo field in temporary table. Making it a text field is preferable because memo fields can be picky, so if you know all the notes combined for a given company will be less than 255 characters make the notes field a text field otherwise you’ll have to make it a memo field.
What we are going to do is run a module that will delete the contents of this table and update it with the new data. Your report will pull from this temporary table and you will need to run the module prior to running your report so you have the newest information.

There are other ways of accomplishing this task, but this is the way I know that works.

So, we now have a temporary table – for this example we’ll call it ‘tblCompanyInfo’. You can name it whatever you want, but once it’s named we don’t want to change it.

Let’s create a new module – name it modAppendCompanyInfo
Notice the name of the sub procedure is different from the module name

Public Sub AppendCompanyInfo()
‘Declare variables
Dim tdf As TableDef ‘This is your table definitions
Dim TrunSQL As String ‘This is the SQL Statement to delete the contents of the table
Dim rst As Recordset ‘This is your recordset variable
Dim CurrentCompany as String ‘This will make sense below
Dim CompanyNotes as String ‘This will contain all the notes for a given company

‘Let’s start by making sure the temporary table has no data
' Refresh TableDefs collection.
CurrentDb.TableDefs.Refresh
'Find ‘tblCompanyInfo’and delete all records.
For Each tdf In CurrentDb.TableDefs
If tdf.Name = "tblCompanyInfo" Then
TrunSQL = "DELETE " & tdf.Name & ".* FROM " & tdf.Name
DoCmd.RunSQL TrunSQL
End If
Next tdf

‘Now we have an empty tblCompanyInfo

‘Lets get our recordset now so we can populate tblCompanyInfo with the new data
‘Go to the query you created and view the SQL – Copy it and paste it below – make sure its all on one line – also make sure your query sorts by company name
rst = CurrentDb.OpenRecordset("Paste SQL here between the quotes", dbOpenDynaset)

‘make sure we are at the beginning of the recordset
rst.MoveFirst

‘We’re going to loop through the recordset by company, when we have captured all of the company’s info, we will insert one record into tblCompanyInfo for that company.
‘Set the CurrentCompany Variable
CurrentCompany = rst.Fields![CompanyName] ‘assuming CompanyName is the field name for the company name.

‘Set CompanyNotes to nothing
CompanyNotes = “”
‘Start the loop and do until the end of the recordset
Do Until rst.EOF
‘Set all fields except notes a variable
‘Otherwise, when we move to the next record we’ll lose the current company info
CompanyName = rst.Fields![CompanyName]
CompanyAddress = rst.Fields![CompanyAddress]
Etc.

‘Append notes to CompanyNotes
CompanyNotes = CompanyNotes & “ “ & rst.Fields![Notes]

‘Now we move to the next record
rst.MoveNext

‘Make sure we are not at the end of the recordset
If Not rst.eof then
‘Check to see if it’s the same company
If CurrentCompany <> rst.Fields![CompanyName] Then
below
InsertRecord CompanyName, CompanyAddress, CompanyNotes
‘Reset CurrentCompany and CompanyNotes
CurrentCompany = “”
CompanyNotes = “”
End If
Else
‘We are at the end of the recordset
'Insert the record - call the sub procedure below
InsertRecord CompanyName, CompanyAddress, CompanyNotes
End If

‘If we aren’t at the end of the recordset and the next record is for the same company, we just loop back and append the notes
Loop

End Sub

'This is the sub procedure to insert the records
Public Sub InsertRecord(ByVal CompanyName as String, CompanyAddress as String, CompanyNotes as String)
Dim SQL1 As String

SQL1 = &quot;INSERT INTO tbl_Responses VALUES('&quot; & CompanyName & &quot;','&quot; & CompanyAddress & &quot;','&quot; & CompanyNotes & &quot;')&quot;

DoCmd.RunSQL SQL1
End Sub

 

[TwoOdd]

Changing the height of all text boxes

Please be sure to see final notes at the bottom

Make sure all the textbox's in the detail section have can shrink and can grow set to 'No'

But make sure the detail section itself is set to shrink and grow.

Go to the properties of the detail section of the report.
Go to the event tab and click on the … button and select ‘Code Builder’

It should give you something like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

End Sub

Put everything below in that sub procedure – You will have to modify some of it to match your information.

Dim ctl As Control
Dim line1 As Integer
Dim line2 As Integer
Dim line3 As Integer

'FYI 1440 Twips is equal to 1 Inch
'Access measures everything in Twips
'Convert Height and Width from Inches to Twips

line1 = 240 'Number of Twips to account for the height of 1 line fontsize 10 TimesNewRoman
line2 = 480 'Number of Twips to account for the height of 2 lines fontsize 10 TimesNewRoman
line3 = 720 'Number of Twips to account for the height of 3 lines fontsize 10 TimesNewRoman

I set my textbox to a specified width and then estimated the number of characters I could fit on each line
It was trial and error, so you may have a little work to do.
Here is what I came up with using font size 10 Times New Roman and the textbox is 4.5 inches wide
<=79 = 1 line
80 to 140 = 2 lines
140 on is 3 lines -- in my particular case none of my records exceeded 3 lines.

It’s been about a year since I wrote this code, but if my memory serves me correctly, I could not capture the height of the tallest control, ie in your case the ‘notes’ field. That’s why I had to check to length of the string in that field and guestimate how many rows I needed. It’s not pretty, but it works.

For Each ctl In Me.Detail.Controls
‘Find the control for company notes
If ctl.Name = &quot;CompanyNotes&quot; Then

If Len(ctl) > 140 Then 'note length here is number of characters in the field
‘because the length is greater than 140 we increase the height of all controls in detail section to 3 lines
Me!CompanyNotes.Height = line3 'note height here is measured in Twips
Me!CompanyName.Height = line3
Me!CompanyAddress.Height = line3

'When shrinking the detail section, make sure it's the last control you shrink
‘Also make sure you use the PERIOD after Me not the !
Me.Detail.Height = line3
Else
If Len(ctl) > 79 Then
‘because the length is greater than 79 we increase the height of all controls in detail section to 2 lines
Me!CompanyNotes.Height = line2 'note height here is measured in Twips
Me!CompanyName.Height = line2
Me!CompanyAddress.Height = line2

'When shrinking the detail section, make sure it's the last control you shrink
‘Also make sure you use the PERIOD after Me not the !
Me.Detail.Height = line2
Else
‘because the length is less than 80 we set the height of all controls in detail section to 1 line
Me!CompanyNotes.Height = line1 'note height here is measured in Twips
Me!CompanyName.Height = line1
Me!CompanyAddress.Height = line1

'When shrinking the detail section, make sure it's the last control you shrink
‘Also make sure you use the PERIOD after Me not the !
Me.Detail.Height = line1
End If
End If
End If
Next

When you are in the Visual Basic Editor, click on the Tools menu and choose References. Make sure the following are marked and in this order
Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3

This is assuming you are using Access 2000. If you are using a different version, these references may be a little different. I haven’t mastered the references but I know that they can be the difference between code working and not working. If anyone else has more information on setting the references, help would be appreciated.

This may not be the prettiest solution to your problem, but it should work.

Good Luck and hope this has helped,

Twoodd

 

[TwoOdd]

Sorry, just found a typo

This:

If CurrentCompany <> rst.Fields![CompanyName] Then
below
InsertRecord CompanyName, CompanyAddress, CompanyNotes
‘Reset CurrentCompany and CompanyNotes
CurrentCompany = “”
CompanyNotes = “”
End If

should Read:
If CurrentCompany <> rst.Fields![CompanyName] Then
'Insert the record - call the sub procedure below
InsertRecord CompanyName, CompanyAddress, CompanyNotes
‘Reset CurrentCompany and CompanyNotes
CurrentCompany = “”
CompanyNotes = “”
End If

 

[TwoOdd]

Just a clarification:

In the section for Changing the height of all text boxes

Go to the properties of the detail section of the report.
Go to the event tab and click on the … button and select ‘Code Builder’

the ... button is for the On Format event

If anything else isn't clear please let me know.

Thanks

 

[spherey]

Twoodd -

Wow. Thank you so very much for all your time and energy in helping me out so much. I have been coding steadily since I got your first response, and so far, I haven't gotten it to work - I'm thinking that this is most likely a mistake in my code that I've made in the process of translating your example to use my field names - and I have sixteen fields instead of three. I haven't built the module to change the height of the text boxes yet, but will begin that momentarily. I've been working on the modAppendCompanyInfo first, after having built a new table with the same information as the query. At this point, I'm getting this error message - &quot;Compile error: syntax error&quot; when I run the module, and it's highlighting the SQL statement from the query that I pasted into the module. You said to make sure its all on one line, and that may be the problem - it won't all fit on one line, whether I paste it in or re-type it by hand. Pasting it breaks it into several lines automatically, and typing it out doesn't solve the problem, as the cursor simply won't move anymore, and the text won't wrap.

I'm sorry about the soon-to-be gargantuan length of this post, but I thought you might want to see the code, just to see if you spot something I'm missing beyond the SQL problem. Do you think that's the issue, that it won't all fit on one line? If so, do you have any ideas as to how I can get around that? O, and I caught the typo too, so I know that isn't the issue.

Again, thanks so much for your time and patience - I really appreciate it.
(code follows below)

Option Compare Database

Public Sub AppendCompanyInfo()

Dim tdf As TableDef
Dim TrunSQL As String
Dim rst As Recordset
Dim CurrentCompany As String
Dim CompanyNotes As String


    CurrentDb.TableDefs.Refresh

    For Each tdf In CurrentDb.TableDefs
        If tdf.Name = "tblCompanyInfo" Then
            TrunSQL = "DELETE " & tdf.Name & ".* FROM " & tdf.Name
            DoCmd.RunSQL TrunSQL
        End If
    Next tdf





rst = CurrentDb.OpenRecordset("SELECT [Prospect Table].[Main Contact?], [Prospect Table].[Dead?], [Prospect Table].[Company Name], [Prospect Table].Consultant, [Prospect Table].[Referred By], [Prospect Table].DateLeadReceived, [Prospect Table].[Number of Participants], [Prospect Table].[Recommended Platform], [Prospect Table].[Plan Assets], [Prospect Table].[Updated On], [Prospect Notes].Status, [Prospect Table].[Prospect Types], [PARC Table].Notes, [Prospect Table].[Number of Employees], [Prospect Table].[PContact Information], [Prospect Table].PPhone, [Referral Source Table].[RSContact Information], [Referral Source Table].RSPhone FROM [Referral Source Table] RIGHT JOIN ([PARC Table] RIGHT JOIN ([Prospect Table LEFT JOIN [Prospect Notes] ON [Prospect Table].[Company Name] = [Prospect Table].[Company Name]) ON [PARC Table].[Company Name] = [Prospect Table].[Company Name]) ON [Referral Source Table].[Company Name] = [Prospect Table].[Referred By]
WHERE ((([Prospect Table].[Main Contact?])=Yes) AND (([Prospect Table].[Dead?])=No)) ORDER BY [Prospect Table].[Company Name];", dbOpenDynaset)


rst.MoveFirst


CurrentCompany = rst.Fields![Company Name]

CompanyNotes = “”

Do Until rst.EOF
    
    
    [Company Name] = rst.Fields![Company Name]
    [PContact Information] = rst.Fields![PContact Information]
    [PPhone] = rst.Fields![PPhone]
    [Consultant] = rst.Fields![Consultant]
    [Referred By] = rst.Fields![Referred By]
    [RSContact Information] = rst.Fields![RSContact Information]
    [RSPhone] = rst.Fields![RSPhone]
    [DateLeadReceived] = rst.Fields![DateLeadReceived]
    [Number of Participants] = rst.Fields![Number of Participants]
    [Number of Employees] = rst.Fields![Number of Employees]
    [Recommended Platform] = rst.Fields![Recommended Platform]
    [Plan Assets] = rst.Fields![Plan Assets]
    [Updated on] = rst.Fields![Updated on]
    [Status] = rst.Fields![Status]
    [Prospect Type] = rst.Fields![Prospect Type]
    [PARC Notes] = rst.Fields![PARC Notes]
    
    

    
    CompanyNotes = CompanyNotes & “ “ &  rst.Fields![Status]

    
    rst.MoveNext
    
    
    If Not rst.EOF Then
    
    If CurrentCompany <> rst.Fields![Company Name] Then

        InsertRecord [Company Name], [PContact Information], [PPhone], [Consultant], [Referred By], [RSContact Information], [RSPhone], [DateLeadReceived], [Number of Participants], [Number of Employees], [Recommended Platform], [Plan Assets], [Updated on], [Status], [Prospect Type], [PARC Notes]
        
        CurrentCompany = “”
        CompanyNotes = “”

    End If
    Else
        
    InsertRecord [Company Name], [PContact Information], [PPhone], [Consultant], [Referred By], [RSContact Information], [RSPhone], [DateLeadReceived], [Number of Participants], [Number of Employees], [Recommended Platform], [Plan Assets], [Updated on], [Status], [Prospect Type], [PARC Notes]
    End If

    
Loop

End Sub


Public Sub InsertRecord(ByVal [Company Name] As String, [PContact Information] As String, [PPhone] As String, [Consultant] As String, [Referred By] As String, [RSContact Information] As String, [RSPhone] As String, [DateLeadReceived] As String, [Number of Participants] As String, [Number of Employees] As String, [Recommended Platform] As String, [Plan Assets] As String, [Updated on] As String, [Status] As String, [Prospect Type] As String, [PARC Notes] As String)
Dim SQL1 As String

SQL1 = &quot;INSERT INTO tbl_Responses VALUES('&quot; & [Company Name] & &quot;','&quot; & [PContact Information] & &quot;','&quot; & [PPhone] & &quot;','&quot; & [Consultant] & &quot;','&quot; & [Referred By] & &quot;','&quot; & [RSContact Information] & &quot;','&quot; & [RSPhone] & &quot;','&quot; & [DateLeadReceived] & &quot;','&quot; & [Number of Participants] & &quot;','&quot; & [Number of Employees] & &quot;','&quot; & [Recommended Platform] & &quot;','&quot; & [Plan Assets] & &quot;','&quot; & [Updated on] & &quot;','&quot; & [Status] & &quot;','&quot; & [Prospect Type] & &quot;','&quot; & [PARC Notes] & &quot;')&quot;

DoCmd.RunSQL SQL1
End Sub

 

[TwoOdd]

Let me answer your first question:

Because the sql statement is so long, try setting it to a variable ie:

MyQuery = &quot;SELECT [Prospect Table].[Main Contact?], [Prospect Table].[Dead?], [Prospect Table].[Company Name], [Prospect Table].Consultant,&quot;
MyQuery = MyQuery & &quot; [Prospect Table].[Referred By], [Prospect Table].DateLeadReceived, [Prospect Table].[Number of Participants],&quot;
MyQuery = MyQuery & &quot; [Prospect Table].[Recommended Platform], [Prospect Table].[Plan Assets], [Prospect Table].[Updated On],&quot;
MyQuery = MyQuery & &quot; [Prospect Notes].Status, [Prospect Table].[Prospect Types], [PARC Table].Notes, [Prospect Table].[Number of Employees],&quot;
MyQuery = MyQuery & &quot; [Prospect Table].[PContact Information], [Prospect Table].PPhone, [Referral Source Table].[RSContact Information], [Referral Source Table].RSPhone&quot;
MyQuery = MyQuery & &quot; FROM [Referral Source Table] RIGHT JOIN ([PARC Table] RIGHT JOIN ([Prospect Table LEFT JOIN [Prospect Notes] ON [Prospect Table].[Company Name] = [Prospect Table].[Company Name]) ON [PARC Table].[Company Name] = [Prospect Table].[Company Name]) ON [Referral Source Table].[Company Name] = [Prospect Table].[Referred By]&quot;
MyQuery = MyQuery & &quot; WHERE ((([Prospect Table].[Main Contact?])=Yes) AND (([Prospect Table].[Dead?])=No)) ORDER BY [Prospect Table].[Company Name];&quot;

rst = CurrentDb.OpenRecordset(MyQuery,dbOpenDynaset)

See if that gets you past creating the recordset

Also check out my final note on Changing the height of all text boxes about the references

 

[TwoOdd]

As for you second question, everything else looks okay. One really doesn't know though until the code has been executed.

Good luck and let me know if you have any other problems,

Twoodd

 

[spherey]

Hey Twoodd,

Here's the latest. In the modAppendCompanyInfo, I changed the SQL statement to what you suggested. Now it's giving me the following: &quot;Compile error: invalid use of property&quot; and it's highlighting

rst =

in the line

rst = CurrentDb.OpenRecordset(MyQuery, dbOpenDynaset)

Meanwhile, I made all the changes in the report. And when I switch back to report view from the design view, I get a pop-up with this message: &quot;Run-time error '438': object doesn't support this property or method&quot; and when I click on debug, it highlights this line:

Me![Company Name].Height = line3

I thought that the error might be because I had carried the &quot;Dim line1&quot; and &quot;line1&quot; variables all the way up to line20, whereas the Me! figures only went up to three - but even after I went back into the code and erased the extra variables, I got the same error message.

Just in case you catch something I don't, I'm pasting the code in (and I did check the references - they're all in place, and all in the right order).

Thanks so much for all your help. I'm actually about to leave for the day, but I'll check the board later this evening and will be back at the code first thing tomorrow morning. Thanks again for all your time.
- Spherey
(code follows below)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
    Dim line1 As Integer
    Dim line2 As Integer
    Dim line3 As Integer
    
    
    line1 = 240
    line2 = 480
    line3 = 720


      
    For Each ctl In Me.Detail.Controls
    
        If ctl.Name = "Status" Then
            
    If Len(ctl) > 140 Then
        
        Me![Company Name].Height = line3
        Me![PContact Information].Height = line3
        Me![PPhone].Height = line3
        Me![Referred By].Height = line3
        Me![RSContact Information].Height = line3
        Me![RSPhone].Height = line3
        Me![DateLeadReceived].Height = line3
        Me![Number of Participants].Height = line3
        Me![Number of Employees].Height = line3
        Me![Recommended Platform].Height = line3
        Me![Plan Assets].Height = line3
        Me![Updated on].Height = line3
        Me![Prospect Type].Height = line3
        Me![PARC Notes].Height = line3
        Me.[Status].Height = line3
                
    
        Me.Detail.Height = line3
    Else
        If Len(ctl) > 79 Then
        
        Me![Company Name].Height = line2
        Me![PContact Information].Height = line2
        Me![PPhone].Height = line2
        Me![Referred By].Height = line2
        Me![RSContact Information].Height = line2
        Me![RSPhone].Height = line2
        Me![DateLeadReceived].Height = line2
        Me![Number of Participants].Height = line2
        Me![Number of Employees].Height = line2
        Me![Recommended Platform].Height = line2
        Me![Plan Assets].Height = line2
        Me![Updated on].Height = line2
        Me![Prospect Type].Height = line2
        Me![PARC Notes].Height = line2
        Me.[Status].Height = line2
                
        
        Me.Detail.Height = line2
        Else
        
        Me![Company Name].Height = line1
        Me![PContact Information].Height = line1
        Me![PPhone].Height = line1
        Me![Referred By].Height = line1
        Me![RSContact Information].Height = line1
        Me![RSPhone].Height = line1
        Me![DateLeadReceived].Height = line1
        Me![Number of Participants].Height = line1
        Me![Number of Employees].Height = line1
        Me![Recommended Platform].Height = line1
        Me![Plan Assets].Height = line1
        Me![Updated on].Height = line1
        Me![Prospect Type].Height = line1
        Me![PARC Notes].Height = line1
        Me.[Status].Height = line1
        Me.Detail.Height = line1
        End If
    End If
    End If
Next
End Sub

 

[TwoOdd]

Here's another way around the one line problem with the recordset:

Everytime you have to break a line, finish the line with &quot; _
Start the next line with &quot; for example:

rst = CurrentDb.OpenRecordset(&quot;SELECT [Prospect Table].[Main Contact?],&quot; _
&quot; [Prospect Table].[Dead?], [Prospect Table].[Company Name],&quot; _
&quot; [Prospect Table].Consultant, [Prospect Table].[Referred By],&quot; _
&quot; [Prospect Table].DateLeadReceived, [Prospect Table].[Number of Participants],&quot; _
&quot; [Prospect Table].[Recommended Platform], [Prospect Table].[Plan Assets], [Prospect Table].[Updated On],&quot; _
&quot; [Prospect Notes].Status, [Prospect Table].[Prospect Types],&quot; _
&quot; [PARC Table].Notes, [Prospect Table].[Number of Employees],&quot; _
&quot; [Prospect Table].[PContact Information], [Prospect Table].PPhone,&quot; _
&quot; [Referral Source Table].[RSContact Information], [Referral Source Table].RSPhone&quot; _
&quot; FROM [Referral Source Table] RIGHT JOIN ([PARC Table] RIGHT JOIN&quot; _
&quot; ([Prospect Table LEFT JOIN [Prospect Notes] ON [Prospect Table].[Company Name] = [Prospect Table].[Company Name])&quot; _
&quot; ON [PARC Table].[Company Name] = [Prospect Table].[Company Name]) ON&quot; _
&quot; [Referral Source Table].[Company Name] = [Prospect Table].[Referred By]&quot; _
&quot; WHERE ((([Prospect Table].[Main Contact?])=Yes) AND (([Prospect Table].[Dead?])=No))&quot; _
&quot; ORDER BY [Prospect Table].[Company Name];&quot;, dbOpenDynaset)

I'm not sure why the other code isn't working to change the textbox height. I'm not able to reproduce your error on my side.

Twoodd

 

[Eupher]

Umm... Shouldn't the syntax be:

Set rst = ...

for a recordset variable?

Ken S.

 

[TwoOdd]

Thanks,

I had been looking at it too long and missed that error.

Also, in the line height it should read
Me![Status].Height = line3 - change for line2 and 1 as well.

Not

Me.[Status].Height = line3

Ken - think you could help out with the other error --
&quot;Run-time error '438': object doesn't support this property or method&quot;

I get that only when my references are not in order or checked, but spherey says they are all marked and in the same order as I have mine.

Any thoughts?

Twoodd

 

[Eupher]

Hmmm...
Re: the Run-time error '438' message...

I tried setting the control.height property both on a form and a report and was unable to reproduce the error. Worked perfectly first and every time. I also suspect that it's a references problem, but obviously can't confirm that. FWIW, here are my references from my little test DB:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

I'm running Access XP. Sorry I couldn't shed more light on this one...

Ken S.

 

[spherey]

Twoodd and Ken,

Thanks for the additional suggestions.
Fixing the rst = seems to have fixed the issue with the SQL. Now, I'm instead getting an error that reads &quot;Compile error: external name not defined&quot; whereupon it hightlights this portion:

[Company Name] =

of this line of code:

[Company Name] = rst.Fields![Company Name]

I don't know what the problem is there - it would seem to be defined to me, but apparently not.
Also, I changed the . back to a ! in the lines in which you advised me to do so for the record height. And so far, I'm still getting the same run-time error as before.

Here are my references, in this order:
Visual Basic for Applications
Microsoft Access 9.0 Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Outlook 9.0 Object Library

I'd love to be able to get these to work, but I understand if you're getting tired of it. I'm going to go pass out some stars, as you've spent a lot of time on this and I really appreciate all the thought and effort. I can't understand why someone else would be able to get the code work and I wouldn't, apart from there being some error in my coding of the fields' names, which is entirely possible. Most of my fields are names with two words, and I've enclosed all those names in [], and I think I'm getting all the relevant code after each additional field I've added in, though, so I'm stumped. . .

Thanks,

Spherey