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]

Expanding code to include dynamic array

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?

[ChrisD76]

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

[ADezii]

What are the Names of the Fields and their Data Types in the Table CSR_Content?

[ChrisD76]

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)

[ADezii]

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.

Public Function ReadCourseContent(strCourseCode As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstSubForm 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
 If Not .BOF And Not .EOF Then
  'DELETE existing Records in Sub-Form Record Souce, only if Records
  'exist in rst(Records for passed CourseCode, namely strCourseCode)
  CurrentDb.Execute "DELETE * From CSR_Content", dbFailOnError
 End If
  Do While Not .EOF
      Set rstSubForm = CurrentDb.OpenRecordset("CSR_Content", dbOpenDynaset)
        rstSubForm.AddNew
          rstSubForm![UnitNr] = ![UnitNr]
          rstSubForm![CSR_ID] = ![Unit]
          rstSubForm![Content] = ![Content]
          rstSubForm![CourseCode] = strCourseCode
        rstSubForm.Update
          rst.MoveNext
  Loop
    If Not rstSubForm Is Nothing Then
      rstSubForm.Close
      Set rstSubForm = Nothing
    End If
End With

'Must Requery the Sub-Form
Forms!frmTest!CSR_Content.Requery

rst.Close

Set rst = Nothing
Set db = Nothing
End Function