public function to get data from table fields 1

[THWatson]

Using Access 2000

What is the correct formulation of a public function to pull data from fields in a table into a module that can be called when a report is opened?

Tom

 

[KenReay]

Not sure I understand this question, why not just bind the report to the query, and then you can have bound columns on the report.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[THWatson]

Ken
Well, it had to do with pulling data concerning a company and using it in a report header. The data would come from a table, tblMyCompany, that isn't related to any other table, and isn't bound to anything in the report.

It was a possible alternative to using DLookup, as they tend to hog resources.

But I think now that probably the DLookup is best, because I can combine the data, even though it spans 4 rows in the header, into one column in a query.

Anyway, Ken...thanks for your reply.

Tom

 

[MajP]

How about a sub report?

 

[Remou]

If you can use DlookUp, surely there must be a related field that can be used to build a query?

 

[KenReay]

hi

Two suggestions

1) just include the table tblCompany in the query, with no join(s), include the column(s) you want in the query output, you can then use the column(s) just like any other

2) in the onformat event (of the page header), put code like
txtCompanyName = MyCompanyName()

in a module, put code like
Public Function MyCompanyName() as String
Dim Db as DAO.Database
Dim Rs as DAO.Recordset

Set Db = CurrentDb()
set rs = db.openrecordset("SELECT myCoName FROM tblCompany;")
if rs.recordcount = 0 then
MyCompanyName = "Error"
else
MyCompanyName = rs!MyCoName
end if
rs.close
set rs = nothing
set db = nothing
end function

The above is using DAO, but you could simply amend to use ADO objects and methods if you wish



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[THWatson]

Well, the simplest method is, as Ken suggests, to add tblMyCompany to the query behind the reports.

I honestly thought I had tried that and ended up with a Cartesian product, which I didn't want. But I have it working now. And this avoids the DLookup function.

Thanks.

Tom