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

Adding selected records from a populated recordset to an empty recordset

P: 31
Hi,

I have a recordset which I have populated with a query.
I have an empty recordset.

I would like to take value of a field in every single record, which is a string.
Search if the 4rth letter of the string is an 'a', and if yes add that record to the new recordset. Finally i want to output the resultset onto a report.

I need some help please.
Nov 1 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,666
Hi,

I have a recordset which I have populated with a query.
I have an empty recordset.

I would like to take value of a field in every single record, which is a string.
Search if the 4rth letter of the string is an 'a', and if yes add that record to the new recordset. Finally i want to output the resultset onto a report.

I need some help please.
The Logic is listed below, I based it on a [LastName] Field in a Table named tblEmployees:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, rstNew As DAO.Recordset
  2. Dim strSQL As String
  3.  
  4. strSQL = "Select [LastName] From tblEmployees Where Mid$([LastName], 4, 1) = 'a' Order By [LastName]"
  5.  
  6. Set MyDB = CurrentDb()
  7. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  8.  
  9. 'For testing purposes - works fine!
  10. Do While Not MyRS.EOF
  11.     Debug.Print MyRS![LastName]
  12.   MyRS.MoveNext
  13. Loop
  14.  
  15. Set Reports("Your Report Name Here").Recordset = MyRS
  16.  
  17. 'In reality, you would probably not want to Close the Recordset used for the Report
  18. 'prior to Opening it and you would also want to Declare MyRS at a Modular level
  19. 'MyRS.Close
  20. 'Set MyRS = Nothing
Nov 2 '07 #2

P: 31
Thank you very much. It worked!
Nov 2 '07 #3

ADezii
Expert 5K+
P: 8,666
Thank you very much. It worked!
Glad it all worked out for you.
Nov 2 '07 #4

Post your reply

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