Query by form problem

  • Thread starter Thread starter Gez
  • Start date Start date
G

Gez

Hi,

I can specify that I want the value of a combo field on a form to be a
parameter in a query e.g. in the criteria area of a query I've got
[Forms]![Form1]![Combo3] but when I try and reference any columns of the
combo I get an "undefined function" error e.g. using
[Forms]![Form1]![Combo3].column(1)

Does anyone know how I can reference the columns in design view without
reverting to code.

Thanks,

Geraint
 
I use it quite often without any problem.

1. Is the Form *already* open and a value *selected* when you run the
Query?

2. Are you aware that Column(1) is referring to the *second* Column?
 
Yes, the form is definitely open, the value is selected and I am aware that I'm looking at the second column. It just seems that I can use this code in vb but on the query grid it thinks I'm calling a function when adding the .column(1) bit on the end.

I'm currently doing a long winded workaround but its really bugging me that I can't do this.

Thanks,

Gez

Van T. Dinh said:
I use it quite often without any problem.

1. Is the Form *already* open and a value *selected* when you run the
Query?

2. Are you aware that Column(1) is referring to the *second* Column?

--
HTH
Van T. Dinh
MVP (Access)



Gez said:
Hi,

I can specify that I want the value of a combo field on a form to be a
parameter in a query e.g. in the criteria area of a query I've got
[Forms]![Form1]![Combo3] but when I try and reference any columns of the
combo I get an "undefined function" error e.g. using
[Forms]![Form1]![Combo3].column(1)

Does anyone know how I can reference the columns in design view without
reverting to code.

Thanks,

Geraint
Click to expand...
Click to expand...
 
Yes, the form is definitely open, the value is selected and I am aware that
I'm looking at the second column. It just seems that I can use this code in
vb but on the query grid it thinks I'm calling a function when adding the
..column(1) bit on the end.

You cannot directly reference the "dot column" value of a ComboBox in a
query. You can either set a hidden control on the form to pull the value
from the column and reference that control in your query or you can use the
Eval() function. I've never actually done the latter, but I recently saw
postings that indicated that if you wrap the reference with the column
identifier in the Eval function that it would work.
 
Are you sure, Rick?

I check it at work tomorrow as I think I used Column in Query without any
problem in AXP.
 
Are you sure, Rick?

I check it at work tomorrow as I think I used Column in Query without any
problem in AXP.
Click to expand...

The feature might have been added in later versions. I'm pretty certain
that when I've tried this in A97 that it wouldn't work.
 
Rick Brandt said:
The feature might have been added in later versions. I'm pretty certain
that when I've tried this in A97 that it wouldn't work.
Click to expand...

I just tested this in A97 and I can't get it to work even with the Eval()
function (maybe I am using it incorrectly). Both of the following...

Expr1: [Forms]![Form1]![Combo0].Column(1)
Expr1: Eval([Forms]![Form1]![Combo0].Column(1))

....produce the error...

Undefined function '[Forms]![Form1]![Combo0].Column' in expression.
 
That's what I'm getting in Access 2000. Maybe its an added feature of AXP.


Rick Brandt said:
Rick Brandt said:
The feature might have been added in later versions. I'm pretty certain
that when I've tried this in A97 that it wouldn't work.
Click to expand...

I just tested this in A97 and I can't get it to work even with the Eval()
function (maybe I am using it incorrectly). Both of the following...

Expr1: [Forms]![Form1]![Combo0].Column(1)
Expr1: Eval([Forms]![Form1]![Combo0].Column(1))

...produce the error...

Undefined function '[Forms]![Form1]![Combo0].Column' in expression.
Click to expand...
 
Sorry, I remembered wrongly. AccessXP keeps interpreting
as a function similar to your results.

However, I actually used Eval() in some Queries which
worked. Slight difference from your Eval:

Eval("[Forms]![frmQCData_AddEdit]![cboTestBatchID].
Column(2)")

as one line in the criterion row of one my Queries.

I just discovered that I used so many methods for this
sort of things: an intermediate TextBox in some cases,
QueryDefs to construct or modify the SQL String before
passing on to JET. I may need to go through an test see
which way is the most efficient and change all others to
the same method.


HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Rick Brandt said:
The feature might have been added in later versions. I'm pretty certain
that when I've tried this in A97 that it wouldn't work.
Click to expand...

I just tested this in A97 and I can't get it to work even with the Eval()
function (maybe I am using it incorrectly). Both of the following...

Expr1: [Forms]![Form1]![Combo0].Column(1)
Expr1: Eval([Forms]![Form1]![Combo0].Column(1))

....produce the error...

Undefined function '[Forms]![Form1]![Combo0].Column' in expression.



--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Click to expand...
 
Back
Top