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]

Re: filtered rowsource for combo

bbdata wrote:[color=blue]
> 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.
>[/color]

-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQijQxYechKq OuFEgEQIZQwCfYQ 8KW4frZWq0Ya0VR VX/4C1K8h8AoMFz
HMR3vc3c4LRWp6z S9KWUqMM9
=MG5X
-----END PGP SIGNATURE-----

[bbdata]

Re: filtered rowsource for combo

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]

Re: filtered rowsource for combo

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]

Re: filtered rowsource for combo

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]

Re: filtered rowsource for combo

bbdata wrote:[color=blue]
> 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?
>[/color]

-----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_AfterU pdate()

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQijleIechKq OuFEgEQJhWwCbBC HVwlq34I6AJXjVw zvwNtXlZVoAoN+n
e7GJ1JqRaQNF+73 q0DZBKngR
=IkcD
-----END PGP SIGNATURE-----

[bbdata]

Re: filtered rowsource for combo

> -----BEGIN PGP SIGNED MESSAGE-----[color=blue]
> 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_AfterU pdate()
>
> 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:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQijleIechKq OuFEgEQJhWwCbBC HVwlq34I6AJXjVw zvwNtXlZVoAoN+n
> e7GJ1JqRaQNF+73 q0DZBKngR
> =IkcD
> -----END PGP SIGNATURE-----[/color]

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]

Re: filtered rowsource for combo

bbdata wrote:

< SNIP >
[color=blue]
> 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 .[/color]

-----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.Ro wSource = SQL & "WHERE Active = True"
Else
Me!cboAgents.Ro wSource = SQL
End If

End Sub

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

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

iQA/AwUBQiygv4echKq OuFEgEQI9IgCg0R VLsih8SWZywvGbS gIjGcmNJ9cAnjqf
nlXFfxw7nz4k6fg CNhP07tOW
=XjJc
-----END PGP SIGNATURE-----

[bbdata]

Re: filtered rowsource for combo

> Hash: SHA1[color=blue]
>
> 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.Ro wSource = SQL & "WHERE Active = True"
> Else
> Me!cboAgents.Ro wSource = SQL
> End If
>
> End Sub
>
> HTH,
> --
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQiygv4echKq OuFEgEQI9IgCg0R VLsih8SWZywvGbS gIjGcmNJ9cAnjqf
> nlXFfxw7nz4k6fg CNhP07tOW
> =XjJc
> -----END PGP SIGNATURE-----[/color]

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.