Need to create a dynamic recordset using generic field names

[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

 

[JP]

I think you need to create the recordset based on the table first,
then iterate through the Fields collection to get the field names. For
example, see "Delete Blank Records from All Tables" at
http://www.codeforexcelandoutlook.com/blog/2009/06/clean-and-repair-your-access-database/
for a method you can adapt to loop through a recordset and build a
list of field names regardless of the table.

--JP

 

[Banana]

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.

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.

 

[JP]

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

 

[k9smrt]

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.

 

[JP]

Not a problem. Do post back if you figure out a solution.

--JP

 

[nwauser]

The following worked for me. Your strSQL needed some adjustment.

Public Function ReuseFunction(strTable1 As String, strColumn1 As String,
strColumn2 As String)
Dim strSQL As String
Dim i As Integer
Dim db As Database
Dim rs As Recordset

strSQL = "SELECT [" & strColumn1 & "],[" & strColumn2 & "] FROM " & strTable1
_
& " ORDER BY [" & strColumn1 & "],[" & strColumn2 & "] ASC"

Debug.Print strSQL

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount

rs.Close


End Function

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.

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

[quoted text clipped - 17 lines]

 

[k9smrt]

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

 

[k9smrt]

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