How to pass a variable to FindFirst in DAO Recordset?

[ChrisD76]

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:

Function ReadCourseContent()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
 
     Set db = CurrentDb()
     Set rst = CurrentDb.OpenRecordset("CourseContent", dbOpenSnapshot)
 
     rst.FindFirst "[CourseCode] = 'BW310_74'"
 
     Do While Not rst.NoMatch
          Debug.Print rst!UnitNr, rst!Unit, rst!Content
          rst.FindNext "[CourseCode] = 'BW310_74'"
     Loop
 
 
     rst.Close
 
     Set rst = Nothing
     db.Close
     Set db = Nothing
 
     SendKeys "^g"
 
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 ReadCourseConte nt(strCourseCod e) 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:

Function ReadCourseContent(strCourseCode)
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
 
     Set db = CurrentDb()
     Set rst = CurrentDb.OpenRecordset("CourseContent", dbOpenSnapshot)
 
     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=E mpty.

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

Cheers

[ADezii]

1. Function Definition:
   

   Public Function ReadCourseContent(strCourseCode As String)
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
    
   strSQL = "Select * From CourseContent Where [CourseCode] = '" & strCourseCode & "';"
    
   Set db = CurrentDb()
   Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
   With rst
     Do While Not .EOF
       Debug.Print !UnitNr, !Unit, !Content
         rst.MoveNext
     Loop
   End With
    
   rst.Close
    
   Set rst = Nothing
   Set db = Nothing
    
   DoCmd.RunCommand acCmdDebugWindow
   End Function

2. Sample Usage:
   

   Call ReadCourseContent("BW310_74")

[ChrisD76]

Absolutely marvellous - thanks!!!

[ChrisD76]

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!

[ADezii]

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.

Public Function ReadCourseContent(strCourseCode As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim cbo As ComboBox
 
strSQL = "Select * From CourseContent Where [CourseCode] = '" & strCourseCode & "';"
 
Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
Set cbo = Forms!frmTest!cboCourseInfo
 
'Define the characteristics of the Combo Box
cbo.RowSourceType = "Value List"    'Critical
cbo.ColumnCount = 3
cbo.BoundColumn = 1     'Bind the 1st Column
cbo.ColumnWidths = "0 in;1 in;1 in"     'Hide the 1st Column
 
With rst
  Do While Not .EOF
    cbo.AddItem !UnitNr & "," & !Unit & "," & !Content
      rst.MoveNext
  Loop
End With
 
rst.Close
 
Set rst = Nothing
Set db = Nothing
End Function

[NeoPa]

Remember ComboBoxes don't support multiple selections. For that you would need a properly specified ListBox.

[ChrisD76]

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...

[ADezii]

How is this SubForm related to the Main Form?

[ChrisD76]

Apologies for the delay - a couple of manic weeks at work... The subform was simply created separately and dragged on to the main form...

[ADezii]

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?