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

How to pass a variable to FindFirst in DAO Recordset?

P: 7
Hi,

I am new to using VBA, and have been working with DAO Recordsets. I'm working on a little problem, and think that DAO Recordsets are the solution. I've been playing around with them to try and get it working, and have the following code, which works perfectly:
Expand|Select|Wrap|Line Numbers
  1. Function ReadCourseContent()
  2.      Dim db As DAO.Database
  3.      Dim rst As DAO.Recordset
  4.  
  5.      Set db = CurrentDb()
  6.      Set rst = CurrentDb.OpenRecordset("CourseContent", dbOpenSnapshot)
  7.  
  8.      rst.FindFirst "[CourseCode] = 'BW310_74'"
  9.  
  10.      Do While Not rst.NoMatch
  11.           Debug.Print rst!UnitNr, rst!Unit, rst!Content
  12.           rst.FindNext "[CourseCode] = 'BW310_74'"
  13.      Loop
  14.  
  15.  
  16.      rst.Close
  17.  
  18.      Set rst = Nothing
  19.      db.Close
  20.      Set db = Nothing
  21.  
  22.      SendKeys "^g"
  23.  
  24. End Function
What I want to do now, is to pass in a variable value, rather than coding the CourseCode. I thought (in my ignorance) that declaring something like Function ReadCourseContent(strCourseCode) would allow me to pass a course code into the function, and that I could use this in place of the string 'BW310_74' - something like this:
Expand|Select|Wrap|Line Numbers
  1. Function ReadCourseContent(strCourseCode)
  2.      Dim db As DAO.Database
  3.      Dim rst As DAO.Recordset
  4.  
  5.      Set db = CurrentDb()
  6.      Set rst = CurrentDb.OpenRecordset("CourseContent", dbOpenSnapshot)
  7.  
  8.      rst.FindFirst "[CourseCode] = " & strCourseCode
However, this fails, telling me I have a missing operator in the expression, and in VBE, the last line of code above is highlighted yellow, telling me strCourseCode=Empty.

Is there a way to pass a variable into the FindFirst and FindNext methods of the DAO Recordset?

Cheers
Jan 7 '10 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,597
  1. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function ReadCourseContent(strCourseCode As String)
    2. Dim db As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim strSQL As String
    5.  
    6. strSQL = "Select * From CourseContent Where [CourseCode] = '" & strCourseCode & "';"
    7.  
    8. Set db = CurrentDb()
    9. Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    10.  
    11. With rst
    12.   Do While Not .EOF
    13.     Debug.Print !UnitNr, !Unit, !Content
    14.       rst.MoveNext
    15.   Loop
    16. End With
    17.  
    18. rst.Close
    19.  
    20. Set rst = Nothing
    21. Set db = Nothing
    22.  
    23. DoCmd.RunCommand acCmdDebugWindow
    24. End Function
  2. Sample Usage:
    Expand|Select|Wrap|Line Numbers
    1. Call ReadCourseContent("BW310_74")
Jan 7 '10 #2

P: 7
Absolutely marvellous - thanks!!!
Jan 7 '10 #3

P: 7
Thanks again for your assistance on this. I have now extended the code to attempt the next step, which is to write the content of the recordset into an array, and have run into a problem. I have now got two questions - should I be using an array at all? If so, what have I done wrong?

My intention here is to have a form with a multiselection combo box. Based on the CourseCode (or CourseCodes) selected, I want Access to read the CourseContent table, and write the list of course content into a form. The form will include a tick box next to each unit of the course, allowing the user to select which unit they want to include, and then this will to output to a report.

The code I posted originally, and which is now corrected, allows for a single CourseCode to be read into a recordset. What I want to do now is to prepare this data to be written to a form - can this be done straight from the recordset, or do I need to write the data into an array first? Eventually I need to go back and adjust the code to accept multiple CourseCodes.

This seems really straight forward in my head, but translating it into VBA has been challenging - I guess I am missing something basic. Before this week I had never used VBA, so please forgive my ignorance.

So my question then is should I be using an array at this point, or can I write directly to a form from the recordset? If I should use an array, then I will post the code for opinion on what I'm doing wrong...

Many thanks!
Jan 7 '10 #4

ADezii
Expert 5K+
P: 8,597
The values can be derived directly from the Recordset. The following code will populate the cboCourseInfo Combo Box on frmTest (must be Open) directly from the Do...Loop navigating the Recordset. Any questions, feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. Public Function ReadCourseContent(strCourseCode As String)
  2. Dim db As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim strSQL As String
  5. Dim cbo As ComboBox
  6.  
  7. strSQL = "Select * From CourseContent Where [CourseCode] = '" & strCourseCode & "';"
  8.  
  9. Set db = CurrentDb()
  10. Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
  11.  
  12. Set cbo = Forms!frmTest!cboCourseInfo
  13.  
  14. 'Define the characteristics of the Combo Box
  15. cbo.RowSourceType = "Value List"    'Critical
  16. cbo.ColumnCount = 3
  17. cbo.BoundColumn = 1     'Bind the 1st Column
  18. cbo.ColumnWidths = "0 in;1 in;1 in"     'Hide the 1st Column
  19.  
  20. With rst
  21.   Do While Not .EOF
  22.     cbo.AddItem !UnitNr & "," & !Unit & "," & !Content
  23.       rst.MoveNext
  24.   Loop
  25. End With
  26.  
  27. rst.Close
  28.  
  29. Set rst = Nothing
  30. Set db = Nothing
  31. End Function
Jan 7 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
Remember ComboBoxes don't support multiple selections. For that you would need a properly specified ListBox.
Jan 7 '10 #6

P: 7
Thanks guys for your help. I was actually hoping to write the recordset to a form, something like the attached. I cannot figure out how to get the recordset to populate the subform. Once this data is in the subform, the idea is that the user can simply tick the box next to the units they want to include and then save this data into a separate table, and at the same time generate a report to be printed. And as I mentioned previously, I need to be able to include multiple units from multiple course codes...
Attached Images
File Type: jpg CSR_Form.jpg (9.0 KB, 488 views)
Jan 8 '10 #7

ADezii
Expert 5K+
P: 8,597
How is this SubForm related to the Main Form?
Jan 8 '10 #8

P: 7
Apologies for the delay - a couple of manic weeks at work... The subform was simply created separately and dragged on to the main form...
Jan 26 '10 #9

ADezii
Expert 5K+
P: 8,597
What is?
  1. The Name of the Main Form?
  2. The Name of the Sub-Form?
  3. The Record Source of the Sub-Form?
  4. You would not be populating the Sub-Form itself, but the Record Source of the Sub-Form, then Requery it.
  5. The Sub-Form is totally independent from the Main Form, correct?
Jan 26 '10 #10

P: 7
The Name of the Main Form? NewCSRForm
The Name of the Sub-Form? CSRContent
The Record Source of the Sub-Form? Table CSR_Content
You would not be populating the Sub-Form itself, but the Record Source of the Sub-Form, then Requery it. Interesting - so I have a table which includes course content, which is where my recordset reads it's data from. So then my recordset needs to be written to the underlying table CSR_Content, and then the Query can read the data into the form?? So then the CSR_Content table (which includes the Include flag) would need to be written to again from the subform (I guess this is reasonably straight forward as it is then simply updating the existing records...), once the user chooses which content to be included...
The Sub-Form is totally independent from the Main Form, correct? Yes
Jan 26 '10 #11

ADezii
Expert 5K+
P: 8,597
What are the Names of the Fields and their Data Types in the Table CSR_Content?
Jan 26 '10 #12

P: 7
The structure of CSR_Content is almost the same as the structure of CourseContent. The main difference is that I have left out the text field Content from CSR_COntent - but given the approach above, i guess this should be included now? Unless of course the query used to populatte teh subform reads data in from both CSR_Content and CourseContent I guess...

To be specific though, CSR_Content has the following fields:
CSR_ID (Number)
UID (Autonumber)
CourseCode (Text)
UnitNr (Number)
Include (Yes/No)
Jan 27 '10 #13

ADezii
Expert 5K+
P: 8,597
The following code will populate the Record Source of the Sub-Form based on the specific Course Code Value passed to the ReadContents() Function. Rather than going into a detailed explanation, I'll simply post the code, and if you have any questions, please feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. Public Function ReadCourseContent(strCourseCode As String)
  2. Dim db As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim rstSubForm As DAO.Recordset
  5. Dim strSQL As String
  6.  
  7. strSQL = "Select * From CourseContent Where [CourseCode] = '" & strCourseCode & "';"
  8.  
  9. Set db = CurrentDb()
  10. Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
  11.  
  12. With rst
  13.  If Not .BOF And Not .EOF Then
  14.   'DELETE existing Records in Sub-Form Record Souce, only if Records
  15.   'exist in rst(Records for passed CourseCode, namely strCourseCode)
  16.   CurrentDb.Execute "DELETE * From CSR_Content", dbFailOnError
  17.  End If
  18.   Do While Not .EOF
  19.       Set rstSubForm = CurrentDb.OpenRecordset("CSR_Content", dbOpenDynaset)
  20.         rstSubForm.AddNew
  21.           rstSubForm![UnitNr] = ![UnitNr]
  22.           rstSubForm![CSR_ID] = ![Unit]
  23.           rstSubForm![Content] = ![Content]
  24.           rstSubForm![CourseCode] = strCourseCode
  25.         rstSubForm.Update
  26.           rst.MoveNext
  27.   Loop
  28.     If Not rstSubForm Is Nothing Then
  29.       rstSubForm.Close
  30.       Set rstSubForm = Nothing
  31.     End If
  32. End With
  33.  
  34. 'Must Requery the Sub-Form
  35. Forms!frmTest!CSR_Content.Requery
  36.  
  37. rst.Close
  38.  
  39. Set rst = Nothing
  40. Set db = Nothing
  41. End Function
Jan 27 '10 #14

Post your reply

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