Need to create a dynamic recordset using generic field names

  • Thread starter Thread starter k9smrt
  • Start date Start date
K

k9smrt

I have a function that I would like to reuse. It uses a recordset.

My question is how can I or is this even possible, use the rs as a generic
field?

strColumn1 = rs!Column1
strColumn2 = rs!Column2

Column1 and Column2 are not the field names.

Code:
Function ReuseFunction(strTable1 As String, strColumn1 As String, strColumn2
As String)
Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String

Set db = CurrentDb()

strSQL = "SELECT" & strColumn1 & "," & strColumn2 & "FROM" & strTable1 _
& "ORDER BY " & strColumn1 & "," & strColumn2 & "ASC"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
strColumn1 = rs!Column1
strColumn2 = rs!Column2

....
....

Set rs = Nothing
Set db = Nothing
End Function
 
k9smrt said:
I have a function that I would like to reuse. It uses a recordset.

My question is how can I or is this even possible, use the rs as a generic
field?

strColumn1 = rs!Column1
strColumn2 = rs!Column2

Column1 and Column2 are not the field names.
Click to expand...

JP has given you an example that you certainly could adapt but I need to
ask... Just what do you hope to accomplish with this code? What will it
be used for? The reason I ask is because I'm fairly sure there exists a
simpler solution that won't require dynamically assigning fields and
table at runtime.
 
It looks like the OP wants a generic function that, given any table in
a db, can be used to create a filtered recordset that consists of all
the records from two fields in that table.

--JP
 
Thanks, JP. That is exactly what I am trying to do. I can create a number
of functions for a specific task but I am trying to cut down on code. I will
try your suggetions. Sorry for getting back late.
 
Here is what I came up with.

call ReuseFunction("table1" , "table2" , "field1" , "field2")

Function ReuseFunction(strTable1 As String, strTable2 As String, strField1
As String, strField2 As String)

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Dim strColumn1 As String, strColumn2 As String

Set db = CurrentDb()

strSQL = strSQL = "SELECT * FROM " & strTable1 & " ORDER BY " & strField1 &
" , " & strField2

'OR strSQL = "SELECT " & strField1 & ", " & strField2 & " FROM " & strTable1
& " ORDER BY " & strField1 & " , " & strField2

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst

'2 ways to get the recrodset fieldname w/o hardcoding
strColumn1 = rs.Fields(0)
strColumn2 = rs.Fields(strField2).Value

....
....

Set rs = Nothing
Set db = Nothing

End Function
 
Here is what I came up with.

call ReuseFunction("table1" , "table2" , "field1" , "field2")



Function ReuseFunction(strTable1 As String, strTable2 As String, strField1
As String, strField2 As String)

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Dim strColumn1 As String, strColumn2 As String

Set db = CurrentDb()

strSQL = "SELECT * FROM " & strTable1

'OR strSQL = "SELECT " & strField1 & ", " & strField2 & " FROM " & strTable1


Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst

'2 ways to get the recrodset fieldname w/o hardcoding
strColumn1 = rs.Fields(0)
strColumn2 = rs.Fields(strField2).Value

....
....

Set rs = Nothing
Set db = Nothing

End Function

Thanks
 
Back
Top