Fill Field in a report

[kronar]

How can I fill a text box in a report with data from a secondary table.
This doesn't work:
Private Sub Report_Open(Cancel As Integer)
Dim selstr As String
Dim db As Database
Dim TRrec As Recordset
MsgBox "Load Tax Rate"
Set db = CurrentDb()
selstr = "Select DistinctRow [TR_Tax].[TR_TaxRate] from [TR_Tax]"
Set TRrec = db.OpenRecordset(selstr, dbOpenDynaset)

If TRrec.RecordCount = 0 Then
MsgBox "There are no records in TR_Tax"
Exit Sub
End If
rpttax_rate = TRrec(0)
End sub

get error "You can't assign a value to this object"
help??

 

[dhookom]

Why would you use code? Would the table TR_Tax contain any more than a single record? I can think of at least three methods of displaying the value with no code involved:
1) DLookup()
2) add the table to your report's record source
3) use a subreport

You are getting an error most likely because you are running the code too soon. Run the code in the On Format event of the section of the report containing the text box. However, again, this is an odd method for displaying a value in a report.

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

Thanks Duane.
Yes, TR_Tax table contains just one record with one field.

1)I am not familier with DLookup function, other than one of my books says that this includes a lot of overhead?

2)Don't know how to do this. The report source is a query in the query tab.

3)I had tried this as a subform(on a form) but thought this was excessively complex and could not reference the taxrate to use in computations.

I moved code to the section format proceedure. How do I make it only happen once and not after the time subreport, then material subreport just after the overhead subreport?

 

[PWise]

does rpttax_rate have a control source?
if yes delete it

 

[dhookom]

Just add the TR_Tax table to your report's record source query and don't join it to anything. Add the field to the grid and then use it in your report.

DLookup() would cause very little overhead unless you called it multiple times.

I would not use code. I would not use DLookup(). I would probably not use a subreport. I would just add the table.

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

How do I add this new query? I tried adding it after the ; on the SQL view of the query, but got an error "chars found after end of query. I removed the ; - got error. I changed it to , - got error.??

 

[dhookom]

Just view the design view of your report's record source. Then add the table and pull it fields to the grid. If you can't figure this out, reply with the SQL view of your report's record source.

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

Thanks, I finally got that one figured out. Got the Dlookup working too.