filtered rowsource for combo

[bbdata]

ok i have a problem here and not much time to play round.
have a form bound to a table. one of the combos is bound to a field
Agents. i have active and retired agents. thing is, i want to be able
to retrieve all agents from the table, but to see only active in
dropdown list of the combo, to asure accurate data entry. if i filter
the agents table for a rowsource to get back only active in the
dropdown, i cant retreive names of the rest when i go through the
records in my form. obviosly they are blank for those guys who are
retired, cos they are not in the dropdown. the only way i see it is to
make a separate form just for dataentry,but there must be an elegant
solution here. whats worse,i bet i had a same situation in access97 and
no problems. now its access2000 . advice will be appreciated.

[MGFoster]

bbdata wrote:

ok i have a problem here and not much time to play round.
have a form bound to a table. one of the combos is bound to a field
Agents. i have active and retired agents. thing is, i want to be able
to retrieve all agents from the table, but to see only active in
dropdown list of the combo, to asure accurate data entry. if i filter
the agents table for a rowsource to get back only active in the
dropdown, i cant retreive names of the rest when i go through the
records in my form. obviosly they are blank for those guys who are
retired, cos they are not in the dropdown. the only way i see it is to
make a separate form just for dataentry,but there must be an elegant
solution here. whats worse,i bet i had a same situation in access97 and
no problems. now its access2000 . advice will be appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you using an .adp file? There's a difference between the methods
you can use in .adp & .mdb for dynamic ComboBoxes (changing RowSources).
Does your ComboBox have a dynamic RowSource?

The form should be bound to the table or a query that gets both active &
retired agents. The ComboBox should have a RowSource that only gets the
active agents. To say "obviosly they are blank for those guys who are
retired, cos they are not in the dropdown" isn't obvious. If you have
two separate queries (one for the Form, the other for the ComboBox) the
Form should always show all the records in the table and the ComboBox
should show only active agents.

IOW, ideally, you'd have 2 SQL statements for the Form & the ComboBox:

Form - RecordSource Property:
SELECT * FROM Agents

ComboBox - RowSource Property:
SELECT AgentID, AgentName FROM Agents WHERE Active = True

What's your Form's RecordSource property and what's your ComboBox's
RowSource property?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQijQxYechKqOuFEgEQIZQwCfYQ8KW4frZWq0Ya0VRVX/4C1K8h8AoMFz
HMR3vc3c4LRWp6zS9KWUqMM9
=MG5X
-----END PGP SIGNATURE-----

[bbdata]

thanks mgfoster,
my two sql statements look EXACTLY like yours, i just use a query
instead Agents table in first one. otherwise my logic is same as yours.
is there a way to change rowsource in runtime? it should be, like any
other property, but im not sure what event to chose to trigger that
change.
i dont know anything about dynamic rowsources. but im about to find out
..
im using .mdb file.

[bbdata]

ok, now i see whats dynamic rowsource. which brings me to a previous
question: when to change it to show only active agents? on BeforeUpdate
of that combo?
and than switch back to all agents AfterUpdate?

[bbdata]

well it works if you store the name of the agent in your table that you
use as a datasource, and have the field with the name in a query for a
rowsource, but thats not a way it should be. i want to keep just
agentID, dealID and other ids in my table, as god intended to be kept
in relational database.

[MGFoster]

bbdata wrote:

ok, now i see whats dynamic rowsource. which brings me to a previous
question: when to change it to show only active agents? on BeforeUpdate
of that combo?
and than switch back to all agents AfterUpdate?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What, exactly, are you doing w/ the ComboBox? It sounds like you're
using the ComboBox to filter the Form. Is that correct? If so, the
ComboBox has to be unbound & you'd use the ComboBox's AfterUpdate event
to change the Form's RecordSource. E.g.:

Private Sub ComboBox_AfterUpdate()

Me.RecordSource = "SELECT * FROM Agents WHERE AgentID = " & _
Me!ComboBox

End Sub

This would show only the Agent selected in the ComboBox.

The ComboBox's properties would be something like this:
ControlSource: <nothing>
BoundColumn: 1
RowSourceType: Table/Query
RowSource: SELECT AgentID, AgentName FROM Agents ORDER BY AgentName
ColumnCount: 2
ColumnWidths: 0";2"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQijleIechKqOuFEgEQJhWwCbBCHVwlq34I6AJXjVwzvwNt XlZVoAoN+n
e7GJ1JqRaQNF+73q0DZBKngR
=IkcD
-----END PGP SIGNATURE-----

[bbdata]

> -----BEGIN PGP SIGNED MESSAGE-----

Hash: SHA1

What, exactly, are you doing w/ the ComboBox? It sounds like you're
using the ComboBox to filter the Form. Is that correct? If so, the
ComboBox has to be unbound & you'd use the ComboBox's AfterUpdate event
to change the Form's RecordSource. E.g.:

Private Sub ComboBox_AfterUpdate()

Me.RecordSource = "SELECT * FROM Agents WHERE AgentID = " & _
Me!ComboBox

End Sub

This would show only the Agent selected in the ComboBox.

The ComboBox's properties would be something like this:
ControlSource: <nothing>
BoundColumn: 1
RowSourceType: Table/Query
RowSource: SELECT AgentID, AgentName FROM Agents ORDER BY AgentName
ColumnCount: 2
ColumnWidths: 0";2"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQijleIechKqOuFEgEQJhWwCbBCHVwlq34I6AJXjVwzvwNt XlZVoAoN+n
e7GJ1JqRaQNF+73q0DZBKngR
=IkcD
-----END PGP SIGNATURE-----

not at all, mgfoster. form is filtered before, filter not afecting
that particular combo.
i simply use the form to review/enter some transaction. one of the
parameters of that transaction is AgentName. Agents may come and go,
but deals are still active . so out of 100 deals, 25 has AgentName
field of an agent who is not employed any more. i need to see those
AgentNames as i am going through the records,(or when creating
reports, for that matter), but when a new transaction has to be
entered, i want to see only the names of currently employed, active
agents in the dropdown list of the combo. So thats why i need a combo
to be bound to AgentName field in Transaction table( which is a
datasource for the form) .
so when i filter the rowsource of the combo(select...from...where
agents=active), i do get only active AgentNames, but at the same time
, i cant see the name of non-active AgentName when such a transaction
comes up.
i explained before , when i store AgentNames in the transaction table
everything is ok, but thats not really the way things should be.
i need to mention that this is only a temp soluton, as in near future
agent will log to start their session, and there will be no need for
that combo. but now i need to have it .

[MGFoster]

bbdata wrote:

< SNIP >

i simply use the form to review/enter some transaction. one of the
parameters of that transaction is AgentName. Agents may come and go,
but deals are still active . so out of 100 deals, 25 has AgentName
field of an agent who is not employed any more. i need to see those
AgentNames as i am going through the records,(or when creating
reports, for that matter), but when a new transaction has to be
entered, i want to see only the names of currently employed, active
agents in the dropdown list of the combo. So thats why i need a combo
to be bound to AgentName field in Transaction table( which is a
datasource for the form) .
so when i filter the rowsource of the combo(select...from...where
agents=active), i do get only active AgentNames, but at the same time
, i cant see the name of non-active AgentName when such a transaction
comes up.
i explained before , when i store AgentNames in the transaction table
everything is ok, but thats not really the way things should be.
i need to mention that this is only a temp soluton, as in near future
agent will log to start their session, and there will be no need for
that combo. but now i need to have it .

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe I have it now: You want to see ALL agents in the ComboBox box
when editing old records; for New records only the ACTIVE agents should
appear in the ComboBox. If my assertion is correct then in the form's
OnCurrent event you will be changing the RowSource of the ComboBox.
E.g.:

Private Sub Form_Current()

Const SQL = "SELECT AgentID, AgentName FROM Agents "

If Me.NewRecord Then
Me!cboAgents.RowSource = SQL & "WHERE Active = True"
Else
Me!cboAgents.RowSource = SQL
End If

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiygv4echKqOuFEgEQI9IgCg0RVLsih8SWZywvGbSgIjGc mNJ9cAnjqf
nlXFfxw7nz4k6fgCNhP07tOW
=XjJc
-----END PGP SIGNATURE-----

[bbdata]

> Hash: SHA1

I believe I have it now: You want to see ALL agents in the ComboBox box
when editing old records; for New records only the ACTIVE agents should
appear in the ComboBox. If my assertion is correct then in the form's
OnCurrent event you will be changing the RowSource of the ComboBox.
E.g.:

Private Sub Form_Current()

Const SQL = "SELECT AgentID, AgentName FROM Agents "

If Me.NewRecord Then
Me!cboAgents.RowSource = SQL & "WHERE Active = True"
Else
Me!cboAgents.RowSource = SQL
End If

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiygv4echKqOuFEgEQI9IgCg0RVLsih8SWZywvGbSgIjGc mNJ9cAnjqf
nlXFfxw7nz4k6fgCNhP07tOW
=XjJc
-----END PGP SIGNATURE-----

thanks mgfoster
didnt know about newrecord property.
i have done somrthin similar, since i have add new deal option on the
swithboard. so i open form in add mode and change rowsource then.