How do I pass a string to a text box in a report?

[Cosette]

Hi all,

I am not understanding what I need to do here. I have a procedure on Format that 'should' decide which name to pass to the first line of a report (txtLine1). However, if I define txtline to have a few fields in it, then regardless of what I write in the procedure, it will print what I selected. So I do I go about getting the procedure to return the correct text in txtline1? Thanks for your help


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

Dim strName As String

If Me!Married = -1 Then
strName = "[tblparent].[first name]&' '&[tblparent].[last name]&' & '&[tblparent_1].[first name]&' '&[tblparent_1].[lastname]"
Else: strName = "[tblparent].[first name]&[tblparent].[last name]"

Me!txtLine1 = strName

End If
End Sub

 

[THWatson]

Cosette
I think you need to use double quotes rather than single quotes between the fields. Also remove the quotes at the beginning and end.

Try this...

strName = [tblparent].[first name]& " " & [tblparent].[last name]& " " & &[tblparent_1].[first name]& " "&[tblparent_1].[lastname]

Tom

 

[Cosette]

Tom,

What do I write in txtLine1. What field should go there? That is what I am not guetting.

Thanks

David

 

[THWatson]

Cosette
txtLine1 should be an "unbound" field, as it is filled by code. It should not have a control source.

Also, it should be Me.Married rather than Me!Married.

Tom

 

[THWatson]

Cosette
This should be the full code.

Dim strName As String
strName = ""

If Me!Married = -1 Then
strName = [tblparent].[first name]& " " & [tblparent].[last name]& " " & &[tblparent_1].[first name]& " "&[tblparent_1].[lastname]
Else
strName = [tblparent].[first name]& " " & [tblparent].[last name]

End If
Me.txtLine1 = strName

Then if you leave the txtLine1 text box without a control source, it should get filled based on the code. Also make sure that you have created txtLine1 as a completely unbound text box, that you didn't drag it in from the field list. If you dragged it in, then delete that text box and just create another one called txtLine1.

However, I also note that you have two tables. So this code assumes, of course, that the report has access to both tables, tblParent and tblParent_1...that both those tables are in the query that populates your report, or there is a subreport, because the data has to be available in order for it to be filled in.

Post back if you still need help.
Tom

 

[Cosette]

Tom,

Thank you very much for te replies. Unfortuntately, I get a Run Time Error '2465', Can't find the field '|' referred to in your expresssion.

The line highlighted is strName = [tblparent]...

So I tried to change the value of teh line to simple fields, and it wouldn't work anyway. However, as soon as I started the line with double quotes, it executed and returned the field name.

Thanks for your help again Tom.

 

[THWatson]

Cosette
Well, I'm not sure what's happening there, because that's not usual. Are you sure that the correct names will be produced in each instance - the parent names that match the name of the record?

If both tblParent and tblParent_1 are available to the report, through the query that is the RecordSource for the report, then the syntax that I suggested is what will work. Otherwise, you have to use DLookup functions, or some other approach.

A DLookup expression searches a field in a table, and uses a WHERE clause to be sure the data matches the record that is being produced on the form or report.
example
Me.txtLine1 = DLookup("[LastName]", "tblParent", "RecordID = " & TheRecordIDThatMatchesMe.Married & ")

Or something like that.

Tom

 

[Cosette]

Tom,

I figured out how to run the report with the code, but this requires me to have the fields in the report design grid. In other words, until tblparent.firstname was in the grid, it would recognize its value. Does that make sense, or is there a way for these fields to pull from the query without needing to be in the report (I knoe I can make it a visible field, which is what I did).

Thanks and sorry for bothering you with this.

 

[THWatson]

Cosette
You're not bothering me. What you encountered is what I was trying to get at. The fields have somehow to be available to the report.

Whether or not the fields have to be pulled into the report design grid depends upon how the RecordSource for the report is set up.

I'm not sure that I know enough about the over-all structure of your database to say much more. But I'm willing to try to help.

For starters, you could post the SQL from the query that is the RecordSource for the report. In case you don't know how to do that, here's how: Open the query in Design View, click on SQL view, select the SQL, copy it, then paste it in a reply here.

Two other questions: Is this a large database with a lot of records? Is there much sensitive data in it? After I see the SQL, I will reply further and you will understand the reason for these questions.

Tom

 

[Cosette]

Tom,

I simplified the query by selecting only one parent. Here is the SQL

SELECT tblParent.FirstName, tblParent.LastName, tblParent.Address, tblParent.City, tblParent.State, tblParent.ZipCode, tblParent.Married, tblStudent.Grade
FROM tblParent RIGHT JOIN tblStudent ON tblParent.[Parent ID] = tblStudent.FirstParent;


It is a very straighforward query I believe

David

 

[THWatson]

David
Something looks problematic about your join. You are joining the [ParentID] field from tblParent to the [FirstParent] field from tblStudent. These look like two different data types, one number and the other text.

If there are two tables, tblParent and tblStudent, then if you want the records from the two tables to be properly related to one another, the [ParentID] field should also be in tblStudent, so that you can create a one-to-many relationship join.

In your simplification of the query, I assume you took out the tblParent_1 table and fields, but the same would hold there. The joins have to be correct in order to get the correct information out in queries and reports.

If you are interested in sending the database to me to look at, I can tell you how to do that.

Tom

 

[Cosette]

Tom,

ParentID is a autonumber which is referenced in tblStudent as a LongInteger which is correct. tblParent_1 is a repeat of tblParent because each child has two parents, and I created unique record for each parent. Therefore I had two create two fields in tblStudent, FirstParent and SecondParent.

The way I set it up is for one parent to have more then 1 child, i.e. one to many.

David

 

[THWatson]

David
I see. So the tblStudent.FirstParent field is the same as tblParent.ParentID. Sorry for confusing the issue. They just didn't look the same.

Then back to your original issue. If all of the fields you want to reference in the report are in the query, and the "Show" box is checked, then you shouldn't have to place those fields in the report design. And as long as they show in the field list available in the report design, you know that they are available.

However, if you need to, bring them into the report design, make their text boxes really small, and make them "not visible." If it works that way, great.

Tom

 

[Cosette]

Tom,

This is exactly what is throwing me off. This is the first time I see a report where eventhough the field names are in the query as show, and they are in the field list, the report won't run without the fields being put in the report grid. There must be an option I forgot somewhere.

Thanks for the help.

david