refering to field in RecordSource

[cssilver]

If I set my recordSource directly in my code:

Me.RecordSource = "sql stuff"

how do I later refer to one of the fields in the SQL code?

 

[kathryn]

The field is now part of the form. If the field name is FieldName, then you can refer to it by me!FieldName

P.S. Sorry for missing the ! in your last posting!

 

[cmmrfrds]

Here is an excerpt from the Help on the dot operator.

The . (dot) operator usually indicates that what follows is an item defined by Microsoft Access. For example, use the . (dot) operator to refer to a property of a form, report, or control.

Note You can also use the . (dot) operator to refer to a field value in an SQL statement, a Visual Basic for Applications method, or a collection. For example, the identifier Forms![Orders].Controls refers to the Controls collection of the Orders form. However, because the Controls collection is the default collection for forms and reports, it's usually not necessary to refer to it explicitly.

 

[cssilver]

I am still having trouble with this. For some reason, the program can't find one of the fields listed in the SQL even immediately after the
Me.RecordSource = "SQL"

I put:
varfieldname = "fieldname"
msgbox Me(varfieldname)

immediately after the SQL just to see if it is there, but it gave me an error that it couldn't find the field. I checked the spelling in all places (SQL code, my msgbox, etc) & all that is correct.

BTW - I need to use the Me(varfieldname) rather than the Me!fieldname syntax because I need to refer to fieldname using a variable. Each time this code is executed, the fieldname may be 1 of 4 different ones, depending on what the user chooses. However, that does not seem to be the problem, because the error message I get refers to the fieldname correctly (i.e. - Can't find "fieldname"...)

Thanks for all your help!

 

[kathryn]

If I understand you correctly, I have had this problem myself. It is a syntax thing.

I think that you are building your RecordSource SQL statement on the fly. If so, use this syntax


....Me!" & varfieldname & "....


HTH

 

[cmmrfrds]

A couple things on field names:

Each field on your form has a Name(other tab) and a Control Source(data tab), and these can be different. When referencing the Form you need to refer to the Name, such as, Me.Yourname. For simplicity it is probably better to standardize or keep the name the same.

This syntax will tell you the field names in you RecordSource. Put it after you equate the SQL to the Me.RecordSource. Access has lots of meta data that you can refer to when needed. This is an example of looking at the field names collection -- can be useful at times.

Dim RSMT as DAO.Recordset, indx as integer
Set RSMT = Me.RecordSetClone
For indx = 0 To (RSMT.Fields.Count - 1)
Debug.Print "Fields in record set = "; RSMT.Fields(indx).Name
Next '-- end for
RSMT.Close

Then do a "Control G" to look at the field names in your recordsource. You could do the same thing to look at your SQL string if you put it in a variable which I always do since it makes it easier to debug if necessary.