Form - Referencing Query ControlSource

[accessworlddoh]

When in a form where the record source is a Select Query and the controlsource is a database field name returned from the Select Query, how does one reference the database field name controlsource in an Event Procedure code statement? Merely using the database field name itself (e.g., Me.databasefieldname) returns an error.

 

[jbg]

Try referencing the controls name, i.e. Me!thetextbox

 

[dcx693]

When you lay out controls on a form, by default Access names the controls using the same name as the field it represents. For example, if your source query has a field named "FirstName" and you place that field onto your form, Access will call the field "FirstName".

This can lead to all sorts of potential confusion for you and for Access and if may be why Access is giving you an error. It thinks you're trying to use a field when you're actually trying to use the control that refers to the field.

The workaround is to rename any fields on the form you might need to reference in code using a prefix tag. For example, if we're talking about using "FirstName" as a text field, rename the field from "FirstName" to "txtFirstName". That way, you know just by looking at the name that you're working with a text field and that the control source is a field called "FirstName".

Then when you reference the field in code, use the syntax that jbg mentioned: Me!txtFieldName.

In case you're interested, the "Me!txtFieldName" notation is actually a shortcut for referring to controls on the current form. The "full" notation is actually: Me.Controls("txtFieldName").

 

[Pat Hartman]

Just a heads up. When referencing recordsource fields or form/report controls, use the Dot syntax rather than the Bang syntax. Search the archives or help for a more detailled explaination of the benefits.

Use Me.txtTextBox rather than Me!txtTextBox