By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,726 Members | 842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,726 IT Pros & Developers. It's quick & easy.

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

P: 26
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
  4. rsContacts.ActiveConnection = CurrentProject.Connection
  5. rsContacts.Open "QRY12MoReviewNewMIPNo"
  7. 'For each record in the TblContacts table, send an email
  8. Do While Not rsContacts.EOF
  10. 'Send data for this active record to Report
  11. DoCmd.OpenReport "RPTPAN", acViewPreview, , "[EEFullName] = " & EEFullName
  13. 'output report to .pdf format and save as PAN.pdf.
  14. '......
  16. DoCmd.Close acReport, "RPTPAN", acSaveNo
Apr 16 '09 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 2,545
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!

Apr 16 '09 #2

Post your reply

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