reference to a fieldname fails because of quotes

[Happy YN]

I have a recordset and I need to refer to a field using a variable e.g instead of rst.Address I have rst(variable). However the variable is being taken out of an array as a string so it looks like "address". The code therefore fails looking for rst("address"). How can I adjust this reference so it will be correct.
Similarly when I try to assign a value to a field e.g rst(datestamp)= date() - again this value is coming as a string so the code reads it as "date()". The quotes are not part of a string that I can strip off using right or left function, they just denote a string.
In short how can I convert a string value into a variable?
There must be something simple I'm missing!
Thanks

 

[ChrisO]

Either of these two methods works OK for me: -

Option Explicit
Option Compare Text

[color=green]'   Needs a reference to DAO.[/color]
Sub Test1()
    Dim strVariable As String
    Dim strField(5) As String
    Dim dbs         As DAO.Database
    Dim rst         As DAO.Recordset
    
    strField(0) = "ContactName"
    strVariable = strField(0)
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tblCustomers", dbOpenDynaset)
    
    Do Until rst.EOF
        MsgBox rst(strVariable)
        rst.MoveNext
    Loop

    Set rst = Nothing
    Set dbs = Nothing

End Sub


[color=green]'   No need for a DAO reference.[/color]
Public Function Test2() As Long
    Dim strVariable As String
    Dim strField(5) As String
    
    strField(0) = "ContactName"
    strVariable = strField(0)

    With CurrentDb.OpenRecordset("tblCustomers", 2)
        Do Until .EOF
            MsgBox .Fields(strVariable)
            .MoveNext
        Loop
        .Close
    End With
    
End Function

If that doesn’t get it sorted can you post a small example of the code you are using?

Regards,
Chris.

 

[Happy YN]

Thanks for reply but sadly no luck (I don't really understand why you dim as (5) and then call it as (0) but it makes no difference.)
I am posting part of my code - see below it for more explanation:

Dim originalValue As String
Dim replaceValue As String
originalValue = ProfileGetItem("exceptions", "originalvalue", "", appPath & "config\stanord.ini")
replaceValue = ProfileGetItem("exceptions", "replacevalue", "", appPath & "config\stanord.ini")

Dim varOriginal As Variant
Dim varReplace As Variant

Dim k As Integer
varOriginal = Split(originalValue, ",") 'Split the string into an array
varReplace = Split(replaceValue, ",") 'Split the string into an array

For k = 0 To UBound(varOriginal) 'Parse through the array

rst2(varOriginal(k)) = varReplace(k)

Next k

The value of originalvalue is a string :-
datew,ddate,amount,mpay

and the value of replacevalue is:-
cdbl(now),(Format(aDates(j))),me.txtinstalment,"standing order"


I want the lines to read the code as
rst2(datew)=cdbl(now) 'those are the first values of both arrays mentioned

but instead it reads

rst2("datew")="cdbl(now)"

It made no difference assigning these strings to other variables , the quotes are always there at the end!

I hope I have clarified myself

Please also explain what he meaning of the (5) is at the dim statement and how it is used later with (0)

Thanks

 

[ChrisO]

The array thing…

From your original post I needed to create an array to test it.
Dim strField(5) As String ( The array has 6 elements, 0-5)

Then I needed to assign data to at least one element of that array.
strField(0) = "ContactName" (I used the first element, 0)

Then to retrieve it.
strVariable = strField(0)

And it worked.


I’m not familiar with the Function ProfileGetItem().
originalValue = ProfileGetItem("exceptions", "originalvalue", "", appPath & "config\stanord.ini")
Is this an Access function? If not what does it do?

But you say…
The value of originalvalue is a string :-
datew,ddate,amount,mpay

However you are not passing the value of datew, ddate, amount or mpay you are passing the string literal "originalvalue".

To almost confirm this you also say…
It made no difference assigning these strings to other variables, the quotes are always there at the end!

So I would need to see the function ProfileGetItem().

Hope that helps.

Regards,
Chris.

 

[Happy YN]

Thanks for explaining the (5) & (0) parts
here is the function

Public Declare Function GetPrivateProfileString _
Lib "kernel32" Alias "GetPrivateProfileStringA" _
(ByVal lpSectionName As String, _
ByVal lpKeyName As Any, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String) As Long

Public Declare Function WritePrivateProfileString _
Lib "kernel32" Alias "WritePrivateProfileStringA" _
(ByVal lpSectionName As String, _
ByVal lpKeyName As Any, _
ByVal lpString As Any, _
ByVal lpFileName As String) As Long

Public Function ProfileGetItem(lpSectionName As String, _
lpKeyName As String, _
defaultValue As String, _
inifile As String) As String

'Retrieves a value from an ini file corresponding
'to the section and key name passed.

Dim success As Long
Dim nSize As Long
Dim ret As String

'call the API with the parameters passed.
'The return value is the length of the string
'in ret, including the terminating null. If a
'default value was passed, and the section or
'key name are not in the file, that value is
'returned. If no default value was passed (""),
'then success will = 0 if not found.

'Pad a string large enough to hold the data.
ret = Space$(2048)
nSize = Len(ret)
success = GetPrivateProfileString(lpSectionName, _
lpKeyName, _
defaultValue, _
ret, _
nSize, _
inifile)

If success Then
ProfileGetItem = Left$(ret, success)
End If

End Function


Public Sub ProfileDeleteItem(lpSectionName As String, _
lpKeyName As String, _
inifile As String)

'this call will remove the keyname and its
'corresponding value from the section specified
'in lpSectionName. This is accomplished by passing
'vbNullString as the lpValue parameter. For example,
'assuming that an ini file had:
' [Colours]
' Colour1=Red
' Colour2=Blue
' Colour3=Green
'
'and this sub was called passing "Colour2"
'as lpKeyName, the resulting ini file
'would contain:
' [Colours]
' Colour1=Red
' Colour3=Green

Call WritePrivateProfileString(lpSectionName, _
lpKeyName, _
vbNullString, _
inifile)

End Sub

The relevant part of the ini file is

[Exceptions]
'the value of these OriginalValue will be substituted for the ReplaceValue. commas must be used


OriginalValue=datew,ddate,amount,mpay
ReplaceValue=cdbl(now),(Format(aDates(j))),me.txtinstalment,"standing order"

'ends

I am grateful for your help It sure is handy Australia being awake while I sweat thru the night!

 

[ChrisO]

Well don’t wait up because I’ll have to go and get the API function calls GetPrivateProfileString() and WritePrivateProfileString().

You know, in cases like this it might have been easier to post the entire database.

 

[ChrisO]

No offence intended but if you don’t understand arrays then please don’t take on API calls.

This was reduced to the minimum for testing.
Start small and build on success.
Read the API documentation and the comments within the API demos.
Use a naming convention and format your code.

Above all else, don’t simply copy and paste code and expect it to work.

Option Explicit
Option Compare Text

[color=green]'   KPD-Team 1999
'   URL: http://www.allapi.net/
'   E-Mail: KPDTeam@Allapi.net[/color]
Declare Function GetPrivateProfileString Lib "kernel32" _
          Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
                                            ByVal lpKeyName As Any, _
                                            ByVal lpDefault As String, _
                                            ByVal lpReturnedString As String, _
                                            ByVal nSize As Long, ByVal lpFileName As String) As Long
                                            

Sub TestIt()
    Dim strAppPath       As String
    Dim strOriginalValue As String
    
    strAppPath = "c:\winnt\"  [color=green]' Change to "c:\windows\" if required.[/color]
    
    strOriginalValue = ProfileGetItem("Exceptions", "OriginalValue", "", strAppPath & "\config\stanord.ini")
   
    MsgBox strOriginalValue   [color=green]' ZLS is a failure. (In my test case it returned Fred, no quotes)[/color]

End Sub


Public Function ProfileGetItem(lpSectionName As String, _
                               lpKeyName As String, _
                               defaultValue As String, _
                               inifile As String) As String

    'Retrieves a value from an ini file corresponding
    'to the section and key name passed.
    
    Dim success As Long
    Dim nSize As Long
    Dim ret As String
    
    [color=green]'call the API with the parameters passed.
    'The return value is the length of the string
    'in ret, including the terminating null. If a
    'default value was passed, and the section or
    'key name are not in the file, that value is
    'returned. If no default value was passed (""),
    'then success will = 0 if not found.
    
    'Pad a string large enough to hold the data.[/color]
    ret = Space$(2048)
    nSize = Len(ret)
    success = GetPrivateProfileString(lpSectionName, _
                                      lpKeyName, _
                                      defaultValue, _
                                      ret, _
                                      nSize, _
                                      inifile)
    
    MsgBox success [color=green]' Zero is a failure. (In my test case it returned 4)[/color]
    
    If (success) Then
        ProfileGetItem = Left$(ret, success)
    End If

End Function

That’s about the smallest I could cut it down to simply test the API call.

And that is the best I can do without the whole database.

Regards,
Chris.

 

[Bat17]

I want the lines to read the code as
rst2(datew)=cdbl(now) 'those are the first values of both arrays mentioned

but instead it reads

rst2("datew")="cdbl(now)"

Is datew actualy a variable or a fieldname? the Eval () should sort out the date
Eval("cdbl(now)")

HTH

Peter

 

[Happy YN]

ChrisO
No offense taken but I have been using the profilegetitem successsfully for years. In this case too it is working fine and returning exactly as it should!
You seemed to have jumped to the conclusion that my problem lay there, perhaps I should not have shortened my code for the question rather posted the whole db on the other hand one likes to keep questions to a minimum because most people don't want to read thru loads of stuff

Bat17 - Yes datew is a fieldname, the eval might help me for the cdbl(now) I will try it later
Thanks

 

[ChrisO]

No problems Happy but please don’t assume I jumped to any conclusion here.

I spent two hours on it and it still looks like you are looking up an entry in an INI file called stanord.ini under the section called [Exceptions] with an entry of OriginalValue=???? where ???? is the value to be returned.

That’s basically what the API call GetPrivateProfileStringA(…) does.

But if you get it working with an Eval(…) then so be it and good luck.
(Again no problems because that’s the way I learn too. )

Regards,
Chris.

 

[Happy YN]

Wow 2hours! I feel very humbled and you are correct this is the file I was referencing and as I said I did not dwell on it because it worked and brought in the string as discussed. My probvlem was only after that how to force vb to realise that this is not a string but an actual field name
I will have to look at the Eval function to see if it helps me
Meantime thanks a million for your hard work
Yours gratefully
Happy YN

 

[Bat17]

force vb to realise that this is not a string but an actual field name

when you use the format rs() Access is looking for a string that contains the field name, which is what your array variable is supplying.

Peter

 

[Happy YN]

So are you saying that in my line

rst2("datew")="cdbl(now)"

the left hand side of the = sign is fine and all I should do is use eval for the right side?

 

[Bat17]

Yes

HTH

Peter

 

[SammyJ]

I'm not following all of this but if you don't get an answer to what is a fairly simple question, try just posing it without some of the irrelavant extras such as the API calls.

If your recordset has a field called "datew" then all of rs!datew, rs("datew") and rs.fields("datew") will reference the data in the field.