=<expr> function call - how to pass record?

Greetings,

  I am new to the MS Access product, but have experience writing DB
middle ware in other environments.

Right now my challenge is this: I want to write an expression in a
report control that is a complex expression of the record fields.  I
see how to directly write an expression referring to the record fields
like [Customer Name] and so on, and I also see how to make a function
call passing one or more fields as function arguments as above, but
what I really want to do is pass a reference to the current record,
and then within my function be able to refer to all the different
fields of that record.

  I'm thinking there's probably some very simple way to do this, but
it's syntax is not guessable to me and I'm not having much luck with
Microsoft "Help".

For example, in the 'Detail' section of my report, what I want to
print is:

  =CategoryA([record])


Then in my Module, I want to define a function something like this:

Function CategoryA(rec)
   CategoryA = rec.speed + rec.weight ' +  ...  some nasty formula
involving many record fields
End Function

What is the syntax to pass the current record as a whole and then to
refer to it's fields?

Thanks for any help you can provide!

-ej
0
Erik
3/17/2008 10:41:42 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
812 Views

Similar Articles

[PageSpeed] 28

Erik Johnson wrote:
>  I am new to the MS Access product, but have experience writing DB
>middle ware in other environments.
>
>Right now my challenge is this: I want to write an expression in a
>report control that is a complex expression of the record fields.  I
>see how to directly write an expression referring to the record fields
>like [Customer Name] and so on, and I also see how to make a function
>call passing one or more fields as function arguments as above, but
>what I really want to do is pass a reference to the current record,
>and then within my function be able to refer to all the different
>fields of that record.
>
>  I'm thinking there's probably some very simple way to do this, but
>it's syntax is not guessable to me and I'm not having much luck with
>Microsoft "Help".
>
>For example, in the 'Detail' section of my report, what I want to
>print is:
>
>  =CategoryA([record])
>
>
>Then in my Module, I want to define a function something like this:
>
>Function CategoryA(rec)
>   CategoryA = rec.speed + rec.weight ' +  ...  some nasty formula
>involving many record fields
>End Function
>
>What is the syntax to pass the current record as a whole and then to
>refer to it's fields?


No such syntax.

BUT, there is no need either.  All of the fields (and
controls) in a report's current record are available by just
referencing the report.  This means that your function can
use code like:

Function CategoryA()
	With Reports!yourreport
		CategoryA = .speed + .weight + . . .
		 . . .
	End With
End Function

-- 
Marsh
MVP [MS Access]
0
Marshall
3/18/2008 12:24:28 AM
That works! ;)

Thanks very much for your reply, Marsh!

-ej
0
Erik
3/18/2008 4:11:44 PM
   Actually, I think I have to take that back (at least partially).
Initially I thought I had the solution I was looking for because the
functions I was testing with were referring to fields that I had in my
detail section from before.  I can construct functions that refer to
controls within the detail section of my report but, I am trying to
create summary reports where my function analyzes the individual
fields and comes up with with a value representing multiple fields.
That's the value I want to be in the detail section of my report, not
all the individual column values.  I don't want to show the individual
fields in the detail section of my report, and so I never want to put
a control in there that holds the value for the individual fields.

  It's those named controls that are being referenced in the exacmple
you gave me (as can be verified by deleting a control the function is
referring to). So, I am back to the same question: How can I refer to
columns on the current record (outside of controls that have been
plopped into the detail section of the report)?

-ej

0
Erik
3/18/2008 8:13:05 PM
Erik Johnson wrote:
>   Actually, I think I have to take that back (at least partially).
>Initially I thought I had the solution I was looking for because the
>functions I was testing with were referring to fields that I had in my
>detail section from before.  I can construct functions that refer to
>controls within the detail section of my report but, I am trying to
>create summary reports where my function analyzes the individual
>fields and comes up with with a value representing multiple fields.
>That's the value I want to be in the detail section of my report, not
>all the individual column values.  I don't want to show the individual
>fields in the detail section of my report, and so I never want to put
>a control in there that holds the value for the individual fields.
>
>  It's those named controls that are being referenced in the exacmple
>you gave me (as can be verified by deleting a control the function is
>referring to). So, I am back to the same question: How can I refer to
>columns on the current record (outside of controls that have been
>plopped into the detail section of the report)?


Ahhh yes.

First, the code is referring to either the field or the
control with whatever name you use in the code.  If a field
in the record source and a control in the report have the
same name, then the code will reference the control.

The serious issue is that (unlike forms) Access tries to
optimize report performance by not retrieving fields that
are not used in a control.  That means that you need to
include each field in either a bound control or use a text
box with an expression that refers to a bunch of fields in
an expression such as
	=f1 & f2 & ...
Either way, you can make those control(s) invisible so they
don't get in the way of the stuff you want to display.

-- 
Marsh
MVP [MS Access]
0
Marshall
3/18/2008 10:40:14 PM
Reply:

Similar Artilces:

What function will check a row for a series of specific numbers?
I need to check a row of numbers, to see if specific numbers (say 1,5,7,8) are in that row. What is the easiest way to do this? =SUMPRODUCT(--(A1:M1={1;5;7;8}))>0 Regards, Peo Sjoblom "PuzzledK" wrote: > I need to check a row of numbers, to see if specific numbers (say 1,5,7,8) > are in that row. > What is the easiest way to do this? > ...

MFC explicit call to RegisterClassW
I'm trying to build the MFC library with a change: all calls to RegisterClass* become RegisterClass*W so that the apps that use this library will answer "true" to the IsWindowUnicode call (even though I'm building these apps *without* the _UNICODE flag--don't ask why I'm not wanting to set that compiler define; I'm constrained against setting it and making this a true Unicode app). Anyway, according to the help, if you register a window class using the *W function, then those windows should answer "true" to the IsWindowUnicode message and therefo...

control a sub/function via a dropdown menu
Hello, I have a dropdown menu on a chart. What must I do to control a sub/function depending on the selection of the dropdown menu. To make it clear .. every time I make a selection on the dropdown menu a function should be executed. Hope somebody can help me! Thanks in advance! daMike Mike - The dropdown is a Forms Toolbar dropdown, so you can right click on it to assign a macro. Also, you can link it to a cell, and the cell holds the index of the selected item. So your macro can check the index, then perform the appropriate actions. - Jon ------- Jon Peltier, Microsoft Excel MVP h...

SQL Not Working
What is wrong with this SQL? I am trying to use a Median function and trying to have the median calculate for each JobCode. SELECT tEmployeeMasterCopy.JobCode, (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode FROM tEmployeeMasterCopy GROUP BY tEmployeeMasterCopy.JobCode; Thanks in advance. It seems that (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) ...

Recording problems
when I record audio on onenote I have trouble hearing the playback. Is there a way to get it louder? I have turned my volume all the way up. I tried using an external mike to record but when I tried to play it back I could hear nothing at all. What can I do? Debbie P. wrote: > when I record audio on onenote I have trouble hearing the playback. > Is there a way to get it louder? I have turned my volume all the way > up. I tried using an external mike to record but when I tried to play > it back I could hear nothing at all. How do things work when recording with some o...

array function
I'm having trouble making an array. When do you press ctrl-alt-enter -- cutsygur ----------------------------------------------------------------------- cutsygurl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 View this thread: http://www.excelforum.com/showthread.php?threadid=38390 when you edit the formula, you normally exit edit mode by selecting another cell or hitting Enter. At this point, you should hit Ctrl+SHIFT+enter instead. (SHIFT, not ALT) -- Regards, Tom Ogilvy "cutsygurl" <cutsygurl.1rhxmf_1120230340.4504@excelforum-...

Functions in charts
Is is possible to use functions within charts (for example, if the chart title changes based on the value in a cell)? That way, if the chart data changes, the title also changes to reflect that. Any ideas? On Mar 15, 5:17 pm, "Matt" <MLThorn...@gmail.com> wrote: > Is is possible to use functions within charts (for example, if the > chart title changes based on the value in a cell)? That way, if the > chart data changes, the title also changes to reflect that. > Any ideas? Yes it is. Full instructions are at <a href="http:// office.microsoft.com/en-gb/e...

What is the function of the MSCREATE.DIR
What is the function of the MSCREATE.DIRs in Office Pro 97? Jack It creates directories during the installation process. "Jack B" <jslimp01nospam@earthlink.net> wrote in message news:%23XbySLS8KHA.3516@TK2MSFTNGP05.phx.gbl... > What is the function of the MSCREATE.DIRs in Office Pro 97? > > > Jack > > You need to read about it yourself here: <http://support.microsoft.com/kb/132978> If you still have any problems then please do not hesitate to come back! hth Jack B wrote: > > What is the function of ...

Separators in Cross Functional Diagrams
I created a swim diagram and added a fill. How do I keep the separators in front? I sent the band to the back, brought the separators forward, and it looks great until I close the document and reopen. Then, the separators don't show up. I changed the translucency of the fill to be able to see them, but I'd like to have them in front. ...

Excel VBA functions
I have learned how to make my own Yield function by writing the code in the VBE window. However, it is saved in my personal macro workbook and everytime I want to use it, I have to type out PERSONAL.MYield.MYield ([arguments]). Is there a way to make it so that I can just type MYield([arguments])? Bonus - Is there another, more user friendly way to make your own functions in Excel? Hi Mokey Check your personal.xls in the VBA editor Tools>References You don't have to type the filename then -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Mokey...

Webservice Function Library in Dexterity
With the new version of Great Plains nearly apon us. I think it would be useful to add functionality to Dexterity to allow developers to call/reference web services directly. In the same way as you have a COM_ libarary. Perhaps a WEBSERVICES_ library would also be useful. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader a...

Customer Statement - Footer summary
-------------------------------------------------------------------------- 7/1/2005 11:34:00 AM CDT -- Stephanie Drilling Can you asssit with a report modification issue? (8248456) We are working on the User Defined Statement format. We have national accounts Cash is applied at the parent account level We wanted a statement that provides a detail of the items by child account with subtotal, then in the report footer, a summary of child account with balance. We can get everything but the footer to do what we want. I have enclosed screen prints and the package file of the report. Any i...

TIME function used in Excel #2
I'm new to these newsgroups, so I apologize if my etiquette is not appropriate. I'm trying to help a friend whose computer was recently replaced. In his new computer, his custom macro will not run. He gets a "Microsoft Visutal Basic Compile error: Can't find project or library" with the TIME function highlighted. I first suggested using the NOW or DATE function, but he said it creates an additional series of errors. I also tried installing 'Windows Script 5.6' to no avail. Any suggestions on how to get this TIME function to work? Thank you in advance for an...

in access databse the function date() returns #name
i have created an access data base at access 2003 profisonal and used the function date() it worked properly . but when i have coped my data base at another computer at the same office , the funtion retuned (name#) aW4gQWNjZXNzOyB0aGUgRGF0ZSBmdW5jdGlvbiBkb2Vzbid0IHdvcmsgc29tZXRpbWVzLgoKaXQgd29ya3MgYWxsIHRoZSB0aW1lIGluIFNRTCBTZXJ2ZXI7IGJ1dCB5b3UndmUgZ290IHRvIGNhbGwgaXQKR0VUREFURSgpCgoKCk9uIE1hciA3LCAxMToyNMKgcG0sINmF2K3ZhdivINiq2KfYrCDYp9mE2KPYtdmB2YrYp9ihINin2YTYqNiu2YrYqiDZhdit2YXYryDYp9mE2LPZitivCjxAZGlzY3Vzc2lvbnMubWljcm9zb2Z0LmNvbT4gd3JvdGU6Cj4gaSBoYXZlIGNyZWF0ZWQgYW4gYWNjZXNzIGRhdGEgYmFzZ...

Make values stay put in established record & reset in new record?
I'm having a problem making the values on a form reset to $0.00 when I go to a new record. Some more info would help us to help you. How are you going to a new record? A button you put on the form? the new record button on the navigation buttons at bottom left of form? What does the value show instead of $0.00 when you go to a new record? Are there any other values that don't reset when you go to a new record? Jeanette Cunningham "Peg" <Peg@discussions.microsoft.com> wrote in message news:B39F82B9-43E2-4B9A-8F9B-C64B46240857@microsoft.com... > I'm having a...

Count Records
Hi Guys, How can I count records in a a combobox to allow me to scroll using the slider bar without first having to scroll to the bottom of the list? Regards John Count the records in the rowsource of the combobox: NumRecord = DCount("*","NameOfTheRowSource") PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "John" <johnlee2509@btopenworld.com> wrote in message news:0E1DBE7E-AD54-4715-A547-EC2DDE9CDE39@microsoft.com... > Hi Guys, > How can I count records in a a combobox...

Nz function in query
Hi All, the query below shows an error if any of the date in / date out entries in the table are empty. How can i impiment the Nz function to stop this. SQL from query... SELECT Employees.[First Name], Employees.[Last Name], Format([Normal start Time],"h:nn AMPM") AS ['Normal Start Time], Format([Normal End Time],"h:nn AMPM") AS ['Normal End Time], Format([scan time in],"h:nn AMPM") AS ['Scan Time In], Format([scan time Out],"h:mm AMPM") AS ['Scan Time Out], Format( [Scan Date In],"dddd dd,mm,yyyy") AS ['Scan Date ...

How to properly record attendance?
Access 2k3 I need to record when a student attends class. Date attended, time they logged in, etc. I started to set up this db years ago but the person requesting it decided they did not want it, now they do. Even though I know a hundred fold more than I did then, I still have much to learn. I remember before I posted here and I was creating a record for the student each time they logged in(which will only ever be once per day) in the tblAttendance table. I was told then that this is not the correct way for access to do this. However, for the life of me, I can not figure o...

Pressing Tab to add a new record at the end of one record in a for
Back in Access 97 and 2003, I noticed that when you reach the end of a record in a form and press tab, it used to take you to a new record. But, in Access 2007, it's not doing that. Please let me know what I need to check for to enable this function. There's a form property (Cycle) that affects how a <Tab> affects the screen. You can set it to (re-)cycle back through the fields of the record you're on, or move to the next record. Regards Jeff Boyce Microsoft Office/Access MVP "briangw" <briangw@discussions.microsoft.com> wrote in message news:329198...

if function only lets me do 8 ifs
ok here is my scenario.... i want to make a drop down menu for all of my material on an estimate sheet... if i click on one of the materials on the menu i want the 4 columns of info that are related to that product to appear where my drop down is .. ie... material, price, price with tax, price per square foot... some other people tried to help me witht his earlier but one way didnt really apply to me and the other was too advanced for me to understand so i tried this using the if function and i got something like this.. first i made a table on the same sheet with all the 4 columns lets say...

Adding records with referential integrity
I have a few tables in my database, all 1 to 1 with the master table. I have a query that gather much of the data into one large datasheet.The problem is I cannot add records through this query. I get a message that I need a related record in the child table for it to work. Am I right in assuming I need to add the record in the master database first, then the inegrity check will create the record inthe child databases, and THEN I can add data through my datasheet? A 1 to 1 relationship is unusual, as there can be only one child record for each parent record. A search for subclas...

count passed or failed
im having a problem getting the correct formula for this one. i used =IF((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000),"Passed","Failed") and =COUNTIF(DATA!$L$2:$L$5000, "name@email.com") and it gave me the desired output. but the problem is, i have to get the total number of passed and failed for each person in my team. i can't seem to find the exact formula to combine those conditions. please help me, thanks! Neri, =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)= (DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) *(DATA!$L$2:$L$...

Combo box to find records
Hi, need help with what I am sure is a simple problem. I have a form and wish to find records using a combo box. Which I know how to do. The problem is it takes me to "one" record. I would like to select, for example, employee name: John Smith and it return all records for John Smith while filtering out all other records. I would be thankful for any help. Thanks, Michael On Wed, 30 Jan 2008 20:24:04 -0800, Michaelchessking <Michaelchessking@discussions.microsoft.com> wrote: >Hi, need help with what I am sure is a simple problem. > >I have a form and wis...

Public Function to display in text box
Have public function (loaded in the global module) that produces a variable as a double. That all works fine as evidenced by the debug.print method in the immediate window. I desire to have the current value of the variable displayed in a text box. I created a callback function (loaded in global module) that looks like: Public Function GetCurrentWork_GPCI() As Double Dim CurrentWork_GPCI As Double GetCurrentWork_GPCI = CurrentWork_GPCI End Function I can't figure out how to make it display; I've currently got the control source property ...

Forms and passing data
Hi. I have a form that has a button on it that opens another form, which you can then add data about the first form...for example... A form that pulls information from the Company table the button opens A form that pulls information from the contact table *specifically* for that company The company name is NOT repeated in the second form, but it is there in an invisible field. My question is, when I want to add a new contact for the company and I use this form, it is not automatically putting in the company name. Is there a way for me to automatically pass this information on to new e...