By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,739 Members | 1,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,739 IT Pros & Developers. It's quick & easy.

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

beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. tblRecords
  2.  
  3. 'Not all fields shown
  4. RecordID: Autonumber
  5. PartIDFK: Number 'Combo box with row source = tblParts. Displays the second of 2 columns in table (PartName)
  6.  
Expand|Select|Wrap|Line Numbers
  1. tblParts
  2.  
  3. PartID: Autonumber
  4. PartName: Text
  5.  
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
Aug 4 '10 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. =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):

Expand|Select|Wrap|Line Numbers
  1. Public Function fFormControlValue(ByVal FormName As String, ByVal ControlName As String, Optional ByVal ColumnNo As Integer = 0)
  2.     If ColumnNo > 0 Then
  3.         fFormControlValue = Forms(FormName).Controls(ControlName).column(ColumnNo)
  4.     Else
  5.         fFormControlValue = Forms(FormName).Controls(ControlName)
  6.     End If
  7. End Function
In the query editor add a new field calling the function as follows:

Expand|Select|Wrap|Line Numbers
  1. Part Name: fFormControlValue("YourFormName", "YourPartIdControlName", 1)
e.g. if your form is named frmRecords and the control PartIDFK the call would be:

Expand|Select|Wrap|Line Numbers
  1. 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

Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. =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):

Expand|Select|Wrap|Line Numbers
  1. Public Function fFormControlValue(ByVal FormName As String, ByVal ControlName As String, Optional ByVal ColumnNo As Integer = 0)
  2.     If ColumnNo > 0 Then
  3.         fFormControlValue = Forms(FormName).Controls(ControlName).column(ColumnNo)
  4.     Else
  5.         fFormControlValue = Forms(FormName).Controls(ControlName)
  6.     End If
  7. End Function
In the query editor add a new field calling the function as follows:

Expand|Select|Wrap|Line Numbers
  1. Part Name: fFormControlValue("YourFormName", "YourPartIdControlName", 1)
e.g. if your form is named frmRecords and the control PartIDFK the call would be:

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 5 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.