'Current Row' or similar for query-based combo box

Apologies if this is an obvious question, but I can't find any suitable query
function or operator. I have a series of tables with RI enforced foreign
keys. When keying data into a given row, I want to be able to limit the
combo box values by a value I have already entered on the current
(uncommitted) row.

Is there an SQL function or a VBA method of qualifying the combo box query
to limit the results of the query by a value on the current row?

For example...

select
J1.Part_No
from
Parts J1
where
J1.Part_Year = current row (INV.Part_Year)
;

* where INV.Part_Year is a value on the row I am keying to table INV

Any help is greatly appreciated.

Dan

Queries don't have comboboxes, forms do.

You could use a reference in your query to the value that was selected in a
form's combobox, and this is a common way of creating a combobox-guided
query. You need to create a form and work from there, not directly in the
query.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Dan Smith - Sojourn" Dan Smith - wrote
in message ...
Apologies if this is an obvious question, but I can't find any suitable
query
function or operator. I have a series of tables with RI enforced foreign
keys. When keying data into a given row, I want to be able to limit the
combo box values by a value I have already entered on the current
(uncommitted) row.

Is there an SQL function or a VBA method of qualifying the combo box query
to limit the results of the query by a value on the current row?

For example...

select
J1.Part_No
from
Parts J1
where
J1.Part_Year = current row (INV.Part_Year)
;

* where INV.Part_Year is a value on the row I am keying to table INV

Any help is greatly appreciated.

If you are entering directly into a table (or query) then there is no way to
accomplish what you want to do. IF you are entering data into a form, then
there is a possibility of doing what you want.

In a continuous form, the solution can be a bit complex. On a continuous
form, there is only one control shown many times (once for each row). So if
you change the row source of a combobox depending on the row you are in, other
rows that need other values to display will suddenly display blank. The data
is still in the table, the problem is that the combobox control does not have
that particular value available to display.

So, post back if you are using a form to display and modify the data.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dan Smith - Sojourn wrote:
Apologies if this is an obvious question, but I can't find any suitable query
function or operator. I have a series of tables with RI enforced foreign
keys. When keying data into a given row, I want to be able to limit the
combo box values by a value I have already entered on the current
(uncommitted) row.

Is there an SQL function or a VBA method of qualifying the combo box query
to limit the results of the query by a value on the current row?

For example...

select
J1.Part_No
from
Parts J1
where
J1.Part_Year = current row (INV.Part_Year)
;

* where INV.Part_Year is a value on the row I am keying to table INV

Any help is greatly appreciated.

Thanks for the information John.

Based on a prior post, I have created a form to access my table. By using
form.[field_name] in my combobox query I am able to get the intended result.
However as you pointed out, the results are only valid for the first row that
is accessed when I open the form. The form.[field_name] values do not update
when I scroll to the next row in the table.

You mentioned doing a postback. I am not familiar with this function. I am
thinking of using the Onlostfocus event as the trigger for each form field
value to be updated. Can you give me an example of how to do the postback?

Thanks again for your help with this.

-Dan


"John Spencer" wrote:

If you are entering directly into a table (or query) then there is no way to
accomplish what you want to do. IF you are entering data into a form, then
there is a possibility of doing what you want.

In a continuous form, the solution can be a bit complex. On a continuous
form, there is only one control shown many times (once for each row). So if
you change the row source of a combobox depending on the row you are in, other
rows that need other values to display will suddenly display blank. The data
is still in the table, the problem is that the combobox control does not have
that particular value available to display.

So, post back if you are using a form to display and modify the data.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dan Smith - Sojourn wrote:
Apologies if this is an obvious question, but I can't find any suitable query
function or operator. I have a series of tables with RI enforced foreign
keys. When keying data into a given row, I want to be able to limit the
combo box values by a value I have already entered on the current
(uncommitted) row.

Is there an SQL function or a VBA method of qualifying the combo box query
to limit the results of the query by a value on the current row?

For example...

select
J1.Part_No
from
Parts J1
where
J1.Part_Year = current row (INV.Part_Year)
;

* where INV.Part_Year is a value on the row I am keying to table INV

Any help is greatly appreciated.

No, POST BACK to the newsgroup and ask for more help.

You can use the form's current event to force the combobox to be requeried.

Me.NameOfCombobox.Requery



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dan Smith - Sojourn wrote:
Thanks for the information John.

Based on a prior post, I have created a form to access my table. By using
form.[field_name] in my combobox query I am able to get the intended result.
However as you pointed out, the results are only valid for the first row that
is accessed when I open the form. The form.[field_name] values do not update
when I scroll to the next row in the table.

You mentioned doing a postback. I am not familiar with this function. I am
thinking of using the Onlostfocus event as the trigger for each form field
value to be updated. Can you give me an example of how to do the postback?

Thanks again for your help with this.

-Dan


"John Spencer" wrote:

If you are entering directly into a table (or query) then there is no way to
accomplish what you want to do. IF you are entering data into a form, then
there is a possibility of doing what you want.

In a continuous form, the solution can be a bit complex. On a continuous
form, there is only one control shown many times (once for each row). So if
you change the row source of a combobox depending on the row you are in, other
rows that need other values to display will suddenly display blank. The data
is still in the table, the problem is that the combobox control does not have
that particular value available to display.

So, post back if you are using a form to display and modify the data.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dan Smith - Sojourn wrote:
Apologies if this is an obvious question, but I can't find any suitable query
function or operator. I have a series of tables with RI enforced foreign
keys. When keying data into a given row, I want to be able to limit the
combo box values by a value I have already entered on the current
(uncommitted) row.

Is there an SQL function or a VBA method of qualifying the combo box query
to limit the results of the query by a value on the current row?

For example...

select
J1.Part_No
from
Parts J1
where
J1.Part_Year = current row (INV.Part_Year)
;

* where INV.Part_Year is a value on the row I am keying to table INV

Any help is greatly appreciated.

Dooh! I'm an idiot. ;-)

Thanks for the clarification. The requery solution is working very well.
Thanks again for all your help.

BTW - I do seem to need the requery function defined for the onlostfocus
event of the driver field so I get the right query results when I am entering
a new row in the table.


"John Spencer" wrote:

No, POST BACK to the newsgroup and ask for more help.

You can use the form's current event to force the combobox to be requeried.

Me.NameOfCombobox.Requery



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dan Smith - Sojourn wrote:
Thanks for the information John.

Based on a prior post, I have created a form to access my table. By using
form.[field_name] in my combobox query I am able to get the intended result.
However as you pointed out, the results are only valid for the first row that
is accessed when I open the form. The form.[field_name] values do not update
when I scroll to the next row in the table.

You mentioned doing a postback. I am not familiar with this function. I am
thinking of using the Onlostfocus event as the trigger for each form field
value to be updated. Can you give me an example of how to do the postback?

Thanks again for your help with this.

-Dan


"John Spencer" wrote:

If you are entering directly into a table (or query) then there is no way to
accomplish what you want to do. IF you are entering data into a form, then
there is a possibility of doing what you want.

In a continuous form, the solution can be a bit complex. On a continuous
form, there is only one control shown many times (once for each row). So if
you change the row source of a combobox depending on the row you are in, other
rows that need other values to display will suddenly display blank. The data
is still in the table, the problem is that the combobox control does not have
that particular value available to display.

So, post back if you are using a form to display and modify the data.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dan Smith - Sojourn wrote:
Apologies if this is an obvious question, but I can't find any suitable query
function or operator. I have a series of tables with RI enforced foreign
keys. When keying data into a given row, I want to be able to limit the
combo box values by a value I have already entered on the current
(uncommitted) row.

Is there an SQL function or a VBA method of qualifying the combo box query
to limit the results of the query by a value on the current row?

For example...

select
J1.Part_No
from
Parts J1
where
J1.Part_Year = current row (INV.Part_Year)
;

* where INV.Part_Year is a value on the row I am keying to table INV

Any help is greatly appreciated.