How to access literal value of a field on a query that has a row source on the table

[beacon]

Hi everybody,

[Access 2003]

I have a combo box field in a table with a number data type that has a row source to another table that has the text value for the item selected. Now, in one of my queries, I would like to access the text value of this field without having to add the second table to my query. Is that possible? I've done it in forms and reports using the .Text or .Column property, but I'm not seeing that has an option in the Expression builder.

Here's an very simplified example of what I'm talking about. The two tables are:

tblRecords
 
'Not all fields shown
RecordID: Autonumber
PartIDFK: Number 'Combo box with row source = tblParts. Displays the second of 2 columns in table (PartName)
 

tblParts
 
PartID: Autonumber
PartName: Text
 

There's a one-to-many relationship between tblRecords and tblParts. In the query I'm trying to create, which includes another table and isn't important to the example, I have PartID as one of the fields. PartID shows up in the query as a combo box and displays the value, but it's bound to the number in the table. I want to grab the text for the field instead of the number.

So if PartIDFK = 2, but the display value is "Engine", I want to be able to access "Engine" in the expression instead of 2.

Just to hopefully address a question you might have, I can't include tblParts in the query because it negatively affects the code in a form where I'm using the query.

Thanks,
beacon

[Stewart Ross]

Hi Beacon. The column property of a control cannot be referred to within a query as far as I know.

As you have mentioned that you cannot include the table tblParts into the source query for your form, which would be by far the simplest option, you have two choices:

(1) use DLookup to lookup the value corresponding to the part ID, or

(2) use a bespoke VBA function to lookup the value of your combo box text column.

Option 1 is not dependent on the combo, and is the most general solution:

=Dlookup("PartName", "tblParts", "PartID = " & Forms!YourFormName!YourPartIdControlName)

This will be slower to run than simply looking up the combo would be.

Option 2 is straightforward to do but does tie your query to the combo on the form. Assuming that the combo is on the main form, not a subform, place the following function in a general code module (one that you can access from the Modules tab in Access):

Public Function fFormControlValue(ByVal FormName As String, ByVal ControlName As String, Optional ByVal ColumnNo As Integer = 0)
    If ColumnNo > 0 Then
        fFormControlValue = Forms(FormName).Controls(ControlName).column(ColumnNo)
    Else
        fFormControlValue = Forms(FormName).Controls(ControlName)
    End If
End Function

In the query editor add a new field calling the function as follows:

Part Name: fFormControlValue("YourFormName", "YourPartIdControlName", 1)

e.g. if your form is named frmRecords and the control PartIDFK the call would be:

Part Name: fFormControlValue("frmRecords", "PartIDFK", 1)

The column number is optional in the function listed - if you leave it out the function will just return the value of the control itself (equivalent to the value for Column (0) in a combo).

Access queries can always interpret calls to built-in and bespoke functions, whereas some objects cannot be referred to at all from within a query, and others may be unreliable (as for example when referring directly to the current value of a control as a criterion in a query).

-Stewart