In a DAO recordset, I want to refer to a field without using its name directly (for various reasons!), rather using a (string) variable which has previously been set. How can I get this to work?
(where rst is recordset etc)
rst!Amount 'works fine because that is the fieldname but
dim DonationValue
DonationValue="Amount"
rst!DonationValue 'returns Item not found in this collection
Thanks Chris & Modest but both of your solutions are not working! I enclose a cut down demo. All is OK except for that line I mentioned. If I use the alternative line, its fine!
Code:
Dim rst2 As DAO.Recordset
Dim txtView As String
txtView = ProfileGetItem("vars", "View_name", "none", "c:\temp\dg\tempv.tmp")
Set rst2 = dbs.OpenRecordset(txtView)
rst2.AddNew
Dim Datestamp
Datestamp = ProfileGetItem("FieldNames", "datestamp", "", appPath & "config\stanord.ini")
'This doesn't work although in the immediate window ?datestamp returns datew
rst2!Fields(Datestamp).Value = CDbl(Now)
'This one does work- the fieldname is datew
rst2!datew = CDbl(Now)
rst2.Update
You're still referencing it "rst!Fields()" --- I believe it's looking for the field name to be "Fields()" ... don't use the bang operator when calling a function
Thanks Modest
Firstly for presenting my code correctly!
Secondly for pointing out my error
I know there are countless threads on the forum re difference between bang and dot but I always thought you're always OK with bang , and dot sometimes works but I see I am wrong! I thought bang emphasizes things more. So what in a nutshell is the main criteria between them?
Thanks again
Yes it can but under some circumstances defeats the purpose of the With structure.
In the following examples both cases do the same job: -
Case 1
Code:
Public Sub InitLanguageCase1(ByRef actobj As Object)
Dim strLanguage As String
Dim rs As DAO.Recordset
Dim db As DAO.Recordset
strLanguage = DLookup("Language", "tblConfig")
Set db = CurrentDb()
Set rs = db.OpenRecordset(" SELECT *" & _
" FROM tblLanguage" & _
" WHERE [Object] = '" & actobj.Name & "'")
Do Until rs.EOF
If rs!Control = "Object" Then
actobj.Caption = rs(strLanguage)
Else
actobj(rs!Control).Caption = rs(strLanguage)
If actobj(rs!Control).Tag = "Resize" Then
actobj(rs!Control).Width = Len(rs(strLanguage)) * conPixelsPerChar
End If
End If
rs.Movenext
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
And Case 2
Code:
Public Sub InitLanguageCase2(ByRef actobj As Object)
Dim strLanguage As String
strLanguage = DLookup("Language", "tblConfig")
With CurrentDb.OpenRecordset(" SELECT *" & _
" FROM tblLanguage" & _
" WHERE [Object] = '" & actobj.Name & "'")
Do Until .EOF
If !Control = "Object" Then
actobj.Caption = .Fields(strLanguage)
Else
actobj(!Control).Caption = .Fields(strLanguage)
If actobj(!Control).Tag = "Resize" Then
actobj(!Control).Width = Len(.Fields(strLanguage)) * conPixelsPerChar
End If
End If
.Movenext
Loop
.Close
End With
End Sub
Note: I just grabbed this out of something that was already written so please don’t expect it to compile.
The major difference between Case1 and Case2 is that Case2 does not require a reference to ADO or DAO and is therefore more portable across Access versions. ADO and/or DAO may be referenced but it still doesn’t matter because no references are made to them…the code in Case2 simply doesn’t care what’s referenced.
The second difference is that Case2 does not set anything to something and therefore does not require setting anything to Nothing, but the recordset is still closed.
The third difference is that Case2, by legitimately dropping some of the code, preserves valuable vertical screen workspace which, in turn, helps to prevent ‘page flipping’ and screen scrolling.
And everything else that I forgot to mention.
I know it will not work under all circumstances and I also know that very few people, if any, have tried it but it works sometimes and that’s enough for me to experiment.
Hope that helps.
ETA
Changed the word legitimacy to legitimately and bolded the change.
Chris, I don't think your cases are correct. You're not declaring the type, but I think Access goes to the default ADO/DAO that your Access version is set to.
For instance:
Dim rs As Recordset
Even though you don't declare the DAO or ADO, rs would still be one of them. Which one depends on if you have Access 97, 2000, XP, or 2003. The reason we don't do this is because sometimes people use ADO and DAO objects/types in the same sub/function, because they both use the same keywords Access would get confused -- there would be errors.
Case 2 works because no variables are declared As Database or As Recordset.
Ever since the introduction of A2K we have been aware that both Database and Recordset need to be disambiguated because the default shifted from DAO to ADO.
But if we don’t declare variables as Database or Recordset then the references to DAO or ADO are ignored. They may be present or they may be removed entirely but the line of code: -
With CurrentDb.OpenRecordset(Blah, Blah)
still works as a recordset, with or without references. So by dropping the creation of database and recordset variables and cutting straight to the chase of With CurrentDb, we make the code independent of declared references in Tools/References.
I have attached a small demo which is in A97 and without references. It readily converts to A2K and even in A2K you can go to Tools/References and remove all the references. Please also note after conversion to A2K that neither DAO or ADO appear as references. And the same sort of thing happens all the way to A2K3…it still works.
So imagine that we have a code library that needed to support all 32bit versions of Access. Which reference would we prefer to use ADO, DAO or none?
Now I know it doesn’t work with all the ways that we can use CurrentDb, but it does with recordsets.
It may seem a bit strange at first, largely because I doubt if With CurrentDb has been used that way before, but once we get the hang of it conversion between different versions of Access becomes much easier.
Chris, I'm still awaiting what someone else has to say on this... but I think you may be correct.
The reason I said what I did is because OpenRecordset() is a function. In this case, it returns a value. I was thinking the value it returned was a Recordset. Because I thought this, that would mean that Access would have to choose which type of recordset it was.
However, after looking further into what you had to say, now I don't think it returns a recordset. When not declaring the variable type, OpenRecordset() seems to return a "Variant/Object/Fields" type value... in this case "Object" and "Fields" is the keyword. And the important thing to note is that "Recordset" was not included.
Have a look at this…open MdlCompactFunctions and Function AutoCompact.
Go inside the With statement and type a dot. Have a look down the Intellisense drop-down list. That looks very much like a DAO recordset to me, even when no references are declared.
So it seems to me that CurrentDb.OpenRecordset returns a pointer to a DAO recordset in all versions of Access with or without references in Tools.
I don’t know what other people may think about this because I’m the only one I know that uses it. But it has been well tested in all versions and it works.
This is how Access libraries are selected by default:
-Prior to 2000: DAO object library was selected by default when a new db was created
-Access 2000: ADO object library was selected by default
-Access 2002: ADO object library still selected by default
-Access 2003: Both libraries are preselected
I'm not sure how the application handles the process when none are selected (an error should come up), or maybe it goes back to the default even though there is no selection.
I wish I could look at your zip file, but can't at work because I don't have a decompressor because of security restrictions.
