Query Where WHERE clause points to Current Row?

[tsteinke]

What is the syntax to refer to your current row in an SQL statement?

I am using the "Lookup Wizard" to build a query in a table. How do you
refer to the Current Row

For instance I have a Table Customers with City and State

and I have a Table Cities with a list of cities and States

I want the query for Row Source of Cities to be something like

SELECT Cities.ID, Cities.Name, Cities.State
FROM Cities
WHERE (((Cities.State )=THISROW.State ))

What would go in place of THISROW?

Thanks in Advance.

[jimfortune]

ts******@gmail. com wrote:

What is the syntax to refer to your current row in an SQL statement?

I am using the "Lookup Wizard" to build a query in a table. How do you
refer to the Current Row

For instance I have a Table Customers with City and State

and I have a Table Cities with a list of cities and States

I want the query for Row Source of Cities to be something like

SELECT Cities.ID, Cities.Name, Cities.State
FROM Cities
WHERE (((Cities.State )=THISROW.State ))

What would go in place of THISROW?

Thanks in Advance.

I'm taking a guess about what you're trying to do.

tblCities
CityID Auto
CityName Text (Name is a Reserved Word)
CityState Text

1 Texarkana TX
2 Houston TX
3 Laredo TX
4 El Paso TX
5 Jennings LA

qryCityAndState :
SELECT (SELECT A.CityName & ', ' & A.CityState FROM tblCities AS A
WHERE A.CityID = tblCities.CityI D) As CityAndState FROM tblCities WHERE
tblCities.CityS tate = Forms!frmMain!c bxCityState.Val ue ORDER BY
tblCities.CityN ame;

! qryCityAndState (where cbxCityState.Va lue on frmMain = 'TX'):
El Paso, TX
Houston, TX
Laredo, TX
Texarkana, TX

The A.CityID = tblCities.CityI D forces the subquery (A) to use the
values from the current row. I hope this gets you close to what you're
looking for.

James A. Fortune

[tsteinke]

Thank you for your help. I'm sorry if I wasn't clear about what I was
doing.

The client has a City and a State Field, each of which contain ids that
are references to to other tables, When I am entering the data in the
datasheet I only want cities to show up that are in the Client's State
that has already been selected in the other field.

I am wondering about the statment

Forms!frmMain!c bxCityState.Val *ue

If I put that in it asks me to define it when I go to enter the data in
the datasheet. I assumed this was the name of the column in the current
record that is being edited? Or do I need to do this in a form?

[jimfortune]

ts******@gmail. com wrote:

Thank you for your help. I'm sorry if I wasn't clear about what I was
doing.

The client has a City and a State Field, each of which contain ids that
are references to to other tables, When I am entering the data in the
datasheet I only want cities to show up that are in the Client's State
that has already been selected in the other field.

I am wondering about the statment

Forms!frmMain!c bxCityState.Val *ue

If I put that in it asks me to define it when I go to enter the data in
the datasheet. I assumed this was the name of the column in the current
record that is being edited? Or do I need to do this in a form?

Your explanation helped. I hope by datasheet you mean a form in
datasheet view rather than a table field with a Lookup. By basing the
RecordSource of the Form on the table you are editing, you can use Form
Events to change the RowSource of a combobox in datasheet view
dynamically. That is, even though you're viewing the form in datasheet
mode, the event code behind the form is still available. Changing the
RowSource of a combobox based on existing values in the record is
typically done in the OnCurrent event. When the user moves to a new
record, code can look at the current value of a field by using the
control name with .Value for use in assigning a new RowSource to the
combobox control. Another possibility that becomes available is using
the AfterUpdate event of a combobox to change the RowSource of another
combobox dynamically. For instance, if 'TX' is chosen for the state,
the combobox for city can be set to return only cities that are in
Texas. Also note that you don't need the Forms!frmMain! part when
referring to a control on frmMain and the code is behind frmMain. When
you move to a record a THISROW is not needed. The value of the control
name grabs the value of the current record automatically. If the
OnCurrent event is used it will fire immediately on the first record
when the form is opened.

James A. Fortune