Recortset syntax error

[Jac Tremblay]

Hi,
Here is some code that works well:
' *****
strSQL = "SELECT tblChambre.[No], tblChambre.NomCourt " & _
"FROM tblChambre ORDER BY tblChambre.[No]"
Set rstChb = CurrentDb.OpenRecordset(strSQL)
For intI = 1 To rstChb.RecordCount
intNo = Len(CStr(intI))
strNo = Mid(cstr0, 1, 2 - intNo) & intI
strNomContrôle = cstrLbl & strNo
' Me.Controls(strNomContrôle).Caption = _
' rstChb.Fields("[tblChambre.NomCourt]").Value
Me.Controls(strNomContrôle).Caption = rstChb.Fields(1).Value
rstChb.MoveNext
Next intI
' *****
The commented line does not work even though it should. There must be some
syntax error.
Can someone help me on that?
I would prefer to use "rstChb.Fields("[tblChambre.NomCourt]").Value" rather
than "rstChb.Fields(1).Value" for clarity sake.
Thanks

 

[Marshall Barton]

Here is some code that works well:
' *****
strSQL = "SELECT tblChambre.[No], tblChambre.NomCourt " & _
"FROM tblChambre ORDER BY tblChambre.[No]"
Set rstChb = CurrentDb.OpenRecordset(strSQL)
For intI = 1 To rstChb.RecordCount
intNo = Len(CStr(intI))
strNo = Mid(cstr0, 1, 2 - intNo) & intI
strNomContrôle = cstrLbl & strNo
' Me.Controls(strNomContrôle).Caption = _
' rstChb.Fields("[tblChambre.NomCourt]").Value
Me.Controls(strNomContrôle).Caption = rstChb.Fields(1).Value
rstChb.MoveNext
Next intI
' *****
The commented line does not work even though it should. There must be some
syntax error.
Can someone help me on that?
I would prefer to use "rstChb.Fields("[tblChambre.NomCourt]").Value" rather
than "rstChb.Fields(1).Value" for clarity sake.

There are a lot of names in the code that are not set in the
code so there may be a problem that is no apparent. The
only thing I can say for sure is that the syntax for
rstChb.Fields("[tblChambre.NomCourt]")
sure looks wrong. If [tblChambre.NomCourt] is the name of a
field in the recordset, it is seriously ugly. I would
expect it to be
rstChb.Fields(tblChambre.NomCourt)
or just
rstChb.Fields(NomCourt)

 

[Tom van Stiphout]

On Sat, 6 Feb 2010 11:51:01 -0800, Jac Tremblay

Me.Controls(strNomContrôle).Caption = _
rstChb.Fields("NomCourt").Value

-Tom.
Microsoft Access MVP

 

[Jac Tremblay]

Hi Marshall,
I know that this looks bad but I get an error no matter how I write the code.
The field name is tblChambre.NomCourt (TableName.FieldName) as one can see
in the strSQL string.
strSQL = "SELECT tblChambre.[No], tblChambre.NomCourt " & _
"FROM tblChambre ORDER BY tblChambre.[No]"
If I code:
Me.Controls(strNomContrôle).Caption = _
rstChb.Fields(tblChambre.NomCourt).Value
I get the error Variable not defined.
If I code:
Me.Controls(strNomContrôle).Caption = _
rstChb.Fields("tblChambre.NomCourt").Value
I get the error Item not found in the collection.
Same if I add the brackets ("[]").
What can I do?
--
Jac Tremblay

Here is some code that works well:
' *****
strSQL = "SELECT tblChambre.[No], tblChambre.NomCourt " & _
"FROM tblChambre ORDER BY tblChambre.[No]"
Set rstChb = CurrentDb.OpenRecordset(strSQL)
For intI = 1 To rstChb.RecordCount
intNo = Len(CStr(intI))
strNo = Mid(cstr0, 1, 2 - intNo) & intI
strNomContrôle = cstrLbl & strNo
' Me.Controls(strNomContrôle).Caption = _
' rstChb.Fields("[tblChambre.NomCourt]").Value
Me.Controls(strNomContrôle).Caption = rstChb.Fields(1).Value
rstChb.MoveNext
Next intI
' *****
The commented line does not work even though it should. There must be some
syntax error.
Can someone help me on that?
I would prefer to use "rstChb.Fields("[tblChambre.NomCourt]").Value" rather
than "rstChb.Fields(1).Value" for clarity sake.

There are a lot of names in the code that are not set in the
code so there may be a problem that is no apparent. The
only thing I can say for sure is that the syntax for
rstChb.Fields("[tblChambre.NomCourt]")
sure looks wrong. If [tblChambre.NomCourt] is the name of a
field in the recordset, it is seriously ugly. I would
expect it to be
rstChb.Fields(tblChambre.NomCourt)
or just
rstChb.Fields(NomCourt)

 

[Dirk Goldgar]

There are a lot of names in the code that are not set in the
code so there may be a problem that is no apparent. The
only thing I can say for sure is that the syntax for
rstChb.Fields("[tblChambre.NomCourt]")
sure looks wrong. If [tblChambre.NomCourt] is the name of a
field in the recordset, it is seriously ugly. I would
expect it to be
rstChb.Fields(tblChambre.NomCourt)
or just
rstChb.Fields(NomCourt)

Shouldn't that be:

rstChb.Fields("NomCourt")

?

 

[Marshall Barton]

Hi Marshall,
I know that this looks bad but I get an error no matter how I write the code.
The field name is tblChambre.NomCourt (TableName.FieldName) as one can see
in the strSQL string.
strSQL = "SELECT tblChambre.[No], tblChambre.NomCourt " & _
"FROM tblChambre ORDER BY tblChambre.[No]"
If I code:
Me.Controls(strNomContrôle).Caption = _
rstChb.Fields(tblChambre.NomCourt).Value
I get the error Variable not defined.
If I code:
Me.Controls(strNomContrôle).Caption = _
rstChb.Fields("tblChambre.NomCourt").Value
I get the error Item not found in the collection.
Same if I add the brackets ("[]").
What can I do?

Sheesh, I mangled it as badly as you did. I actually
thought that tblChambre was another recordset with a field
that contained the name of the field in rstChb (very
unusual, but legal).

As others have pointed out. it could be:
rstChb.Fields("NomCourt")
Or more simply:
rstChb!NomCourt

Do NOT use the table name when referencing a field in a
recordset and the [ ] are only needed when the field name
contains a non alphanumeric character.