473,756 Members | 8,108 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

filtered rowsource for combo

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.

Nov 13 '05 #1
8 6159
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:::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-----
Nov 13 '05 #2
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.

Nov 13 '05 #3
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?

Nov 13 '05 #4
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.

Nov 13 '05 #5
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_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-----
Nov 13 '05 #6
> -----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-----


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 .
Nov 13 '05 #7
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.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-----
Nov 13 '05 #8
> 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-----


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.
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3537
by: Krisa | last post by:
Hello all, I just discovered something (stop me if you've heard this before....) that was causing me a significant performance hit when opening a form with subforms. To speed up loading the form, I set the rowsources of its, and its subforms', combo boxes in the "Enter" events of the combo boxes. That's the standard trick for not loading the rowsource unless/until the user actually needs the combo box. For example:
3
9371
by: B | last post by:
I know there are several ways to speed up combo boxes and form loading. Most of the solutions leave rowsource of the combo box blank and set the rowsource to a saved query or an SQL with a where clause after users typed in one or several letters/digits. My problem is as follows Most of the time I need to display form in continuous format, that means the combo box will appear in each record. For example I have a form to let users view...
7
8526
by: Nicolae Fieraru | last post by:
Hi All, I am trying to change the rowsource of a combobox when I click on it. I played with many events, associated with the form and the combobox, but still haven't figured out what is the way of doing it. I have a table with products, tblProducts, some of them are Active while others are Inactive. The form shows all the products purchased by a customer, both Active and Inactive in a ComboBox, cbProducts. My client wants to view all...
6
4222
by: lauren quantrell | last post by:
I have a combo box on a form. It uses the GotFocus event to poopulate the rowsource with a value list that is created on the fly in VBA. Fine and dandy, except, is a user opens another form and returns to the form with the rowsource, the rowsource is not refreshed because the combo box doesn't execute the GotFocus event because it has never lost the focus. If I put the code to generate the value list in the MouseDown event of the combo...
5
11535
by: dkintheuk | last post by:
Is it possible to set (using VBA) the rowsource for a combo box but have that rowsource come from a pass-through query. I can obviously manually go into the rowsource property and define the query and even set up the odbc string, but if i want to set this using VBA i cannot see how to do this. I know i can set forms!frm_test!combobox.rowsource to the SQL but that does not allow me to set the odbc for the passthrough element.
7
15023
by: bluray | last post by:
I have been fighting with this for a while, and I know someone on this board will be able to figure it out in a split second :) I have a List Box and a Combo Box. Based on the selection the user makes in the Combo Box, I need for the Rowsource in the List Box to be changed to one of two queries, and requeried so the user can select an option from the List Box based on the newly selected criteria from the Combo Box. I thought it would be...
3
2404
by: Thelma Roslyn Lubkin | last post by:
I have a form whose rowsource is a single table, i.e. 'Datasystem'. I use a combobox to search for records in that table based on the value of a single field, i.e., 'systemname'. I use a multi-select listbox to filter the form on a different field, i.e. 'domain', so that the filtered form can have several allowed values of this field
4
2589
by: lulu123 | last post by:
here's my problem 1) rowsource .RowSource = "select distinct , " & "from Projects " & " where =" & Me! I have the above code, and it works fine. however, i want to make the combo box in alphabetical order by . I tried to add "order by " after "me!". But it gives me error msg. Can someone tell me how can i do that. Thanks. 2) REfresh the combo box list after add a record.
14
2348
kcdoell
by: kcdoell | last post by:
Hello: I have a form (Default view =single form) with a subform (Default view =continuous forms) embedded into it. In the form I have three controls that display the Division, Working Region & Credit Region. The subform displays the data/records. The record source for both my form and subform is driven by a query, which is the way I filter the records that the end user will see via another form I created. Everything is working fine but...
0
9456
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10040
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9873
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9713
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8713
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6534
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5142
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5304
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.