473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

how do I filter by the current record in the recordset (when no Form is involved)

Hi gang,

I'm stumped on something that I've been at for a few days and so am seeking help.

I'm sending emails to managers to let them know that a performace review is due and attach a custom form for the employee referenced. I have the query working and have created the recordset. The emails are generating correctly from the recordset but I can't get the code to send the active record info over to populate a report for just the one person referenced. I keep getting the entire recordset sent over.

I've seen this problem on the boards elsewhere but I have a new twist on it as I am not driving the request from a button on a form. I'm doing the whole thing programatically.

That being said, how do I filter [EEFullName] by the current record in the recordset (when no Form is involved)?


The relevant parts of the code are below.

Many thanks!


Expand|Select|Wrap|Line Numbers
  1. 'Dim strLtrContent As String
  2. Dim rsContacts As New ADODB.Recordset
  3.  
  4. rsContacts.ActiveConnection = CurrentProject.Connection
  5. rsContacts.Open "QRY12MoReviewNewMIPNo"
  6.  
  7. 'For each record in the TblContacts table, send an email
  8. Do While Not rsContacts.EOF
  9.  
  10. 'Send data for this active record to Report
  11. DoCmd.OpenReport "RPTPAN", acViewPreview, , "[EEFullName] = " & EEFullName
  12.  
  13. 'output report to .pdf format and save as PAN.pdf.
  14. '......
  15.  
  16. DoCmd.Close acReport, "RPTPAN", acSaveNo
  17.  
Apr 16 '09 #1
1 2593
Stewart Ross
2,545 Expert Mod 2GB
Hi. You need to refer to the value of the name field from the current record in your recordset within the WHERE clause of the OpenReport method.

When referring to string literals like this in a WHERE clause you must put the value inside single quotes, which you are not doing in the example you have provided. You don't need single quotes if the value you are comparing is of numeric type (integer or floating point), but you do if the value is a string (as in a person's name).

You may have left out the code line that sets what you show as variable EEFullName - or you may not have set it at all. In any case, for brevity below I replace this with a variable called strName. I assume that the name field in your rsContacts recordset is indeed EEFullName. If it is not you must substitute the name of the correct field in the extract below.

Expand|Select|Wrap|Line Numbers
  1. Dim strName as String ' the DIM is outside of the loop
  2. strName = rsContacts!EEFullName ' this line and the next are inside the loop - replacing line 11 above
  3. DoCmd.OpenReport "RPTPAN", acViewPreview, , "[EEFullName] = '" & strName & "'"
You don't show the rsContacts.MoveNext line and the remaining parts of the loop for processing your recordset, but I assume that they are actually there.

Whilst I am all for being selective about what you post, I think you may have left out key parts of your code which could have helped me and others to understand what is wrong - and guessing blindly is not what I do best!

-Stewart
Apr 16 '09 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: dick | last post by:
I am just trying to print/report the results of a "filter by selection" which is done by right-clicking a form, filling in values, and "applying the filter." I have searched the newsgroups, and...
2
by: Salad | last post by:
I have a log file with a list of records. The log file can be unfiltered or filtered. I have a command button to call a data entry form from the log. At first I was only going to present the...
8
by: Zlatko Matiæ | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
2
by: cefrancke | last post by:
I have a form (no underlying record set) that has two separate sub-forms on it. Each sub-form has data from two different tables. Above each sub-form there is one unbound combo box with a SQL...
2
by: allyn44 | last post by:
Hello, I have built a serch form for users to edit records. I only want them to pull up the record they need, and I want to check for nulls. There should not be dupes becasue the underlying...
2
by: bobdydd | last post by:
Hi All Access 2007 I have a form with a ListBox called "ListContactTasks" DoubleClicking the ListBox opens another form called "frmContact" at a particular record corresponding to the one in...
3
by: MLH | last post by:
I've pretty much always applied a filter to a form to go to a specific record - filtering out all but the desired record. Am looking for code used to move to a specific record when keyfield value...
3
by: birt | last post by:
I know I am getting in my own way .... so someone please save me from myself! I created a form and rather than force the users to scroll through all the records to find theirs I decided to have...
3
by: franc sutherland | last post by:
Hello, I have a report which I filter using the me.filter command in the OnOpen event. Me.Filter = "OrderID=" & Forms!variable_form_name! Me.FilterOn = True I want to be able to open that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.