vba access trying to get a variable fieldname working-CodePudding

I have been staring at my screen for about 1.5 hours now and am ready to give up. I have a record called ouderecord I get from a table with a SQL statement.

Sofar I have this code:

strSQL = "SELECT * from tblS1lijst where id = " & Keuzelijst3.Column(0)
Debug.Print strSQL
ouderecord = CurrentDb.OpenRecordset(strSQL)
Tmpstr = Mid(Keuzelijst3.Column(2), InStr(1, Keuzelijst3.Column(2), ".")   1, _
  Len(Keuzelijst3.Column(2)) - InStr(1, Keuzelijst3.Column(2), ".") - 1)
veld_naam = "[" & Tmpstr & "]"
tekst = "ouderecord!" & veld_naam
Debug.Print tekst

When I hover over the text (ouderecord![Situatie]) in the Direct window, I see the contents of the field... But how do I get that content in a variable?

Thanks for any help!

Bimmer

CodePudding user response：

If the name of the field is static, you can do it like this

Dim tekst As String

tekst = Nz(ouderecord![Situatie])

tekst = Nz(ouderecord!Situatie)

The [ and ] are only required if the column name contains invalid characters like a space character or a hyphen or if it conflicts with a keyword.

But if the name is given dynamically in a variable, you must access the Fields default property of the Recordset

tekst = Nz(ouderecord(veld_naam))

This is the same as

tekst = Nz(ouderecord.Fields(veld_naam).Value)

Where veld_naam must be the name of the field without the column name escapes [ and ]: E.g., veld_naam = "Situatie"

Note that the parameter can be either a String representing the name of the field or an Integer representing the index of the column in the query.

The Nz function converts NULL values into and appropriate value for the given type. In this case it returns an empty string for NULL.

CodePudding user response：

That Nz did the trick! I now have

strSQL = "SELECT * from tblS1lijst where id = " & Keuzelijst3.Column(0)
Debug.Print strSQL
ouderecord = CurrentDb.OpenRecordset(strSQL)
Tmpstr = Mid(Keuzelijst3.Column(2), InStr(1, Keuzelijst3.Column(2), ".")   1, Len(Keuzelijst3.Column(2)) - InStr(1, Keuzelijst3.Column(2), ".") - 1)
veld_naam = "[" & Tmpstr & "]"
tekst = "ouderecord!" & veld_naam
tekst = Nz(ouderecord(veld_naam))

Debug.Print tekst

And that works like a charm!

Thanks you very much!!!!