Trying to Populate Subform with Multiple Criteria, Getting Data Type Mismatch Error

[jimmy_bones]

The non-generic version of the following code is supposed to use two pieces of criteria, one from a form textbox and one from a SQL query, to find the most recent record from a subset of records and assign it as the record source for a subform. The code uses a SQL query to create a record subset of all records that contain a value derived from a form text box, then uses a second SQL query and that same record subset to determine the record Creation Date for the most recent record, uses the Creation Date value as a filter for the original record subset, assigns the filtered record to a final record set, and sets the final record set as the record source for a sub form. The code seems to work till the last line where it assigns the final record set as the record source for the subform. THat's is what generates the data type mismatch error. Can anyone suggest a fix for this, or a simpler way to pull the record and assign it as a record source for the subform?

Dim strSQL1 as String
Dim strSQL2 as String
Dim strSQLmaxDate as String
Dim dbAllRecords as DAO.Database
Dim rsAllRecords as DA0.Recordset
Dim dbMaxDate as DAO.Database
Dim rsMAxDate as DAO.Recordset
Dim MaxDate as Date
Dim rsFiltered as DAO.Recordset

'Get subset of records where field1 meets criteria pulled from form text box
Set dbAllRecords = CurrentDb
strSQL1 = "SELECT * FROM [myDatabase] WHERE [field1] = '" & [textBox].value & "' ;"
set rsAllRecords = dbAllRecords.OpenRecordset(strSQL1, dbOpenSnapshot)

'Get max Creation Date from same record subset
set dbMaxDate = CurrentDb
strSQLmaxDate = "SELECT max([Creation Date]) as maxDate FROM [myDatabase] WHERE [field1] = '" & [textBox].value & "' ;"
set rsMaxDate = dbMaxDate.OpenRecordset(strSQLmaxDate, dpOpenSnapshot)

'Assign max date to variable
MaxDate = rsMaxDate![maxDate]

'Filter original subset of records by max Creation Date
rsAllRecords.Filter = "[Creation Date] = '" & maxDate & "'"

'Populate final recordset with the filtered record
rsFiltered = rsAllRecords.OpenRecordSet



'Assign final recordset to subform
subform.form.recordset = rsFiltered.OpenRecordset 'This line generates the data mismatch error

[John_G]

Hi -

The recordset property cannot be the recordset itself. It has to be a string containing the name of a table or query, or the SQL for a select statement, so the SQL is what you need to assemble (untested):

'
'Get max Creation Date from same record subset
' DMax is simpler than using a recordset
'
MaxDate = dMax("[Creation Date]","[myDatabase]", "[field1] = '" & [textBox].value & "' ")
'
' Create new SQL statement. Date value is delimited by #
'
strSQL1 = "SELECT * FROM [myDatabase] WHERE [field1] = '" & [textBox].value & "' AND [Creation date] = #" & Maxdate & "#"
'
' Set the recordsource for the subform, then requery
'
subform.form.recordset = strSQL1
subform.form.requery

That should be close, if I have your field names right.

John

[jimmy_bones]

It's seems to be working till the very end. After getting the max date I created the SQL string below:

strSQL1 = "SELECT * FROM [dbo_Node Opt Birth Certificate] WHERE [Node Name] = '" & [Node Name].Value & "' AND [Record Creation date] = #" & maxDate & "#"

This line

sfrmSample_Data.Form.Recordset = strSQL1

is producing the error message "Operation is not supported for this type of object." I tried it like this and got the same results:

sfrmSample_Data.Form.Recordset = dbAllRecords.OpenRecordSet(strSQL1,dbOpenSnapshot)

[John_G]

Hi -

Oops!! My mistake -

The line should read : sfrmSample_Data.Form.Recordsource = strSQL1

I gave you the wrong property name - my apologies.

John

[jimmy_bones]

Okay, I'm no longer getting the error message but I thought assigning a record source to the subform would automatically populate it. Is there another step to populate the various text boxes that correspond to the fields in the SQL query?