Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 1st, 2006, 02:25 AM
Registered User
 
Join Date: Jan 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Referencing a field from the current recordset

I am trying to reference various fields in the Detail_Format subroutine that are part of the current record. These fields are NOT defined as controls on the report. I am receiving the following error message - "Run-time error '2465'. 'Database' can't find the field 'fieldname' referred to in your expression."

So for a table 'tbl' with the fields 'FName', 'LName' and 'Suffix' I have tried to reference the 'FName' field as -
FName, [FName]
Me.FName, Me!FName, Me![FName], Me.[FName]
tbl.FName, tbl!FName, tbl![FName], tbl.[FName]

Although it doesn't make sense to me to use the Me. prefix, when I do I can see FName.

Also, if I define a control on the report that references FName then I can "see" it in the subroutine.

Any help would be much appreciated!

 
Old January 3rd, 2006, 08:36 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure why you just don't use "Me.FName" to reference the field. Me refers to the report, and FName refers to the control or field, and Me.FName returns the data currently in the control. What I do is to put the fields on the report, and then just set their visible property to No. Then I don't have any problems referencing them, and they are not shown to the user.

Alternatively, seeing as it looks like you are trying to concatenate text fields into something like "Smith, John, Jr." why don't you do this in an underlying query by adding a field like this to a query that pulls all your data from tbl:

FullName:([LName] & ", " & [FName] & ", " & [Suffix])

The results in the query will be "Smith, John, Jr." and you can just drop this field onto your report - after you make the query the record source for the report.

HTH


mmcdonal
 
Old January 5th, 2006, 01:45 AM
Registered User
 
Join Date: Jan 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi mmcdonal,

Thanks for responding. I have over 20 fields that I need to reference and concatenate differently based on values in those fields. The example I gave was just that - an example. In the past I have used "in"visible controls as you mentioned; but have always questioned why I've had to do it. Do you know why I can't "see" the fields? As I said - maybe not so clearly - when I type in Me. in VBA the listbox that comes up shows all the fields in the recordsource - even though they are NOT controls on the report - as well as other properties of Me.

Another track I took was to see if there is a RecordSetClone - like for forms - for reports. There's not that I could find.

So for this task I defined my "in"visible control and set the controlsource to a concatenated string of all the values I need to reference in VBA.

But thanks for your input.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Referencing another field and more scandalous Access VBA 1 January 30th, 2007 09:28 AM
Current Recordset does not support updating. u813222 Classic ASP Professional 1 March 3rd, 2006 08:41 PM
ADODB.Recordset (0x800A0CB3)Current Recordset does tks_muthu Classic ASP Databases 0 June 16th, 2005 07:22 AM
How to set current recordset (rst.) gasma1975 Access 1 February 11th, 2005 11:42 AM
lookup table current field. Squid Access 0 December 21st, 2003 07:08 PM





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